时间戳

首页 > 解决方案 > 在什么情况下如何使用 IS NOT NULL？

sql - 在什么情况下如何使用 IS NOT NULL？

问题描述

我在 SSMS 中有以下 SQL 查询，我想排除NULL出现在“公司间预算”列中的行。

我试图搜索答案并为 加上一个WHERE子句'Intercompany Budget' IS NOT NULL，就像在下面的查询中一样，但NULL行仍然显示。

有什么建议么？谢谢！

SELECT 
    ONE.PROJECT_CODE AS 'Charter Number',
    TWO.Charter_sales_amount - SUM(ONE.HOME_VALUE) AS 'Gross Margin',
    SUM(CASE WHEN (ONE.COSTCENTRE_CODE = 'ACS 50% GM') THEN (ONE.HOME_VALUE) END) AS 'Intercompany Budget',
    (TWO.Charter_sales_amount - SUM(ONE.HOME_VALUE)) - SUM(CASE WHEN (ONE.COSTCENTRE_CODE = 'ACS 50% GM') THEN (ONE.HOME_VALUE) ELSE 0 END) AS 'Difference',
    ((TWO.Charter_sales_amount - SUM(ONE.HOME_VALUE)) - SUM(CASE WHEN (ONE.COSTCENTRE_CODE = 'ACS 50% GM') THEN (ONE.HOME_VALUE) ELSE 0 END)) / 2 AS 'Budget Adjustment Required'
FROM 
    dbo.AA_CST_CENTRE_TRANSACTION_SIMPLE_VIEW AS ONE 
LEFT JOIN 
    dbo.CFMS_Charters_FlightsAndGrossMargin_IncICFLIGHT AS TWO ON ONE.PROJECT_CODE = TWO.Charter_Number
WHERE 
    ONE.CT_DEADLINE BETWEEN '2/1/2021' AND '5/15/2021'
    AND ONE.CT_TRANTYPE= 'MSC'
    AND 'Intercompany Budget' IS NOT NULL
GROUP BY 
    ONE.PROJECT_CODE, TWO.Charter_sales_amount, TWO.Charter_Gross_Margin
HAVING 
    SUM(ONE.HOME_VALUE) <> 0
ORDER BY 
    one.PROJECT_CODE

标签： sqlsql-server

解决方案

'Intercompany Budget' IS NOT NULL永远不可能是真的，'Intercompany Budget'是一个文字字符串，因此没有 value NULL。这实际上是为什么使用文字字符串作为别名是一个坏习惯的原因之一，因为它会导致这样的误解。'Intercompany Budget'不引用使用AS 'Intercompany Budget';定义的别名的列 它实际上是一个文字字符串。只有在别名中，语法才被（不幸地）接受。

如果必须使用需要定界标识的别名，则使用方言的定界标识符，[]T-SQL 中的方括号 ( ) 或 ANSI 定界标识符，双引号 ( ")。但是，理想情况下，根本不要使用需要定界标识的别名/名称。我倾向于使用 PascalCase（如下图所示）。

至于过滤掉行，一种方法是使用 CTE：

WITH CTE AS(
    SELECT ONE.PROJECT_CODE AS CharterNumber,
           TWO.Charter_sales_amount - SUM(ONE.HOME_VALUE) AS GrossMargin,
           SUM(CASE WHEN (ONE.COSTCENTRE_CODE = 'ACS 50% GM') THEN (ONE.HOME_VALUE) END) AS IntercompanyBudget,
           (TWO.Charter_sales_amount - SUM(ONE.HOME_VALUE))
           - SUM(CASE WHEN (ONE.COSTCENTRE_CODE = 'ACS 50% GM') THEN (ONE.HOME_VALUE)
                     ELSE 0
                 END) AS Difference,
           ((TWO.Charter_sales_amount - SUM(ONE.HOME_VALUE))
            - SUM(CASE WHEN (ONE.COSTCENTRE_CODE = 'ACS 50% GM') THEN (ONE.HOME_VALUE)
                      ELSE 0
                  END)) / 2 AS BudgetAdjustmentRequired
    FROM.dbo.AA_CST_CENTRE_TRANSACTION_SIMPLE_VIEW ONE
        LEFT JOIN.dbo.CFMS_Charters_FlightsAndGrossMargin_IncICFLIGHT TWO ON ONE.PROJECT_CODE = TWO.Charter_Number
    WHERE ONE.CT_DEADLINE BETWEEN '2/1/2021' AND '5/15/2021'
      AND ONE.CT_TRANTYPE = 'MSC'
    GROUP BY ONE.PROJECT_CODE,
             TWO.Charter_sales_amount,
             TWO.Charter_Gross_Margin
    HAVING SUM(ONE.HOME_VALUE) <> 0
    ORDER BY ONE.PROJECT_CODE)
SELECT CharterNumber,
       GrossMargin,
       IntercompanyBudget,
       Difference,
       BudgetAdjustmentRequired
FROM CTE
WHERE IntercompanyBudget IS NOT NULL;

推荐阅读