SQL Server中2个别名的总和

SELECT dbo.Items.ItemNo, 
       dbo.Items.Name, 
       dbo.Vendors.Name AS VendorName, 
       ISNULL(ItemsStock.StockOnHand, 0) AS [Stock on Hand], 
       ISNULL(PO.[Open PO's'], 0) AS [Open PO], 
       ISNULL(WO.OpenWOQTY, 0) AS [QTY Entered],
       ISNULL(ItemsStock.StockOnHand, 0) + ISNULL(PO.[Open PO's'], 0) - ISNULL(WO.OpenWOQTY, 0) AS [Balance]
FROM table

三条建议:

1. 使用表别名。
2. 使用 coalesce() 而不是 isnull() 。 聚结() 是ANSI标准。
3. 使用不需要转义的列名。

这看起来像:

SELECT i.ItemNo, i.Name, v.Name AS VendorName, 
       COALESCE(is.StockOnHand, 0) AS Stock_on_Hand, 
       COALESCE(PO.[Open PO's'], 0) AS Open_PO, 
       COALESCE(WO.OpenWOQTY, 0) AS QTY_Entered,
       (COALESCE(is.StockOnHand, 0) + COALESCE(PO.[Open PO's'], 0) - COALESCE(WO.OpenWOQTY, 0) as Balance
FROM dbo.Items i JOIN
     dbo.Vendors v
     . . .

如果不想重复别名,传统的方法是使用CTE或子查询。SQL Server还提供了横向联接,这是由 apply 关键词:

SELECT i.ItemNo, i.Name, v.Name AS VendorName, 
       vals.Stock_on_Hand, vals.Open_PO, vals.QTY_Entered,
       (vals.Stock_on_Hand + vals.Open_PO - vals.QTY_Entered) as Balance
FROM dbo.Items i JOIN
     dbo.Vendors v
     . . . CROSS APPLY
     (VALUES ( COALESCE(is.StockOnHand, 0), COALESCE(PO.[Open PO's'], COALESCE(WO.OpenWOQTY, 0))
     ) vals(Stock_on_Hand, Open_PO, QTY_Entered);