求和(isnull(DB.budget_amount，0))超过(DB.ACCOUNT划分)为YearTotalBudget (按原因添加一行)

Question

SELECT     
    b.GlDtlTID, b.CORP, b.CORPNAME, hr.Function_Name, hr.Busunit_Name, 
    b.CostCenter, hr.Department_Code, hr.Department_Name, b.CostCenterName, 
    b.CostCenter + ' - ' + b.CostCenterName AS CostCenter_Name, 
    b.ACCOUNT, b.ACCTNAME, b.FISCAL_YEAR_PERIOD, b.TxnType, b.ACTUAL_AMT, 
    ISNULL(b.BUDGET_AMT, 0) AS BUDGET_AMT, ISNULL(b.FORECAST_AMT, 0) AS FORECAST_AMT,  
    ISNULL(b.BUDGET_AMT, 0) - b.ACTUAL_AMT AS VARIANCE_AMT, 
    b.BatchPosted, b.DESCRIPTION, b.AFE, b.AFENAME, b.VENDOR, b.VENDORNAME, b.INVOICEREF,
    SUM(ISNULL(DB.budget_amount, 0)) OVER (PARTITION BY DB.ACCOUNT) AS YearTotalBudget --Adding this line
FROM        
    BI_dept_actualbudget AS b 
LEFT OUTER JOIN   
    BI_BusUnit_HR AS hr ON b.CostCenter = hr.Hier_Code
LEFT OUTER JOIN 
    BI_Dept_Budget DB ON DB.CORP = b.CORP 
                      AND DB.CORPNAME = B.CORPNAME 
                      AND DB.dept = b.CostCenter 
                      AND DB.ACCOUNT= B.ACCOUNT -- And this line 
WHERE    
    b.account NOT IN (
        '1022-010','1022-040','2500-022','2500-023','2500-024','2500-025','2500-026','2500-030',
        '2500-035','2500-040','2500-045','2500-050','2500-055','2500-060','2500-065','2500-070','2500-075',
        '6000-010','6000-011','6000-012','6000-013','6000-015','6000-016','6000-017','6000-018','6000-019',
        '6000-110','6000-111','6000-112','6000-113','6000-115','6000-116','6000-117','6000-118','6000-119',
        '6100-010','6100-015',
        '6110-010','6110-015','6110-020','6110-025','6110-035','6110-040','6110-045','6110-050','6110-055',
        '6110-065','6110-075','6110-080','6110-085','6115-010','6115-015',
        '6120-015','6120-020','6120-025','6320-010',
        '6905-000','6905-010','6905-020','6905-030','6110-110','6110-115','6110-120','6110-135','6110-140')
  AND DB.dept = 'D1100' 
GROUP BY 
    b.GlDtlTID, b.CORP, b.CORPNAME, hr.Function_Name, hr.Busunit_Name, 
    b.CostCenter, hr.Department_Code, hr.Department_Name, b.CostCenterName, 
    b.CostCenter + ' - ' + b.CostCenterName, b.ACCOUNT, b.ACCTNAME, 
    b.FISCAL_YEAR_PERIOD, b.TxnType, b.ACTUAL_AMT, 
    ISNULL(b.BUDGET_AMT, 0), ISNULL(b.FORECAST_AMT, 0), 
    ISNULL(b.BUDGET_AMT, 0) - b.ACTUAL_AMT, 
    b.BatchPosted, b.DESCRIPTION, b.AFE, b.AFENAME, 
    b.VENDOR, b.VENDORNAME, b.INVOICEREF

我得到了这个错误：

Msg 8120，16级，状态1，第5行 列'BI_Dept_Budget.ACCOUNT‘在select列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中。

请帮帮忙。谢谢。

Answer 1

分组与分割本质上是矛盾的。使用组by时，根据列对记录进行分组。当您使用分区时，您的意图是不松散任何记录，并且仍然对每个记录使用一些聚合。

从您的查询中，我觉得如果您使用SELECT DISTINCT .只要去掉你的froup by子句，就能达到你的目的。

`SELECT  DISTINCT
b.GlDtlTID, b.CORP, b.CORPNAME, hr.Function_Name, hr.Busunit_Name, 
b.CostCenter, hr.Department_Code, hr.Department_Name, b.CostCenterName, 
b.CostCenter + ' - ' + b.CostCenterName AS CostCenter_Name, 
b.ACCOUNT, b.ACCTNAME, b.FISCAL_YEAR_PERIOD, b.TxnType, b.ACTUAL_AMT, 
ISNULL(b.BUDGET_AMT, 0) AS BUDGET_AMT, ISNULL(b.FORECAST_AMT, 0) AS FORECAST_AMT,  
ISNULL(b.BUDGET_AMT, 0) - b.ACTUAL_AMT AS VARIANCE_AMT, 
b.BatchPosted, b.DESCRIPTION, b.AFE, b.AFENAME, b.VENDOR, b.VENDORNAME, b.INVOICEREF,
SUM(ISNULL(DB.budget_amount, 0)) OVER (PARTITION BY DB.ACCOUNT) AS YearTotalBudget -- 
Adding this line
FROM        
BI_dept_actualbudget AS b 
LEFT OUTER JOIN   
BI_BusUnit_HR AS hr ON b.CostCenter = hr.Hier_Code
LEFT OUTER JOIN 
BI_Dept_Budget DB ON DB.CORP = b.CORP 
                  AND DB.CORPNAME = B.CORPNAME 
                  AND DB.dept = b.CostCenter 
                  AND DB.ACCOUNT= B.ACCOUNT -- And this line 
WHERE    
b.account NOT IN (
    '1022-010','1022-040','2500-022','2500-023','2500-024','2500-025','2500- 
026','2500-030',
    '2500-035','2500-040','2500-045','2500-050','2500-055','2500-060','2500- 
065','2500-070','2500-075',
    '6000-010','6000-011','6000-012','6000-013','6000-015','6000-016','6000- 
017','6000-018','6000-019',
    '6000-110','6000-111','6000-112','6000-113','6000-115','6000-116','6000-117','6000-118','6000-119',
    '6100-010','6100-015',
    '6110-010','6110-015','6110-020','6110-025','6110-035','6110-040','6110-045','6110-050','6110-055',
    '6110-065','6110-075','6110-080','6110-085','6115-010','6115-015',
    '6120-015','6120-020','6120-025','6320-010',
    '6905-000','6905-010','6905-020','6905-030','6110-110','6110-115','6110-120','6110-135','6110-140')
  AND DB.dept = 'D1100'`