sql - 根据每个 id 中存在的字段值计算计数：MSSQL

Question

我有如下数据

id       desc
1       error 1
1       captured
1       closed
2       captured
2       closed
3       closed

预期输出是：对于每个 id，如果有描述“错误 1”，它应该在 A 组中计数，否则在 B 组中。

A      B
1      2

到目前为止，我的查询是检查一个上面和一个下面。

select sum(A) as TW, sum(B) as B
from (
select 
case when lower(Description) like '%error 1%' then 1 end as A,

case when lg_description  not like '%error 1%' 
and ld_desc not like '%error 1%'
 then 1 end as B
from (
select  Description,
              lag(Description)  
                over (partition by DSurveillanceAssetFaultID order by TimestampUTC) as lg_description,
                lead(Description)  
                over (partition by  DSurveillanceAssetFaultID order by TimestampUTC) as ld_desc
        from  #temp1) a
)b
group by Format(TimestampUTC, 'MMMM-yyyy')

标签： sqlsql-server

Answer 1

使用条件聚合：

select count(distinct case when description = 'error 1' then id end) as A
       count(distinct id) 
        - count(distinct case when description = 'error 1' then id end) as B
  from your_Table t