一个比较难的SQL题目

[xiaogua]

有表结构
员工表(Tuser)
u_id,d_id,u_name(用户ID,部门ID,用户名字)
01 张三 01
02 王五 01
03 小照 02
04 小林 03
05 天天 03
06 呖呖 03
07 菲菲


部门表(Tdep)
d_id,d_name(部门ID,部门名字)
01 技术部
02 财务部
03 市场部
04 企划部

薪资表(Tsalary)
u_id,year,mouth,salary(用户ID,工资年份,工资月份,金额)
01 2005 1 1000
01 2005 2 1500
01 2005 3 1500
02 2005 1 1500
02 2005 2 2000
02 2005 3 2500
03 2005 1 2500
03 2005 2 2500
03 2005 3 2500
04 2005 1 1800
04 2005 2 1900
04 2005 3 2000
05 2005 1 1500
05 2005 2 1700
05 2005 3 1800
06 2005 1 1200
06 2005 2 1500
06 2005 3 1700



1.要求用SQL找出部门编号,部门总人数,部门平均工资

[software51]

Select a.DeptID,b.DeptName, [部門總人數]=Count(*),[部門平均工資]=
avg(c.Salary) From tblEmployee a
Join tblDept b On a.DeptID=b.DeptID
Join tblSalary c on a.EmployeeID=c.EmployeeID
Group by a.DeptID,b.DeptName

[黑马腾飞]

select d.d_id,d.d_name,s.mouth,isNull(avg(s.salary),0)
from Tdep d
left join Tuser u on u.d_id=d.d_id
left join Tsalary s on u.u_id=s.u_id
group by d.d_id,d.d_name,s.mouth

[jevin8011]

Select a.d_id as '部门编号',Count(*) as '部门总人数',Sum(Salary)/Count(*) as '部门平均工资' 
from TDep a inner join TUser b on a.d_id = b.d_id inner join TSalary c on b.u_id = c.u_id
Group by a.d_id

[Aslan_]

理解错误，等等先

[Aslan_]

select 部门编号 = [d_id],年份 = [year],月份= [month],部门总人数 = count([u_id]),平均工资=avg([salary])
from
(select [Tuser].[u_id],[Tdep].[d_id],[Tsalary].[year],[Tsalary].[month],[Tsalary].[salary] from
[dbo].[Tuser] inner join [dbo].[Tdep] on [dbo].[Tuser].[d_id] = [dbo].[Tdep].[d_id]
inner join [dbo].[Tsalary] on [dbo].[Tuser].[u_id] = [dbo].[Tsalary].[u_id]) as a
group by [d_id],[year],[month]
go

 部门编号 年份 月份 部门总人数 平均工资
1	2005	1	2	1250
1	2005	2	2	1750
1	2005	3	2	2000
2	2005	1	1	2500
2	2005	2	1	2500
2	2005	3	1	2500
3	2005	1	3	1500
3	2005	2	3	1700
3	2005	3	3	1833

[午夜还在张]

同意七樓的答案

[hjj841020]

select d_id,count(d_id) rs,sum(salary)/count(d_id) pj from
(select a.*,b.d_name from Tuser a left join Tdep b on a.d_id=b.d_id)a left join
(
select u_id ,max(salary) salary from Tsalary
group by u_id)b on a.u_id=b.u_id
group by d_id

[Aslan_]

if object_id(N'[dbo].[Tsalary]') is not null
drop table [dbo].[Tsalary]
go
if object_id(N'[dbo].[Tuser]') is not null
drop table [dbo].[Tuser]
go
if object_id(N'[dbo].[Tdep]') is not null
drop table [dbo].[Tdep]

go
create table [dbo].[Tuser](u_id smallint primary key,d_id smallint,u_name varchar(50))
go
create table [dbo].[Tsalary](u_id smallint foreign key references [dbo].[Tuser]([u_id]), [year] smallint ,[month] smallint,salary smallint)
go
create table [dbo].[Tdep](d_id smallint primary key,d_name varchar(50))
go

insert [dbo].[Tuser]
select 1,1,'张三' union all
select 2,1,'王五' union all
select 3,2,'小照' union all
select 4,3,'小林' union all
select 5,3,'天天' union all
select 6,3,'呖呖' union all
select 7,null,'菲菲'
go

