首页 新闻 赞助 找找看

数据库的select 查询

0
[已解决问题] 解决于 2008-08-01 08:33
<P>表名 Department</P> <P>id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; int&nbsp;&nbsp;&nbsp; 部门id<BR>code&nbsp;varchar(20)&nbsp; 部门编号<BR>name&nbsp;varchar(20)&nbsp;部门名称</P> <P>表名&nbsp; User</P> <P>id&nbsp;&nbsp; int&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;用户id&nbsp;</P> <P>deptid&nbsp; int&nbsp;&nbsp; 部门id</P> <P>code&nbsp;&nbsp; varchar 员工编号</P> <P>name&nbsp; varchar&nbsp;员工姓名</P> <P>1.一条sql语句统计出每个部门的‘王’姓的员工有多少,要求列出部门名称,人数</P> <P>2.一条sql语句统计出没有同名员工的部门,要求列出部门名称和员工名称</P> <P>3。一条sql语句统计出每个部门的人数,要求列出部门名成和员工人数。</P> <P>&nbsp;</P> <P>请问大家谁会做啊???</P> <P>请尽快给出答案。。</P> <P>很急。。。</P> <P>下边是我写的</P> <P>我们组长说不对。。我很郁闷阿。。。</P> <P>第一条&nbsp;</P> <P>select a.name,b.name,(a.id*b.code)code<BR>from department as a,users as b<BR>where b.deptid = a.id and b.name like '%王%'</P> <P>第二条<BR>select b.name,a.name<BR>from department as a,users as b<BR>where b.deptid = a.id and b.name like '%%'</P> <P>第三条&nbsp;</P> <P>select a.name,(a.id*b.id)code<BR>from department as a,users as b<BR>where&nbsp; b.deptid = a.id</P> <P>&nbsp;<BR>&nbsp;&nbsp;</P>
冰儿的主页 冰儿 | 初学一级 | 园豆:200
提问于:2008-07-31 11:26
< >
分享
最佳答案
0
--1. select a.name, b.num from Department a inner join (select deptid,count([id]) as num from [user] where [name] like '王%' group by deptid ) b on a.id=b.deptid --2. select a.name, b.name from Department a inner join [User] b on a.id=b.deptid where a.id not in (select deptid from [user] where name in (select name from [user] group by name having count(name)>1) ) --3. select a.name, count(a.name) from Department a inner join [User] b on a.id=b.deptid group by a.name
丁学 | 专家六级 |园豆:18730 | 2008-07-31 13:00
其他回答(4)
0
顶~
like%'远远'% | 园豆:635 (小虾三级) | 2008-07-31 12:08
0
1.select d.name,count(u.code) from user as u inner join Department as d on u.id = d.id where u.name like '王%' 2.暂无 3.select count(u.code),d.name from user as u iner join Department as d on u.deptid = d.id
金鱼 | 园豆:1090 (小虾三级) | 2008-07-31 12:41
0
[code] with wangCn as ( select distinct cnOfWang = count(*) OVER(PARTITION BY u.deptid) ,u.deptid as departmentid from [user] u where left(u.name ,1) = '王' )select departmentName = d.name, 姓王员工数= wangCn.cnOfWang from wangCn inner join department d ON wangCn.departmentid = d.id [/code] 其他的也类似了。
玉开 | 园豆:8822 (大侠五级) | 2008-07-31 12:52
0
你上面的要求都是在做统计数据啊~~ 数据统计一般都要用到:count \sum\group by\having\cube\rollup\ouder by等 像你的第一个要求: select a.name as 部门名,sum(b.id) as 王姓人数 from department as a,users as b where b.depid=a.id and left(b.naem,1)='王'--第一个为姓 group by a.name 其他类似~~
照清 | 园豆:205 (菜鸟二级) | 2008-07-31 12:52
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册