SQL查询基于过滤状态获得计数

你可以用这种不同的变化发挥：

select customerId, 
     case when HasA+HasB+HasC = 3 then 'A' 
      when HasA+HasB = 2 then 'A' 
      when HasB+HasC = 2 then 'B' 
      when HasA+HasC = 2 then 'A' 
      when HasA is null and HasB is null and HasC is not null then 'C' 
      when HasB is null and HasC is null and HasA is not null then 'A' 
      when HasC is null and HasA is null and HasB is not null then 'B' 
     end as overallStatus 
from 
    (
    select customerId, 
      max(case when Status = 'A' then 1 end) HasA, 
      max(case when Status = 'B' then 1 end) HasB, 
      max(case when Status = 'C' then 1 end) HasC 
    from tableName 
    group by customerId 
    ) as t; 

我喜欢用十字申请该类型的查询，因为它允许使用了该组的计算状态BY子句。

这里是我的解决方案与一些示例数据。

Declare @Table Table (Customerid int, Stat varchar(1)) 
INSERT INTO @Table (Customerid, Stat) 
VALUES 
(1, 'a'), 
(1 , 'b'), 
(2, 'b'), 
(2 , 'c'), 
(3, 'a'), 
(3 , 'b'), 
(3, 'c') 
SELECT 
ca.StatusGroup 
, COUNT(DISTINCT Customerid) as Total 
FROM 
@Table t 
CROSS APPLY 
    (VALUES 
     (
     CASE WHEN 
      EXISTS 
      (SELECT 1 FROM @Table x where x.Customerid = t.CustomerID and x.Stat = 'a') 
      AND EXISTS 
      (SELECT 1 FROM @Table x where x.Customerid = t.CustomerID and x.Stat = 'b') 
      THEN 'A' 
     WHEN 
      EXISTS 
      (SELECT 1 FROM @Table x where x.Customerid = t.CustomerID and x.Stat = 'b') 
      AND EXISTS 
      (SELECT 1 FROM @Table x where x.Customerid = t.CustomerID and x.Stat = 'c') 
      THEN 'B' 
      ELSE t.stat 
     END 
     ) 
    ) ca (StatusGroup) 
GROUP BY ca.StatusGroup 

我编辑这与客户打交道谁只能有一个状态......在这种情况下，它会返回A，B或C取决于客户状态