enrollment_id time source event
1 2014-06-14T09:38:29 server navigate
1 2014-06-14T09:38:39 server access
1 2014-06-14T09:38:39 server access
1 2014-06-14T09:38:48 server access
1 2014-06-14T09:41:49 browser problem
1 2014-06-14T09:41:50 browser problem
1 2014-06-14T09:42:28 browser problem
1 2014-06-14T09:42:30 browser problem
1 2014-06-14T09:43:20 browser problem
1 2014-06-14T09:43:25 browser problem
1 2014-06-14T09:43:25 server problem
1 2014-06-14T09:43:40 server problem
1 2014-06-14T09:44:29 browser page_close
1 2014-06-19T06:21:04 server navigate
1 2014-06-19T06:21:16 server access
1 2014-06-19T06:21:16 server access
1 2014-06-19T06:21:32 server access
1 2014-06-19T06:21:32 browser page_close
1 2014-06-19T06:21:45 server access
1 2014-06-19T06:21:46 browser page_close
1 2014-06-19T06:22:12 server access
這是代碼：
SELECT enrollment_id,COUNT(*)as discussion
FROM log_train
WHERE event='discussion'GROUP BY enrollment_id ORDER BY enrollment_id;
這是結果：
3 26
5 34
7 33
12 1
13 4
16 28
18 3
20 1
26 1
31 6
32 7
35 13
39 3
45 45
我想要的結果使每個enrollment_id 下的disscussion,如果是0，就顯示0.但是我得到的結果如果disscussion如果為0，則不顯示這條id和0。
請問我該怎么改呢？？
uj5u.com熱心網友回復：
enrollment_id time source event1 2014-06-14T09:38:29 server navigate
1 2014-06-14T09:38:39 server access
1 2014-06-14T09:38:39 server access
1 2014-06-14T09:38:48 server access
1 2014-06-14T09:41:49 browser problem
1 2014-06-14T09:41:50 browser problem
1 2014-06-14T09:42:28 browser problem
1 2014-06-14T09:42:30 browser problem
1 2014-06-14T09:43:20 browser problem
1 2014-06-14T09:43:25 browser problem
1 2014-06-14T09:43:25 server problem
1 2014-06-14T09:43:40 server problem
1 2014-06-14T09:44:29 browser page_close
1 2014-06-19T06:21:04 server navigate
1 2014-06-19T06:21:16 server access
1 2014-06-19T06:21:16 server access
1 2014-06-19T06:21:32 server access
1 2014-06-19T06:21:32 browser page_close
1 2014-06-19T06:21:45 server access
1 2014-06-19T06:21:46 browser page_close
1 2014-06-19T06:22:12 server access
1 2014-06-19T06:22:13 browser page_close
1 2014-06-19T06:22:22 server navigate
1 2014-06-19T06:26:43 browser problem
1 2014-06-19T06:26:43 server problem
我想要得到的結果是，每個id下邊，event為disscussion，的總數。如果是0就顯示0。但是我寫的那個代碼，如果disscussion是 0，它就直接不顯示那個id.
這是代碼：
SELECT enrollment_id,COUNT(*)as discussion
FROM log_train
WHERE event='discussion'GROUP BY enrollment_id ORDER BY enrollment_id;
這是我運行的結果：
enrollment_id disscussion
3 26
5 34
7 33
12 1
13 4
16 28
18 3
uj5u.com熱心網友回復：
這樣試試：
select sv.numbr as enrollment_id,count(0) as disscussion
from (
SELECT max(enrollment_id) as Maxenrollment_id
FROM log_train WHERE event='discussion'
) as e inner join master.dbo.spt_values as sv on sv.type='P' and sv.number between 1 and e.Maxenrollment_id
left join log_train as lt on lt.enrollment_id=sv.numbr and lt.event=e.event
WHERE GROUP BY sv.numbr
ORDER BY sv.numbr;
uj5u.com熱心網友回復：
額，沒看出來樓主說的“如果disscussion是 0，它就直接不顯示那個id.”的陳述句條件另外樓主給的測驗陳述句和結果不配套吧？
uj5u.com熱心網友回復：
樓主給出舉例的陳述句和結果不一致。我想樓主的問題大概能夠通過 這個陳述句實作
select enrollment_id,sum(case when event='discussion' then 1 else 0 end )
GROUP BY enrollment_id ORDER BY enrollment_id;
uj5u.com熱心網友回復：
select isnull(欄位,0) from 表名轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/107455.html
標籤：疑難問題
上一篇：c＃大炮打飛機多執行緒該怎么寫
下一篇：用Python語言