MSSQL创建具有多个条件的组

Question

场景:我正在收集一份客户调查。每个时间点(基线、24小时、48小时、3-5天……)等等)。表1包含ID、人口统计…等。表2包含ID、Collect Timepoints。当我执行左连接时，输出表对一个ID有多个Timepoint。下面是一个表格的例子:

ID |  HasCollected   |TimeCollect
---|-----------------|----------------
1  |  YES            |Baseline
1  |  NO             |24 Hours
1  |  NO             |48 Hours
2  |  YES            |Baseline
2  |  YES            |24 Hours
2  |  NO             |48 Hours
3  |  NO             |Baseline
3  |  YES            |24 Hours
3  |  NO             |48 Hours
4  |  NO             |Baseline
4  |  YES            |24 Hours
4  |  YES            |48 Hours

我如何知道Baseline收集了多少ID ONLY ?或只收集Baseline和24 Hours

For example, ID = 1 collect only Baseline, so he will be in the Baseline group.
ID = 2 collected Baseline and 24 Hours. He will be in the BaselineAnd24Hours group but Baseline group.
ID = 3 collected 24 Hours, he is in 24Hours group ONLY.
ID = 4 collected 24 and 48 hours. He is in 24-48Hours group ONLY. (he is not in group 24 or 48 hours.)

我试过用IIF:

IIF(HasCollected= 'YES' and TimeCollect= 'Baseline' and TimeCollect = '24 Hours', 'G-Base24','NoGroup') as G-BaseAnd24,
IIF(HasCollected= 'YES' and TimeCollect= 'Baseline', 'G-Base24','NoGroup') as G-Baseline,

或带大小写

case
when (HasCollected = 'YES' and CollectTime = 'Baseline') then 'G-Baseline'
when (HasCollected  = 'YES' and CollectTime  = '24 Hours') then 'G-24Hours'
when (HasCollected  = 'YES' and CollectTime  = '48 Hours') then 'G-48Hours'
when ((HasCollected = 'YES' and CollectTime  = 'Baseline') and (HasCollected  = 'YES' and CollectTime  = '24 Hours')) then 'G-BaselineAnd24Hours'
when ((HasCollected  = 'YES' and CollectTime  = '24 Hours') and (HasCollected  = 'YES' and CollectTime  = '48 Hours')) then 'G-24And48Hours'        
else 'NoFilter'
end as filterGroup

两种情况下的输出相似。它可以实现只有一个时间点的ID，但如果ID有两个或两个以上的时间点，它仍然使用捕获的第一个时间点，而不将它们计算在双时间点组中。

Answer 1

你的意思是这样吗?

DECLARE @Table TABLE (ID INT,HasCollected NVARCHAR(10),TimeCollect NVARCHAR(50))
INSERT @Table
VALUES
(1,'YES','Baseline'),
(1,'NO', '24 Hours'),
(1,'NO', '48 Hours'),
(2,'YES','Baseline'),
(2,'YES','24 Hours'),
(2,'NO', '48 Hours'),
(3,'NO', 'Baseline'),
(3,'YES','24 Hours'),
(3,'NO', '48 Hours'),
(4,'NO', 'Baseline'),
(4,'YES','24 Hours'),
(4,'YES','48 Hours')
SELECT TimeCollect, COUNT(*) NumIds
FROM(
SELECT DISTINCT
ID,
STUFF((SELECT ',' + t.TimeCollect
FROM @Table t
WHERE t.ID = t2.ID
AND t.HasCollected = 'YES'
FOR XML PATH('')), 1, 1,'') AS TimeCollect
FROM @Table t2
GROUP BY t2.ID
)a
GROUP BY TimeCollect

结果:

TimeCollect         NumIds
24 Hours            1
24 Hours,48 Hours   1
Baseline            1
Baseline,24 Hours   1

或者如果你有SQL Server 2017+:

SELECT TimeCollect, COUNT(*) NumIds
FROM(
SELECT ID,
STRING_AGG(TimeCollect,', ') AS TimeCollect
FROM @Table t
WHERE t.HasCollected = 'YES'
GROUP BY ID
)a
GROUP BY TimeCollect