关于GROUP BY 语句
表 p
pid ptypeid
1 1
2 1
3 2
4 2
表ptype
ptypeid
1
2
3
4
一个sql语句
SELECT ptype.ptypeid,COUNT(p.pid) as pcount
FROM ptype INNER JOIN p ON ptype.ptypeid = p.ptypeid
GROUP by ptype.ptypeid
得到如下表
----------------
ptypeid pcount
1 2
2 2
我想得到如下表 怎么写？
--------------------
ptypeid pcount
1 2
2 2
3 0
4 0
[解决办法]
select ptype.*,
pcount=isnull(p.pcount, 0)
from ptype
left join
(
select ptypeid, count(*) as pcount
from p
group by ptypeid
)p on ptype.ptypeid=p.ptypeid
--result
ptypeid pcount
----------- -----------
1 2
2 2
3 0
4 0
(4 row(s) affected)
