Re: [SQL ] table中分群做count

看板Database (資料庫)作者nickerChen (天留我不留)時間7年前 (2017/05/05 11:50)推噓2(2推 0噓 0→)

留言2則, 2人參與討論串3/3 (看更多)

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