数据库的select 查询
0
[已解决问题]
解决于 2008-08-01 08:33
<P>表名 Department</P>
<P>id int 部门id<BR>code varchar(20) 部门编号<BR>name varchar(20) 部门名称</P>
<P>表名 User</P>
<P>id int 用户id </P>
<P>deptid int 部门id</P>
<P>code varchar 员工编号</P>
<P>name varchar 员工姓名</P>
<P>1.一条sql语句统计出每个部门的‘王’姓的员工有多少，要求列出部门名称，人数</P>
<P>2.一条sql语句统计出没有同名员工的部门，要求列出部门名称和员工名称</P>
<P>3。一条sql语句统计出每个部门的人数，要求列出部门名成和员工人数。</P>
<P> </P>
<P>请问大家谁会做啊？？？</P>
<P>请尽快给出答案。。</P>
<P>很急。。。</P>
<P>下边是我写的</P>
<P>我们组长说不对。。我很郁闷阿。。。</P>
<P>第一条 </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>第三条 </P>
<P>select a.name,(a.id*b.id)code<BR>from department as a,users as b<BR>where b.deptid = a.id</P>
<P> <BR> </P>
最佳答案
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
其他回答(4)
0
顶~
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
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]
其他的也类似了。
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
其他类似~~
