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

2005-10-18 05:20:24 205 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

c++中txt文件的读取以及在MFC中读取txt坐标数据并完成图形绘制主要介绍如何读取txt文件中的坐标数据，并在MFC窗口中绘制出来，工程创建方法和绘图方法与上一篇博文基本一致，这里就不再详赘述，可参考上一篇博文vs2010、MFC窗口中绘制点、线、面。 C++中读取文件的方法有两种，一种是来自于C语言的“文件指针”方法，另一种是C++中的“文件流”思想。 1、文件指针 FILE * fopen(const char * path,const char * ... 阅读详情

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

...全文

MFC C++代码与WebBrowser HTML的互动（还需完善） testWebBrowser.h testWebBrowser.cpp tset.html 阅读详情

xjb_netboy

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

子陌红尘 2005-10-18

打赏
举报

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

singlepine 2005-10-18

打赏
举报

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

WangZWang 2005-10-18

打赏
举报

-- 改一下:

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

子陌红尘 2005-10-18

打赏
举报

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

子陌红尘 2005-10-18

打赏
举报

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

zlp321002 2005-10-18

打赏
举报

--try
select A.uid,
count(*)
from t_uid A inner join t_pid B
on A.uid=B.uid
group by A.uid

Alang_79 2005-10-18

打赏
举报

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

WangZWang 2005-10-18

打赏
举报

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

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

相关推荐