我可以将两列合并为一列，然后像一列一样计算它们的值吗？

Question

我当前正在使用此 SQL 语句返回完成每条记录的计数 (RFI)：

SELECT Completed_By, COUNT(DISTINCT Unique_ID) AS RFICount FROM RFI_ 
WHERE Date_Submitted BETWEEN '20110101' AND '20110630' 
GROUP BY Completed_By

这将返回如下所示的数据集：

Completed_By     RFICount
------------     ---------
SMITH, Bob       1
DOE, John        15
WARNER, Jack     11

这就是我希望它的外观。但是，某人可以是完成或协助完成记录 (RFI) 的主要或次要人员。我想在一栏中显示这一切，无论他们是完成记录 (Completed_By) 还是协助记录 (Assisting_Analyst)。

当我使用以下 SQL 语句时，当它们需要聚合时，它将显示为单独的：

SELECT Completed_By, Assisting_Analyst, COUNT(DISTINCT Unique_ID) AS RFICount 
FROM RFI_ WHERE Date_Submitted BETWEEN '20110101' AND '20110630' 
GROUP BY Completed_By, Assisting_Analyst

这将返回以下类型的输出：

Completed_By     Assisting_Analyst     RFICount
------------     -----------------     --------
SMITH, Bob       NULL                  1
DOE, John        NULL                  15
WARNER, Jack     NULL                  7
WARNER, Jack     SMITH, Bob            4

由于 Bob Smith 协助 Jack Warner 处理了四个记录 (RFI)，因此创建了一个单独的行。但我只是希望每个人都获得个人信用并将其显示在一列中，如下所示：

Completed_By     RFICount
------------     ---------
SMITH, Bob       5
DOE, John        15
WARNER, Jack     11

在上面的示例中，他们共同处理的四条记录都归功于他们两人。当然，这意味着某些记录会被计算两次，但这正是我想要的应用程序。

我已经尝试过 UNIONS 和一堆其他东西，但它要么没有给我我想要的东西，因为我想要它显示，要么它给我一个没有数据的错误。我知道 DISTINCT 必须消失，但不知道具体如何做到这一点。这是在 SQL Server 2008 R2 中完成的。帮助？

Answer 1

SELECT t.Completed_By, COUNT(DISTINCT t.Unique_ID) AS RFICount 
    FROM (SELECT Completed_By, Unique_ID
              FROM RFI_ 
              WHERE Date_Submitted BETWEEN '20110101' AND '20110630' 
          UNION ALL
          SELECT Assisting_Analyst AS Completed_By, Unique_ID
              FROM RFI_ 
              WHERE Date_Submitted BETWEEN '20110101' AND '20110630'
                  AND Assisting_Analyst IS NOT NULL) t
    GROUP BY t.Completed_By

Answer 2

做到这一点的最佳方法可能是使用汇总，它避免了子查询，并且可能在大型数据集上表现更好。

SELECT Completed_By, Assisting_Analyst, COUNT(DISTINCT Unique_ID) AS RFICount 
FROM RFI_ WHERE Date_Submitted BETWEEN '20110101' AND '20110630' 
AND Assisting_Analyst IS NULL and Completed_By IS NOT NULL
GROUP BY Completed_By, Assisting_Analyst WITH ROLLUP

ROLLUP 意味着总值应包含在 Completed_By 行中，因此排除了 where 子句中的 Assisting 行。

Answer 3

SELECT a.Completed_By
     , COUNT(DISTINCT a.Unique_ID)
       + ( SELECT COUNT(DISTINCT b.Unique_ID)
           FROM RFI_ b
           WHERE b.Date_Submitted BETWEEN '20110101' AND '20110630' 
             AND b.Assisting_Analyst = a.Completed_By
         )
       AS RFICount
FROM RFI_  a
WHERE a.Date_Submitted BETWEEN '20110101' AND '20110630' 
GROUP BY a.Completed_By

另一种尝试：

SELECT COALESCE(first.Completed_By, second.Assisting_Analyst)
       AS Completed_By
     , COALESCE(first.RFICount,0) + COALESCE(second.RFICount,0)
       AS RFICount
FROM
  ( SELECT Completed_By
         , COUNT(DISTINCT Unique_ID) AS RFICount
    FROM RFI_ 
    WHERE Date_Submitted BETWEEN '20110101' AND '20110630' 
    GROUP BY Completed_By
  ) AS first
FULL JOIN
  ( SELECT Assisting_Analyst
         , COUNT(DISTINCT Unique_ID) AS RFICount
    FROM RFI_ 
    WHERE Date_Submitted BETWEEN '20110101' AND '20110630' 
    GROUP BY Assisting_Analyst
  ) AS second
ON first.Completed_By = second.Assisting_Analyst