TSQL 不同记录计数

Question

---

我正在使用 SSMS 2008，并试图从我的表中获取 Event_names 的不同计数。但相反，当实际有 2 条记录和其他无效值时，它会返回 count = 1。我想要的是他们所属的不同 event_names 数量的总计数/消费者。此外，同一消费者的某些事件计数是不同的。但我想要一个不同的计数/消费者。以下是我的无效查询返回的部分数据：

消费者计划注册日期 event_name Event_Ct

B, Tiffany  2010-09-27 12:00:00.000 Comprehensive Clinical Assessment   1
B, Tiffany  2010-09-27 00:00:00.000 Telemedicine Comprehensive Clinical Assessment  1
B, Nickolas Tyan    2010-12-07 15:00:00.000 Comprehensive Clinical Assessment   1
B, Nickolas Tyan    2010-12-07 00:00:00.000 Telemedicine Comprehensive Clinical Assessment  1
B, Jack 2011-06-13 08:30:00.000 Comprehensive Clinical Assessment   1
B, Jack 2011-01-03 00:00:00.000 Medication Management   1
B, Victoria Lynn    2010-11-10 00:00:00.000 Telemedicine Comprehensive Clinical Assessment  3
B, Victoria Lynn    2010-12-28 00:00:00.000 Telemedicine Psychiatric Assessment 3
B, Victoria Lynn    2011-01-07 00:00:00.000 Telemedicine Psychiatric Progress Note  2
B, Victoria Lynn    2011-02-08 00:00:00.000 Telemedicine Psychiatric Progress Note  2

这是我用于上述操作的 T-SQL：

SELECT consumer, [Program Enrollment Date], event_name, [Program Quarter]
INTO #INITIATIONS
FROM #consumer_initiations   
WHERE consumer IN 
(SELECT DISTINCT Consumer 
FROM #consumer_initiations
GROUP BY Consumer 
HAVING Count(DISTINCT event_name) > 1)
ORDER BY consumer, event_name

SELECT A.consumer, A.[Program Enrollment Date], A.event_name, count(A.event_name)
FROM #INITIATIONS A 
JOIN #INITIATIONS B ON A.consumer = B.consumer AND A.event_name <> B.event_name AND A.[Program Enrollment Date] <> B.[Program Enrollment Date]
GROUP BY A.consumer, A.event_name, a.[Program Enrollment Date]

我也尝试了此查询，它为只有 2 条记录的消费者返回了正确的计数，但是对于那些> 2 条记录，它返回的计数太高：

SELECT A.consumer, count(A.event_name)
FROM #INITIATIONS A 
JOIN #INITIATIONS B ON A.consumer = B.consumer AND A.event_name <> B.event_name AND A.[Program Enrollment Date] <> B.[Program Enrollment Date]
GROUP BY A.consumer

感谢 Chris，这是有效的解决方案：

Select I.consumer, [Program Enrollment Date], event_name, countPerConsumer.[Number of Events] 
From #INITIATIONS i 
Join (Select Consumer, count(distinct event_name) as [Number of Events] From #INITIATIONS GROUP BY Consumer) countPerConsumer  
    on countPerConsumer.Consumer 

= i.consumer

Answer 1

我想我已经明白你想要做什么。如果没有，我表示歉意。像这样的东西应该对您有帮助：

Select consumer, [Program Enrollment Date], event_name, countPerConsumer.[Number of Events]
From #INITIATIONS i
Join (Select Consumer, count(distinct event_name) as [Number of Events] 
        group by Consumer)countPerConsumer 
    on countPerConsumber.consumer = i.consumer

这个想法是您加入一个根据每个消费者的不同事件计数而构建的表。您应该能够保留构建 #INITIATIONS 的部分，并将最后一个查询替换为上面的查询。希望这有帮助。