SQL使用多个CASE WHEN ELSE进行减法运算，导致GROUP BY问题

Question

我目前有以下查询在SSMS中工作。(仅供参考，我将列名和数据库名称作为敏感信息隐藏(

SELECT  ONE.PROJECT_CODE as 'A',
sum(ONE.HOME_VALUE) as 'B',
TWO.Charter_Gross_Margin - ISNULL(THREE.OP_OUTSTANDING, 0) as 'C',
TWO.Charter_Gross_Margin - ISNULL(THREE.OP_OUTSTANDING, 0) - sum(ONE.HOME_VALUE) as 'D',
(((TWO.Charter_Gross_Margin - ISNULL(THREE.OP_OUTSTANDING, 0)) - sum(ONE.HOME_VALUE))/2) as 'E'
FROM X.dbo.AA_CST_CENTRE_TRANSACTION_SIMPLE_VIEW as ONE
LEFT JOIN X.dbo.CFMS_Charters_FlightsAndGrossMargin_IncICFLIGHT as TWO
ON ONE.PROJECT_CODE = TWO.Charter_Number
LEFT JOIN X.dbo.TB_ACS_CARPAS_OUTSTANDING as THREE
ON ONE.PROJECT_CODE = THREE.Project and TWO.Charter_Number = THREE.Project
WHERE ONE.CT_DEADLINE between '1/2/2021' and '2/28/2021'
and ONE.COSTCENTRE_CODE= 'ACS 50% GM'
and ONE.CT_TRANTYPE= 'MSC'
GROUP BY ONE.PROJECT_CODE, TWO.Charter_Gross_Margin, THREE.OP_OUTSTANDING
Having sum(ONE.HOME_VALUE) <> 0

但是，我想做的是使SELECT中的以下三行依赖于THREE.CENTRE = 'ACS 50% GM'。

TWO.Charter_Gross_Margin - ISNULL(THREE.OP_OUTSTANDING, 0) as 'C',
TWO.Charter_Gross_Margin - ISNULL(THREE.OP_OUTSTANDING, 0) - sum(ONE.HOME_VALUE) as 'D',
TWO.Charter_Gross_Margin - ISNULL(THREE.OP_OUTSTANDING, 0)) - sum(ONE.HOME_VALUE))/2) as 'E'

如果ACS 50% GM不存在，那么我想删除ISNULL(THREE.OP_OUTSTNADING, 0)。

我认为我需要为每一行做CASE WHEN？我试过下面的，但我得到了column X.dbo.TB_ACS_CARPAS_OUTSTANDING.Centre' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause，我不是100%为什么？

如有任何帮助，我们将不胜感激！

SELECT  ONE.PROJECT_CODE as 'A',
sum(ONE.HOME_VALUE) as 'B',
CASE WHEN THREE.CENTRE= 'ACS 50% GM'
THEN TWO.Charter_Gross_Margin - ISNULL(THREE.OP_OUTSTANDING, 0)
ELSE TWO.Charter_Gross_Margin
END AS 'C',
CASE WHEN THREE.CENTRE = 'ACS 50% GM'
THEN TWO.Charter_Gross_Margin - ISNULL(THREE.OP_OUTSTANDING, 0) - sum(ONE.HOME_VALUE)
ELSE TWO.Charter_Gross_Margin - sum(ONE.HOME_VALUE)
END AS 'D',
CASE WHEN THREE.CENTRE = 'ACS 50% GM'
THEN (((TWO.Charter_Gross_Margin - ISNULL(THREE.OP_OUTSTANDING, 0)) - sum(ONE.HOME_VALUE))/2)
ELSE (TWO.Charter_Gross_Margin - sum(ONE.HOME_VALUE))/2
END AS 'E'
FROM X.dbo.AA_CST_CENTRE_TRANSACTION_SIMPLE_VIEW as ONE
LEFT JOIN X.dbo.CFMS_Charters_FlightsAndGrossMargin_IncICFLIGHT as TWO
ON ONE.PROJECT_CODE = TWO.Charter_Number
LEFT JOIN X.dbo.TB_ACS_CARPAS_OUTSTANDING as THREE
ON ONE.PROJECT_CODE = THREE.Project and TWO.Charter_Number = THREE.Project
WHERE ONE.CT_DEADLINE between '1/2/2021' and '2/28/2021'
and ONE.COSTCENTRE_CODE= 'ACS 50% GM'
and ONE.CT_TRANTYPE= 'MSC'
and THREE.CENTRE= 'ACS 50% GM'
GROUP BY ONE.PROJECT_CODE, TWO.Charter_Gross_Margin, THREE.OP_OUTSTANDING
Having sum(ONE.HOME_VALUE) <> 0

Answer 1

您正在获得

列X.dbo.TB_ACS_CARPAS_OUTSTANDING.Centre在选择列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中

因为您没有在Group BY子句中添加列THREE.CENTRE。如果你需要将其包含在SELECT中，那么你应该将其添加到Group BY条款中。您甚至可以使用IIF()来代替CASE。如果您在GROUP BY中添加THREE.CENTRE没有问题，请尝试以下代码。

SELECT  ONE.PROJECT_CODE AS 'A',
SUM(ONE.HOME_VALUE) AS 'B',
IIF(THREE.CENTRE= 'ACS 50% GM',TWO.Charter_Gross_Margin - ISNULL(THREE.OP_OUTSTANDING, 0),TWO.Charter_Gross_Margin) AS 'C',
IIF(THREE.CENTRE= 'ACS 50% GM',TWO.Charter_Gross_Margin - ISNULL(THREE.OP_OUTSTANDING, 0) - SUM(ONE.HOME_VALUE),TWO.Charter_Gross_Margin - sum(ONE.HOME_VALUE)) AS 'D',
IIF(THREE.CENTRE= 'ACS 50% GM',(((TWO.Charter_Gross_Margin - ISNULL(THREE.OP_OUTSTANDING, 0)) - SUM(ONE.HOME_VALUE))/2),((TWO.Charter_Gross_Margin - sum(ONE.HOME_VALUE))/2)) AS 'E'
FROM X.dbo.AA_CST_CENTRE_TRANSACTION_SIMPLE_VIEW as ONE
LEFT JOIN X.dbo.CFMS_Charters_FlightsAndGrossMargin_IncICFLIGHT as TWO
ON ONE.PROJECT_CODE = TWO.Charter_Number
LEFT JOIN X.dbo.TB_ACS_CARPAS_OUTSTANDING as THREE
ON ONE.PROJECT_CODE = THREE.Project and TWO.Charter_Number = THREE.Project
WHERE ONE.CT_DEADLINE between '1/2/2021' and '2/28/2021'
and ONE.COSTCENTRE_CODE= 'ACS 50% GM'
and ONE.CT_TRANTYPE= 'MSC'
GROUP BY ONE.PROJECT_CODE, TWO.Charter_Gross_Margin, THREE.OP_OUTSTANDING,THREE.CENTRE
HAVING SUM(ONE.HOME_VALUE) <> 0