Re: [SQL ] table中分群做count
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
推
05/05 15:19, , 1F
05/05 15:19, 1F
推
05/05 15:48, , 2F
05/05 15:48, 2F
對喔，思慮不周，感謝提醒
※ 編輯: nickerChen (60.249.147.127), 05/05/2017 17:09:38
討論串 (同標題文章)