作者nickerChen (天留我不留)
看板Database
標題Re: [SQL ] table中分群做count
時間Fri May 5 11:50:31 2017
po完才發現昨天joedenkidd大大已經有PO更好的解決方法
獻醜了
練習一下
select tmpA.date , tmpeA.count_a , tmpeB.count_b from
(select date,count(value) as count_a from table where value='a' group by date)
as tmpA
inner join
(select date count(value) as count_b from table where value='b' group by date)
as tmpB
on tmpA.date =tmpB.date
結果如下
date count_a count_b
----------------------------------
20010101 1 3
20010102 2 2
----------------------------------
發現因為20010103是只有a 沒有b 所以個別count的情況tmpb的結果是null
調整一下
select tmpA.date ,
isnull(tmpeA.count_a,0)as count_a ,
isnull(tmpeB.count_b,0)as count_b
from
(select date,count(value) as count_a from table where value='a' group by date)
as tmpA
left join
(select date count(value) as count_b from table where value='b' group by date)
as tmpB
on tmpA.date =tmpB.date
date count_a count_b
----------------------------------
20010101 1 3
20010102 2 2
20010103 2 0
----------------------------------
※ 引述《Czero (悠閒)》之銘言:
: id date value
: ----------------------
: 1 20010101 a
: 2 20010101 b
: 3 20010101 b
: 4 20010101 b
: 1 20010102 b
: 2 20010102 a
: 3 20010102 a
: 4 20010102 b
: 1 20010103 a
: 2 20010103 a
: 我想求得如下結果,請問SQL該如何下?
: date count_a count_b
: ---------------------------------
: 20010101 1 3
: 20010102 2 2
: 20010103 2 0
: 感謝!
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 118.163.223.52
※ 文章網址: https://www.ptt.cc/bbs/Database/M.1493956234.A.485.html
※ 編輯: nickerChen (118.163.223.52), 05/05/2017 11:51:56
推 moyasi: 有b沒a 你的sql就錯很大了 05/05 15:19
推 cutekid: 推樓上,或許可慮 full join XD 05/05 15:48
對喔,思慮不周,感謝提醒
※ 編輯: nickerChen (60.249.147.127), 05/05/2017 17:09:38