select uid,count(pid) aa from t_pid group by uid union all select uid,0 as aa from t_uid where uid not in (select distinct uid from t_pid) order by uid
--try select A.uid, count(*) from t_uid A inner join t_pid B on A.uid=B.uid group by A.uid
select a.uid, count(b.pid) from t_uid a left join t_pid b on a.uid = b.uid group by a.uid order by a.uid
select a.uid, sum(case when b.pid then 1 else 0 end) from t_uid a left join t_pid b on a.uid = b.uid group by a.uid order by a.uid
-- 改一下: select a.uid,isNULL(count(b.uid),0) as cn from t_uid a left join t_pid b on a.uid=b.uid group by a.uid
--测试数据 insert into t_uid(uid) values(2001) insert into t_uid(uid) values(2002) insert into t_uid(uid) values(2003) insert into t_uid(uid) values(2004) insert into t_uid(uid) values(2005) insert into t_pid(pid,uid) values(1101,2005) insert into t_pid(pid,uid) values(1102,2005) insert into t_pid(pid,uid) values(1103,2005) insert into t_pid(pid,uid) values(1107,2002) insert into t_pid(pid,uid) values(1108,2004)--语句 select u.uid,count(p.uid) from t_uid u left join t_pid p on u.uid=p.uid group by u.uid
--生成测试数据 CREATE TABLE [dbo].[t_uid] ([uid] [int] NOT NULL) CREATE TABLE [dbo].[t_pid] ([pid] [int] NOT NULL ,[uid] [int] NULL) insert into t_uid select 2001 insert into t_uid select 2002 insert into t_uid select 2003 insert into t_uid select 2004 insert into t_uid select 2005 insert into t_pid select 1101,2005 insert into t_pid select 1102,2005 insert into t_pid select 1103,2005 insert into t_pid select 1107,2002 insert into t_pid select 1108,2004--执行查询 select a.uid,cnt=count(b.uid) from t_uid a left join t_pid b on a.uid = b.uid group by a.uid order by a.uid--输出结果 /* uid cnt ---- ---- 2001 0 2002 1 2003 0 2004 1 2005 3 */--删除测试数据 drop table t_uid,t_pid
from t_pid
group by uid
union all
select uid,0 as aa
from t_uid
where uid not in (select distinct uid from t_pid)
order by uid
select A.uid,
count(*)
from t_uid A inner join t_pid B
on A.uid=B.uid
group by A.uid
a.uid,
count(b.pid)
from
t_uid a
left join
t_pid b
on
a.uid = b.uid
group by
a.uid
order by
a.uid
a.uid,
sum(case when b.pid then 1 else 0 end)
from
t_uid a
left join
t_pid b
on
a.uid = b.uid
group by
a.uid
order by
a.uid
from t_uid a left join
t_pid b on a.uid=b.uid
group by a.uid
insert into t_uid(uid) values(2001)
insert into t_uid(uid) values(2002)
insert into t_uid(uid) values(2003)
insert into t_uid(uid) values(2004)
insert into t_uid(uid) values(2005)
insert into t_pid(pid,uid) values(1101,2005)
insert into t_pid(pid,uid) values(1102,2005)
insert into t_pid(pid,uid) values(1103,2005)
insert into t_pid(pid,uid) values(1107,2002)
insert into t_pid(pid,uid) values(1108,2004)--语句
select u.uid,count(p.uid) from t_uid u left join t_pid p
on u.uid=p.uid group by u.uid
CREATE TABLE [dbo].[t_uid] ([uid] [int] NOT NULL)
CREATE TABLE [dbo].[t_pid] ([pid] [int] NOT NULL ,[uid] [int] NULL)
insert into t_uid select 2001
insert into t_uid select 2002
insert into t_uid select 2003
insert into t_uid select 2004
insert into t_uid select 2005
insert into t_pid select 1101,2005
insert into t_pid select 1102,2005
insert into t_pid select 1103,2005
insert into t_pid select 1107,2002
insert into t_pid select 1108,2004--执行查询
select a.uid,cnt=count(b.uid)
from t_uid a left join t_pid b on a.uid = b.uid
group by a.uid order by a.uid--输出结果
/*
uid cnt
---- ----
2001 0
2002 1
2003 0
2004 1
2005 3
*/--删除测试数据
drop table t_uid,t_pid