Sql-Server

查詢以僅過濾具有不同值的分組

  • November 15, 2021

我需要一個簡單的查詢,但找不到方法:

我有一張客戶購買的服務表,其中不同的客戶可能會購買相同的服務(很簡單),每個客戶可能有幾個活躍或不活躍的服務:

ServiceId  IsActive ServiceCustomerId
815        0        111
715        0        111
985        1        222
815        1        333
475        1        111
985        1        111
815        1        222

我想獲取同時具有活動和非活動服務的客戶的記錄,因此輸出將是(ServiceId可以省略):

IsActive ServiceCustomerId
0        111
1        111

我所擁有的是:

select ServiceCustomerId,IsActive from Services 
group by ServiceCustomerId, IsActive
having count(IsActive) > 1
order by ServiceCustomerId

這使:

IsActive ServiceCustomerId
0        111
1        111
1        222

展示可以在這裡看到

如何僅過濾同時具有這兩個IsActive組的人?

怎麼樣

select ServiceCustomerId, sum(case when IsActive=1 then 1 else 0 end) ActiveServices, count(*) TotalServices 
from TServices 
group by ServiceCustomerId
having sum(case when IsActive=1 then 1 else 0 end) between 1 and count(*)-1
order by ServiceCustomerId

引用自:https://dba.stackexchange.com/questions/302606