sql - 如何在 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 s 被选为相关子查询，因此您将获得表中每条记录的计数。

要解决这个问题，您必须在外部查询中使用聚合函数，而不是来自相关子查询。要获得每个状态的总数，请使用 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;

SQL Fiddle Demo

或:您可以使用 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;

Like this .