作者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://webptt.com/cn.aspx?n=bbs/Database/M.1493956234.A.485.html
※ 编辑: nickerChen (118.163.223.52), 05/05/2017 11:51:56
1F：推 moyasi: 有b没a 你的sql就错很大了 05/05 15:19
2F：推 cutekid: 推楼上，或许可虑 full join XD 05/05 15:48
对喔，思虑不周，感谢提醒
※ 编辑: nickerChen (60.249.147.127), 05/05/2017 17:09:38