联盟的 SUM 查询全部关闭 1

Question

我有一个查询，其中创建了一个表，并与一个WITH name AS()连接 查询的这一部分运行良好，并按以下格式返回预期的结果：

VISIT_ID | ACTUAL WEIGHT | STATED WEIGHT | WAS WEIGHT INFO OBTAINED?
123      | 1             | 0             | 0
321      | 0             | 0             | 1
567      | 0             | 1             | 0
...

然后，我使用向其添加UNION ALL来获取列底部的总计。列ACTUAL WEIGHT和STATED WEIGHT加起来很好，但WAS WEIGHT INFO OBTAINED?列相差 1。

这些列是标志 1 和 0，如果两列[ACTUAL WEIGHT] AND [STATED WEIGHT]都NULL，则第三列将生成 1

这是我的查询：

-- VARIABLE DECLARATION AND INITIALIZATION
DECLARE @SD DATE;
DECLARE @ED DATE;
SET @SD = '2013-12-16'
SET @ED = '2013-12-16';
-- THIS CREATES A TABLE WHERE ALL THE DESIRED VISIT ID NUMBERS WILL GO
-- THIS TABLE IS A UNIQUE CLUSTER
CREATE TABLE #T1
  (
  VISIT_ID VARCHAR(20) UNIQUE CLUSTERED
  )
-- WHAT GETS INSERTED INTO #T1. IT IS QUICKER TO USE #T1 THAN @T1
INSERT INTO #T1
SELECT DISTINCT PTNO_NUM
FROM smsdss.BMH_PLM_PtAcct_V
WHERE Adm_Date BETWEEN @SD AND @ED
AND Plm_Pt_Acct_Type = 'I'
AND PtNo_Num < '20000000'
OPTION(RECOMPILE);
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++//
WITH WT AS
       (SELECT EPISODE_NO,
            MAX(CASE
                    WHEN FORM_USAGE = 'Admission'
                    AND OBSV_CD = 'A_WEIGHTOBTAINED'
                    AND DSPLY_VAL = 'Actual'
                    THEN 1
                END) AS [ACTUAL WEIGHT],
            MAX(CASE
                    WHEN FORM_USAGE = 'ADMISSION'
                    AND OBSV_CD = 'A_WEIGHTOBTAINED'
                    AND DSPLY_VAL = 'STATED'
                    THEN 1
                END) AS [STATED WEIGHT]
        FROM smsmir.obsv
        WHERE form_usage = 'ADMISSION'
        GROUP BY episode_no
        )
SELECT T1.VISIT_ID
, ISNULL(WT.[ACTUAL WEIGHT], 0) AS [ACTUAL WEIGHT]
, ISNULL(WT.[STATED WEIGHT], 0) AS [STATED WEIGHT]
, CASE
    WHEN [ACTUAL WEIGHT] IS NULL AND [STATED WEIGHT] IS NULL
    THEN 1
    ELSE 0
  END AS [WAS WEIGHT INFO OBTAINED?]
FROM #T1 T1
LEFT JOIN WT WT
ON T1.VISIT_ID = WT.episode_no
UNION ALL
SELECT 'TOTALS'
, SUM(WT.[ACTUAL WEIGHT])
, SUM(WT.[STATED WEIGHT])
, SUM(
    CASE
        WHEN [ACTUAL WEIGHT] IS NULL AND [STATED WEIGHT] IS NULL
        THEN 1
        ELSE 0
    END)
FROM #T1 T1
JOIN WT WT
ON T1.VISIT_ID = WT.[episode_no]
DROP TABLE #T1

关于为什么会发生这种情况的任何信息都会很棒。

我希望所有信息都出来，这意味着我正在尝试获得以下内容：

VISIT_ID | ACTUAL WEIGHT | STATED WEIGHT | WAS WEIGHT INFO OBTAINED?
123      | 1             | 0             | 0
321      | 0             | 0             | 1
567      | 0             | 1             | 0
...
TOTALS   | 10            | 25            | 9

Answer 1

行代码与总代码存在差异。

前者使用OUTER LEFT JOIN，而后者则限制性更强INNER JOIN。