时间戳

首页 > 解决方案 > SQL 减法使用多个 CASE WHEN ELSE 导致 GROUP BY 问题

sql - SQL 减法使用多个 CASE WHEN ELSE 导致 GROUP BY 问题

问题描述

我目前在 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但是，我想要做的是在依赖 if中有以下三行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

标签： sqlsql-servertsql

解决方案

你得到

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

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

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

推荐阅读