insert [dbo].[Tdep]
select 1,'技术部' union all
select 2,'财务部' union all
select 3,'市场部' union all
select 4,'企划部'
go

insert [dbo].[Tsalary]
select 1,2005,1,1000 union all
select 1,2005,2,1500 union all
select 1,2005,3,1500 union all
select 2,2005,1,1500 union all
select 2,2005,2,2000 union all
select 2,2005,3,2500 union all
select 3,2005,1,2500 union all
select 3,2005,2,2500 union all
select 3,2005,3,2500 union all
select 4,2005,1,1800 union all
select 4,2005,2,1900 union all
select 4,2005,3,2000 union all
select 5,2005,1,1500 union all
select 5,2005,2,1700 union all
select 5,2005,3,1800 union all
select 6,2005,1,1200 union all
select 6,2005,2,1500 union all
select 6,2005,3,1700
go

select  部门编号 = [dbo].[Tdep].[d_id],平均工资 = avg([dbo].[Tsalary].[salary]) from
[dbo].[Tuser] inner join [dbo].[Tdep] on [dbo].[Tuser].[d_id] = [dbo].[Tdep].[d_id]
inner join [dbo].[Tsalary] on [dbo].[Tuser].[u_id] = [dbo].[Tsalary].[u_id]
group by [dbo].[Tdep].[d_id]

1	1666
2	2500
3	1677

[milizi820]

还是错了

[milizi820]

终于做出来了
Select a.d_id as '部门编号', count(*) as '部门人数',max(salary) as '部门平均工资'
from TDep a inner join TUser b on a.d_id = b.d_id inner join (select avg(salary) as salary,Tdep.d_id from Tsalary
inner join Tuser on Tsalary.u_id=Tuser.u_id inner join Tdep on Tuser.d_id = Tdep.d_id group by Tdep.d_id) c on b.d_id = c.d_id
Group by a.d_id

[milizi820]

员工表(Tuser)
u_id,u_name,d_id(用户ID,用户名字,部门ID)
01 张三 01
02 王五 01
03 小照 02
04 小林 03
05 天天 03
06 呖呖 03
07 菲菲 部门表(Tdep)
d_id,d_name(部门ID,部门名字)
01 技术部
02 财务部
03 市场部
04 企划部
薪资表(Tsalary)
u_id,year,mouth,salary(用户ID,工资年份,工资月份,金额)
01 2005 1 1000
01 2005 2 1500
01 2005 3 1500
02 2005 1 1500
02 2005 2 2000
02 2005 3 2500
03 2005 1 2500
03 2005 2 2500
03 2005 3 2500
04 2005 1 1800
04 2005 2 1900
04 2005 3 2000
05 2005 1 1500
05 2005 2 1700
05 2005 3 1800
06 2005 1 1200
06 2005 2 1500
06 2005 3 1700


1.要求用SQL找出部门编号,部门总人数,部门平均工资
select Tdep.d_id'编号',a.countid'总人数',avg(salary)'平均工资'from Tdep inner join (select d_id,count(d_id)'countid' from Tuser group by d_id)a on Tdep.d_id=a.d_id inner join Tsalary
on Tuser.u_id=Tdep.u_id

[milizi820]

select Tdep.d_id'编号',a.countid'总人数'from Tdep inner join (select d_id,count(d_id)'countid' from Tuser group by d_id)a on Tdep.d_id=a.d_id
不知道怎么统计金额

[milizi820]

select avg(salary)'平均工资',Tdep.d_id'编号',count(Tuser.d_id)'总人数' from Tsalary inner join Tuser on Tsalary.u_id=Tuser.u_id
inner join Tdep on Tuser.d_id = Tdep.d_id group by Tdep.d_id

[Realue]

SELECT a.[year] AS 工资年份, a.mouth AS 工资月份, c.d_id AS 部门编号, COUNT(*) 
      AS 人数, Avg(a.salary) AS 平均工资
FROM dbo.Tsalary a LEFT OUTER JOIN
      dbo.Tuser b ON a.u_id = b.u_id LEFT OUTER JOIN
      dbo.Tdep c ON b.d_id = c.d_id
GROUP BY c.d_id, a.[year], a.mouth

[flyidealism]

如果是同年的可以不加年份的判断
SQL语句如下:

