--> 测试数据:[emp] if object_id('[emp]') is not null drop table [emp] go create table [emp]([empID] int,[empName] varchar(10),[empSal] int) insert [emp] select 1,'ABC',1200 union all select 2,'BCD',2000 --> 测试数据:[sal] if object_id('[sal]') is not null drop table [sal] go create table [sal]([minSal] int,[maxSal] int,[salClass] int) insert [sal] select 1000,1500,1 union all select 1500,2200,2 --查询1 --select * from [emp] --select * from [sal]select a.empid,a.empName ,a.empSal,b.salClass,c.同级别人数,c.平均工资 from emp a left join sal b on a.empSal >=b.minSal and a.empSal<b.maxSal left join ( select b.salClass,count(*)as '同级别人数',sum(a.empSal)/count(*) as '平均工资' from emp a left join sal b on a.empSal >=b.minSal and a.empSal<b.maxSal group by b.salClass ) c on b.salClass=c.salClass where empID =1 /* empid empName empSal salClass 同级别人数 平均工资 ----------- ---------- - ---------- ----------- ----------- ----------- 1 ABC 1200 1 1 1200(1 行受影响)*/--查询2 select a.empid,a.empName ,a.empSal,b.salClass,c.同级别人数,c.平均工资, case when a.empSal=b.minSal then '最低' when a.empSal=b.maxSal then '最高' else '普通' end as '成本标准' from emp a left join sal b on a.empSal >=b.minSal and a.empSal<b.maxSal left join ( select b.salClass,count(*)as '同级别人数',sum(a.empSal)/count(*) as '平均工资' from emp a left join sal b on a.empSal >=b.minSal and a.empSal<b.maxSal group by b.salClass ) c on b.salClass=c.salClass where empID =1/* empid empName empSal salClass 同级别人数 平均工资 成本标准 ----------- ---------- ----------- ----------- ----------- ----------- ---- 1 ABC 1200 1 1 1200 普通(1 行受影响)*/
--> 测试数据:[emp]
if object_id('[emp]') is not null drop table [emp]
go
create table [emp]([empID] int,[empName] varchar(10),[empSal] int)
insert [emp]
select 1,'ABC',1200 union all
select 2,'BCD',2000
--> 测试数据:[sal]
if object_id('[sal]') is not null drop table [sal]
go
create table [sal]([minSal] int,[maxSal] int,[salClass] int)
insert [sal]
select 1000,1500,1 union all
select 1500,2200,2
--查询1
--select * from [emp]
--select * from [sal]select a.empid,a.empName ,a.empSal,b.salClass,c.同级别人数,c.平均工资
from emp a left join sal b on a.empSal >=b.minSal and a.empSal<b.maxSal
left join
(
select b.salClass,count(*)as '同级别人数',sum(a.empSal)/count(*) as '平均工资'
from emp a left join sal b on a.empSal >=b.minSal and a.empSal<b.maxSal
group by b.salClass
) c on b.salClass=c.salClass
where empID =1
/*
empid empName empSal salClass 同级别人数 平均工资
----------- ---------- - ---------- ----------- ----------- -----------
1 ABC 1200 1 1 1200(1 行受影响)*/--查询2
select a.empid,a.empName ,a.empSal,b.salClass,c.同级别人数,c.平均工资,
case when a.empSal=b.minSal then '最低' when a.empSal=b.maxSal then '最高' else '普通' end as '成本标准'
from emp a left join sal b on a.empSal >=b.minSal and a.empSal<b.maxSal
left join
(
select b.salClass,count(*)as '同级别人数',sum(a.empSal)/count(*) as '平均工资'
from emp a left join sal b on a.empSal >=b.minSal and a.empSal<b.maxSal
group by b.salClass
) c on b.salClass=c.salClass
where empID =1/*
empid empName empSal salClass 同级别人数 平均工资 成本标准
----------- ---------- ----------- ----------- ----------- ----------- ----
1 ABC 1200 1 1 1200 普通(1 行受影响)*/