|
|
#7
mywisdom882018-04-19 17:08
/*
-- 建立员工测试数据
create table #员工表(工号 varchar(5),姓名 varchar(10))
insert into #员工表
select 'A0001','张三' union all
select 'A0002','李四' union all
select 'A0003','王五' union all
select 'A0004','赵六' union all
select 'A0005','刘七'
-- 建立零件测试数据
create table #零件表(零件ID varchar(5),零件名称 varchar(20))
insert into #零件表
select '001','零件1' union all
select '002','零件2' union all
select '003','零件3' union all
select '004','零件4' union all
select '005','零件5'
-- 建立产量测试数据
create table #产量表(工号 varchar(5),零件ID varchar(5),产量 int)
insert into #产量表
select 'A0001','001',85 union all
select 'A0001','002',84 union all
select 'A0002','001',62 union all
select 'A0002','002',55 union all
select 'A0003','002',58 union all
select 'A0004','001',50 union all
select 'A0005','001',50 union all
select 'A0005','002',60
*/
--select * from #员工表
--select * from #零件表
--select * from #产量表
--（1）查询“001”零件比“002”零件产量高的所有员工工号和姓名
-- 方法1,用关联
select a.工号,c.姓名,a.产量,b.产量
from (select 工号,产量 from #产量表 where 零件ID='001') as a
inner join (select 工号,产量 from #产量表 where 零件ID='002') as b
on a.工号=b.工号 and a.产量>b.产量
inner join #员工表 as c on a.工号=c.工号
-- 方法2,用where
select a.工号,c.姓名,a.产量,b.产量
from (select 工号,产量 from #产量表 where 零件ID='001') as a,
(select 工号,产量 from #产量表 where 零件ID='002') as b,#员工表 as c
where a.工号=b.工号 and a.产量>b.产量 and a.工号=c.工号
--（2）查询零件平均产量大于60的员工工号和平均产量
-- 方法1
select a.工号,a.平均产量
from (select 工号,avg(cast(产量 as numeric(10,2))) as 平均产量 from #产量表 group by 工号)a
where a.平均产量>60
-- 方法2
select 工号,avg(cast(产量 as numeric(10,2))) as 平均产量 from #产量表
group by 工号 having avg(cast(产量 as numeric(10,2))) >60
--（3）查询所有员工工号、姓名、零件加工种类数、总产量
select a.工号,b.姓名,a.类数,a.总产量
from (select 工号,count(零件ID)as 类数,sum(产量) as 总产量 from #产量表 group by 工号) as a
inner join #员工表 as b on a.工号=b.工号
--（4）查询没加工过“001”零件的员工工号、姓名；
-- 方法1
select a.工号,a.姓名 from #员工表 a
where a.工号 not in(select 工号 from #产量表 where 零件ID = '001')
-- 方法2
select a.工号,a.姓名 from #员工表 a
where not exists(select 工号 from #产量表 where 零件ID = '001'and a.工号=工号)
--（5）查询加工过“001”并且也加工过编号“002”零件的员工工号、姓名；
select a.工号,a.姓名 from #员工表 a
inner join (select 工号 from #产量表 where 零件ID = '001')b on a.工号=b.工号
inner join (select 工号 from #产量表 where 零件ID = '002')c on a.工号=c.工号
--（6）统计列印各零件的产量,各产量段人数:
select 零件ID,
[100-85人数] =sum(case when 产量 <= 100 and 产量 >=85 then 1 else 0 end) ,
[85-70人数] =sum(case when 产量 <85 and 产量 >=70 then 1 else 0 end) ,
[70-60人数] =sum(case when 产量 <70 and 产量 >=60 then 1 else 0 end) ,
[<60人数] =sum(case when 产量 <60 then 1 else 0 end),
[100-85产量] =sum(case when 产量 <= 100 and 产量 >=85 then 产量 else 0 end) ,
[85-70产量] =sum(case when 产量 <85 and 产量 >=70 then 产量 else 0 end) ,
[70-60产量] =sum(case when 产量 <70 and 产量 >=60 then 产量 else 0 end) ,
[<60产量] =sum(case when 产量 <60 then 产量 else 0 end)
from #产量表
group by 零件ID
--（7）查询各零件加工量前三名的记录:(不考虑加工量并列情况)
select c.零件ID,d.零件名称,b.工号,b.姓名,c.产量,排名
from (select 工号,零件ID,产量,排名=(select count(产量)+1 as 排名 from #产量表 where a.零件ID=零件ID and a.产量<产量)
from #产量表 a)c
inner join #员工表 b on c.工号=b.工号 and c.排名<=3
inner join #零件表 d on c.零件ID=d.零件ID
order by c.零件ID,c.产量 desc
|
