我有一個名為 EventHistory 的表，其中包含表中所示的欄位。A 和 B 一起構成表的主鍵。對于每個 ke，我想計算有多少事件屬于 schedule 和 manual_schedule 并顯示其計數以及在特定鍵的單獨列中有多少是取消型別的？
樣本資料：
A B createdAt createdBy eventType
249106 1956901101 2020-01-24 12:01:21.8300000 MOC000 manual_schedule
249106 1956901101 2019-12-11 04:44:42.0000000 VSSUBR schedule
249106 1956901101 2019-12-10 13:12:32.6980000 VSSUBR cancel
249128 1956904001 2020-01-27 10:14:23.8930000 MOC000 manual_schedule
249128 1956904001 2020-01-23 09:45:20.7450000 MOC000 manual_schedule
249128 1956904001 2019-12-17 08:41:25.0000000 VSSUBR schedule
249128 1956904001 2019-11-28 11:46:40.4930000 VSSUBR hold
249128 1956904001 2019-11-28 11:46:15.8160000 VSSUBR cancel
249240 1956932501 2020-01-06 10:44:46.7620000 YUG000 manual_schedule
249240 1956932501 2020-01-02 21:09:05.0000000 A9SRI3 schedule
249240 1956932501 2019-12-31 06:41:01.3300000 KRSRIP cancel
249240 1956932501 2019-12-17 08:35:35.7490000 VSSUBR manual_schedule
249240 1956932501 2019-12-17 08:30:09.0000000 External reschedule
249240 1956932501 2019-12-17 05:56:31.0000000 VSSUBR reschedule
249240 1956932501 2019-12-17 05:55:03.8030000 VSSUBR manual_schedule
249240 1956932501 2019-12-17 05:16:04.0000000 VSSUBR reschedule
249240 1956932501 2019-12-17 05:14:46.9600000 VSSUBR manual_schedule
249240 1956932501 2019-12-17 05:14:43.0000000 VSSUBR reschedule
249240 1956932501 2019-12-17 05:13:21.2890000 VSSUBR manual_schedule
249240 1956932501 2019-12-17 05:11:51.5050000 VSSUBR manual_schedule
249240 1956932501 2019-12-13 10:02:49.7800000 KRSRIP manual_schedule
249240 1956932501 2019-12-13 10:02:49.7760000 KRSRIP manual_schedule
249240 1956932501 2019-12-12 04:20:04.1620000 VSSUBR cancel
249240 1956932501 2019-12-05 12:30:42.2630000 VSSUBR manual_schedule
249240 1956932501 2019-12-05 12:23:52.3480000 VSSUBR manual_schedule
249240 1956932501 2019-12-05 09:49:37.0000000 External reschedule
249240 1956932501 2019-12-05 09:48:42.0000000 External reschedule
249240 1956932501 2019-12-04 13:16:10.0000000 External reschedule
249240 1956932501 2019-12-04 12:59:00.0000000 External reschedule
249240 1956932501 2019-12-04 07:29:43.0000000 External schedule
uj5u.com熱心網友回復：
我認為您需要在這里進行條件聚合。我通常使用 CASE 陳述句執行此操作。底部的 select 陳述句演示了我如何使用示例資料的子集執行此操作。
IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
DROP TABLE #TEMP
CREATE TABLE #TEMP (
A INT
,B INT
,event_type NVARCHAR(50)
)
INSERT INTO #TEMP (A,B,event_type)
VALUES
('249106','1956901101','manual_schedule')
,('249106','1956901101','schedule')
,('249106','1956901101','manual_schedule')
,('249128','1956904001','manual_schedule')
,('249128','1956904001','schedule')
,('249128','1956904001','manual_schedule')
,('249128','1956904001','manual_schedule')
,('249128','1956904001','schedule')
,('249240','1956932501','manual_schedule')
,('249240','1956932501','schedule')
SELECT A
,B
,SUM(CASE WHEN event_type = 'manual_schedule' THEN 1 ELSE 0 END) [manual_schedule]
,SUM(CASE WHEN event_type = 'schedule' THEN 1 ELSE 0 END) [schedule]
FROM #TEMP
GROUP BY A
,B
樣本資料輸出和選擇陳述句輸出：
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/347373.html
標籤：sql sql-server