加个all,
SELECT ptype.ptypeid,COUNT(p.pid) as pcount
FROM ptype INNER JOIN p ON ptype.ptypeid = p.ptypeid
GROUP by all ptype.ptypeid
SELECT ptype.ptypeid,COUNT(p.pid) as pcount
FROM ptype INNER JOIN p ON ptype.ptypeid = p.ptypeid
GROUP by all ptype.ptypeid
pcount=(select count(*) from p where ptypeid=tmp.ptypeid)
from ptype as tmp
insert p select 1, 1
union all select 2, 1
union all select 3, 2
union all select 4, 2create table ptype(ptypeid int)
insert ptype select 1
union all select 2
union all select 3
union all select 4select ptypeid,
pcount=(select count(*) from p where ptypeid=tmp.ptypeid)
from ptype as tmp--result
ptypeid pcount
----------- -----------
1 2
2 2
3 0
4 0(4 row(s) affected)
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)
FROM ptype left JOIN p ON ptype.ptypeid = p.ptypeid
GROUP by ptype.ptypeid