如何在sql server 2008中使用子查询？

Question

我知道这个问题可能会重复..。但是我有这样的查询，我的表中有一个字段"Compute_CRM_State“。该字段包含“已批准、待处理、已取消”之类的数据，但现在我想获取已批准、待处理或已取消的总数。我试着用子查询。我可以获得已批准、待处理或取消的总计数，但是..我多次得到相同的记录。

​

查询：

SELECT        (SELECT        COUNT(Compute_CRM_State) AS Expr1
                          FROM            CRM_Doctor_Request
                          WHERE        (CRM_State_Id = 1)) AS PENDING,
                             (SELECT        COUNT(Compute_CRM_State) AS Expr2
                               FROM            CRM_Doctor_Request AS CRM_Doctor_Request_3
                               WHERE        (CRM_State_Id = 2)) AS Approved,
                             (SELECT        COUNT(Compute_CRM_State) AS Expr3
                               FROM            CRM_Doctor_Request AS CRM_Doctor_Request_2
                               WHERE        (CRM_State_Id = 3)) AS CANCELLED
FROM            CRM_Doctor_Request AS CRM_Doctor_Request_1

应仅显示1条计数记录。但它显示的是表中记录数量包含的次数。

Answer 1

您没有正确地获取总计数而是获取了许多记录的原因是，COUNT被选为相关子查询，因此您将获得表中每条记录的计数。

要解决这个问题，必须在外部查询中使用聚合函数，而不是在相关子查询中。要获得每个状态的总计数，请使用CASE表达式，如下所示：

SELECT
  SUM(CASE WHEN CRM_State_Id = 1 THEN 1 ELSE 0 END) AS PENDING,
  SUM(CASE WHEN CRM_State_Id = 2 THEN 1 ELSE 0 END) AS Approved,
  SUM(CASE WHEN CRM_State_Id = 3 THEN 1 ELSE 0 END) AS CANCELLED
FROM  CRM_Doctor_Request;

或：您可以使用PIVOT表运算符执行相同的操作：

SELECT 
  [1] AS PENDING, 
  [2] AS Approved, 
  [3] AS Cancelled
FROM 
( 
   SELECT * FROM CRM_Doctor_Request
) AS t
PIVOT
(
  COUNT(Compute_CRM_State)
  FOR  CRM_State_Id IN([1], [2], [3])
) AS p;

。