select d.d_id,isnull(s.[month],0) as [month],count(distinct(u.u_id)) as[unum],isnull(avg(s.salary),0) as [savg]
from Tdep d left join Tuser u
on d.d_id=u.d_id
left join Tsalary s
on u.u_id=s.u_id
group by d.d_id,s.[month]
order by d.d_id

d_id       month unum        savg        
---------- ----- ----------- ----------- 
01         1     2           1250
01         2     2           1750
01         3     2           2000
02         1     1           2500
02         2     1           2500
02         3     1           2500
03         1     3           1500
03         2     3           1700
03         3     3           1833
04         0     0           0

（所影响的行数为 10 行）

[flyidealism]

上面的有些问题，没有完全符合题目要求，现在重新发一个

select a.d_id,a.[unum] as [部门总人数],a.[savg] as [部门平均工资] 
from Tdep b right join
(select d.d_id,isnull(s.[month],0) as [month],count(distinct(u.u_id)) as[unum],isnull(avg(s.salary),0) as [savg]
from Tdep d left join Tuser u
on d.d_id=u.d_id
left join Tsalary s
on u.u_id=s.u_id
group by d.d_id,s.[month]
) a
on b.d_id=a.d_id
order by a.d_id
d_id       部门总人数       部门平均工资      
---------- ----------- ----------- 
01         2           1250
01         2           1750
01         2           2000
02         1           2500
02         1           2500
02         1           2500
03         3           1500
03         3           1700
03         3           1833
04         0           0

（所影响的行数为 10 行）

[ai19811125]

select avg(salary),d_name from (select b.*,s.salary from (select d.*,u.u_name,u.u_id from tdep d left join tuser u on d.d_id=u.d_id) b left join tsalary s
on b.u_id=s.u_id) t group by d_name

[zhangtq]

select b.d_id,b.d_name,counts,mouth,avgsalary
from (
select t.d_id,count(*)as counts
from tuser t
group by d_id
)a,
(
select d_id,mouth,avg(salary)as avgsalary
from(
select n.u_id,mouth,n.salary,m.d_id
from tsalary n,tuser m
where n.u_id = m.u_id
)
group by d_id,mouth
)c,
tdep b
where a.d_id = b.d_id
and b.d_id = c.d_id

[UltraBejing]

以后需再关注，现在先帮你顶一下

[rybhgr]

两种方式：
1.
select Tdep.d_id as '部门Id',count(Tuser.u_id) as '部门人数',Tsalary.year as '年份',Tsalary.mouth as '月份',avg(Tsalary.salary) as '部门平均工资'
from Tdep
left join Tuser on Tdep.d_id=Tuser.d_id
left join Tsalary on Tsalary.u_id=Tuser.u_id
group by Tdep.d_id,Tsalary.mouth,Tsalary.year
order by Tdep.d_id
结果：
部门Id 部门人数 年份 月份 部门平均工资
1 2 2005 1 1250
1 2 2005 2 1750
1 2 2005 3 2000
2 1 2005 1 2500
2 1 2005 2 2500
2 1 2005 3 2500
3 3 2005 1 1500
3 3 2005 2 1700
3 3 2005 3 1833
4 0 NULL NULL NULL

2.
select a.d_id,a.Count as '人数',avg(b.January) as '1月', avg(b.February) as '2月', avg(b.March) as '3月' from
(select Tdep.d_id as 'd_id',count(Tuser.u_id) as 'Count'
from Tdep left join Tuser on Tuser.d_id=Tdep.d_id
group by Tdep.d_id) as a
left join
(select Tuser.u_id,Tuser.u_name,Tuser.d_id,
sum(case when Tsalary.mouth=1 then salary else 0 end ) as 'January',
sum(case when Tsalary.mouth=2 then salary else 0 end) as 'February',
sum(case when Tsalary.mouth=3 then salary else 0 end) as 'March'
from Tsalary left join Tuser on Tsalary.u_id=Tuser.u_id
group by Tuser.u_id,Tuser.u_name,Tuser.d_id) as b
on a.d_id=b.d_id
group by a.d_id,a.Count
结果：
d_id 人数 1月 2月 3月
1 2 1250 1750 2000
2 1 2500 2500 2500
3 3 1500 1700 1833
4 0 NULL NULL NULL