时间戳

首页 > 解决方案 > 在 SQL 查询中，不能用这些名称减去具有给定名称的计算列

sql - 在 SQL 查询中，不能用这些名称减去具有给定名称的计算列

问题描述

在 SQL 查询中，不能用这些名称减去具有给定名称的计算列。有没有人可以告诉我问题是什么？

SELECT DISTINCT 
    MT.UnitNo
    ,UC.UnitName
    ,(SELECT (SUM(CAST(ISNULL(UnitPrice, '0') AS BIGINT)))
      FROM MonthlyUnitPrice MUP
      WHERE MUP.[Year] = '1398'
        AND MUP.UnitNo = MT.UnitNo) AS AnnualQuota
    ,(SELECT (SUM(CAST(ISNULL(TotalCalculatedPrice, '0') AS BIGINT)))
      FROM [ManagerTable] MTB
      WHERE MTB.[Year] = '1398'
        AND MTB.[Month] < '9'
        AND MTB.UnitNo = MT.UnitNo) AS TotalUsage
    ,(AnnualQuota - TotalUsage)
FROM 
    ManagerTable MT
INNER JOIN 
    UnitCodes UC ON UC.UnitNO = MT.UnitNO
INNER JOIN 
    MonthlyUnitPrice MU ON MT.UnitNO = MU.UnitNO
                        AND MT.[Year] = MU.[Year]
                        AND MT.[Month] = MU.[Month]
WHERE  
    MT.[Year] = '1398'
    AND MT.[Month] < 9
    AND MU.UnitPrice IS NOT NULL
GROUP BY 
    MT.UnitNo
    ,UC.UnitName

错误：

消息 207，级别 16，状态 1，第 15 行
无效的列名称“AnnualQuota”

消息 207，级别 16，状态 1，第 15 行
无效的列名称“TotalUsage”

标签： sqlsql-servercalculated-columnssubtraction

解决方案

这是计算比率的正确方法[具有可接受的性能]：

With AnnualQuotaCTE as (
    SELECT
        SUM(UnitPrice) as TotalUnitPrice,
        UnitNo
    FROM MonthlyUnitPrice
    WHERE [Year] = '1398'
    Group by UnitNo

), TotalUsageCTE as (
    SELECT
        SUM(TotalCalculatedPrice) as SumTotalCalculatedPrice,
        UnitNo
    FROM [ManagerTable]
    WHERE [Year] = '1398' AND [Month] < '9'
    Group by UnitNo
)

SELECT 
    MT.UnitNo,
    UC.UnitName,
    AnnualQuotaCTE.AnnualQuota,
    TotalUsageCTE.TotalUsage,
    (Isnull(AnnualQuotaCTE.AnnualQuota, 0) - isnull(TotalUsageCTE.TotalUsage,0)) as UsageRatio
FROM ManagerTable MT
INNER JOIN UnitCodes UC ON UC.UnitNO = MT.UnitNO
INNER JOIN MonthlyUnitPrice MU ON MT.UnitNO = MU.UnitNO AND MT.[Year] = MU.[Year] AND MT.[Month] = MU.[Month]
LEFT JOIN AnnualQuotaCTE on AnnualQuotaCTE.UnitNo = MT.UnitNo
LEFT JOIN TotalUsageCTE on TotalUsageCTE.UnitNo = MT.UnitNo
WHERE MT.[Year] = '1398' AND MT.[Month] < 9 AND MU.UnitPrice IS NOT NULL
GROUP BY MT.UnitNo, UC.UnitName, AnnualQuotaCTE.AnnualQuota, TotalUsageCTE.TotalUsage

此外，您可以将年份和月份添加到 CTE 和主查询。

推荐阅读