求一sql,我都想了半天了，没有结果！！！！！

[xjb_netboy]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_uid]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t_uid]
GO

CREATE TABLE [dbo].[t_uid] (
[uid] [int] NOT NULL --单位的编号
) ON [PRIMARY]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_pid]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t_pid]
GO

CREATE TABLE [dbo].[t_pid] (
[pid] [int] NOT NULL , --个人编号
[uid] [int] NULL --单位编号
) ON [PRIMARY]
GO




--给出表t_uid的数据

uid
------------------------------------------------
2001
2002
2003
2004
2005



--给出表t_pid的数据

pid uid
------------------------------------------------
1101 2005
1102 2005
1103 2005
1107 2002
1108 2004



依据t_uid表中的uid字段得出,t_pid表中的uid字段记录有几个，如果没有,那么为0
要求是求一sql,得到结果：就是要显示，uid,uid在t_pid中有几条记录,能写出这样的sql吗，我都想了半天了，没有头绪？


2001 0
2002 1
2003 0
2004 1
2005 3

[WangZWang]

select a.uid,count(1) as cn
from t_uid a inne join
t_pid b on a.uid=b.uid
group by a.uid

[Alang_79]

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

[zlp321002]

--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

[WangZWang]

-- 改一下:

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

[singlepine]

--测试数据
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