drop table employcreate table employ( 员工编号 int , 工资项目名称 nvarchar(100), 金额 money, 月份 datetime) go insert into employ values(00001,'基职', 145.00 ,'2003-02-01 00:00:00.000') insert into employ values(00001,'职务津贴',14.00,' 2003-02-01 00:00:00.000') insert into employ values(00001,'职务津贴',14.00,' 2003-03-01 00:00:00.000') insert into employ values(00001,'基职', 145.00,' 2003-03-01 00:00:00.000') insert into employ values(00002,'基职', 214.00,' 2003-02-01 00:00:00.000') insert into employ values(00002,'职务津贴',25.00,' 2003-02-01 00:00:00.000') insert into employ values(00002,'职务津贴',25.00,' 2003-03-01 00:00:00.000') insert into employ values(00002,'基职',214.00,' 2003-03-01 00:00:00.000') insert into employ values(00003,'基职',111.00,' 2003-02-01 00:00:00.000') insert into employ values(00003,'职务津贴',66.00,' 2003-02-01 00:00:00.000') insert into employ values(00003,'职务津贴',66.00,' 2003-03-01 00:00:00.000') insert into employ values(00003,'基职',111.00,' 2003-03-01 00:00:00.000') insert into employ values(00004,'基职',145.00,' 2003-02-01 00:00:00.000') insert into employ values(00004,'职务津贴',56.00,' 2003-02-01 00:00:00.000') insert into employ values(00004,'职务津贴', 56.00,' 2003-03-01 00:00:00.000') insert into employ values(00004,'基职',145.00,' 2003-03-01 00:00:00.000') insert into employ values(00006,'基职',56.00,' 2003-02-01 00:00:00.000') insert into employ values(00006,'职务津贴',565.00,' 2003-02-01 00:00:00.000') insert into employ values(00006,'职务津贴',565.00,' 2003-03-01 00:00:00.000') insert into employ values(00006,'基职',56.00,' 2003-03-01 00:00:00.000') insert into employ values(00007,'基职',454.00,' 2003-02-01 00:00:00.000') insert into employ values(00007,'职务津贴',52.00,' 2003-02-01 00:00:00.000') insert into employ values(00007,'职务津贴',52.00,' 2003-03-01 00:00:00.000') insert into employ values(00007,'基职', 454.00,' 2003-03-01 00:00:00.000') gosolution 1: select 员工编号,sum(金额) as 工资,月份 from employ where ((工资项目名称='基职' and 金额>100) or (工资项目名称='职务津贴' and 金额<50)) group by 员工编号 ,月份solution 2: select 员工编号,sum(case when 工资项目名称='基职' then 金额 end) as 基职, sum(case when 工资项目名称='职务津贴' then 金额 end) as 职务津贴, 月份 from employ where ((工资项目名称='基职' and 金额>100) or (工资项目名称='职务津贴' and 金额<50)) group by 员工编号,月份
create table employ( 员工编号 int , 工资项目名称 nvarchar(100), 金额 money, 月份 datetime) go insert into employ values(00001,'基职', 145.00 ,'2003-02-01 00:00:00.000') insert into employ values(00001,'职务津贴',14.00,' 2003-02-01 00:00:00.000') insert into employ values(00001,'职务津贴',14.00,' 2003-03-01 00:00:00.000') insert into employ values(00001,'基职', 145.00,' 2003-03-01 00:00:00.000') insert into employ values(00002,'基职', 214.00,' 2003-02-01 00:00:00.000') insert into employ values(00002,'职务津贴',25.00,' 2003-02-01 00:00:00.000') insert into employ values(00002,'职务津贴',25.00,' 2003-03-01 00:00:00.000') insert into employ values(00002,'基职',214.00,' 2003-03-01 00:00:00.000') insert into employ values(00003,'基职',111.00,' 2003-02-01 00:00:00.000') insert into employ values(00003,'职务津贴',66.00,' 2003-02-01 00:00:00.000') insert into employ values(00003,'职务津贴',66.00,' 2003-03-01 00:00:00.000') insert into employ values(00003,'基职',111.00,' 2003-03-01 00:00:00.000') insert into employ values(00004,'基职',145.00,' 2003-02-01 00:00:00.000') insert into employ values(00004,'职务津贴',56.00,' 2003-02-01 00:00:00.000') insert into employ values(00004,'职务津贴', 56.00,' 2003-03-01 00:00:00.000') insert into employ values(00004,'基职',145.00,' 2003-03-01 00:00:00.000') insert into employ values(00006,'基职',56.00,' 2003-02-01 00:00:00.000') insert into employ values(00006,'职务津贴',565.00,' 2003-02-01 00:00:00.000') insert into employ values(00006,'职务津贴',565.00,' 2003-03-01 00:00:00.000') insert into employ values(00006,'基职',56.00,' 2003-03-01 00:00:00.000') insert into employ values(00007,'基职',454.00,' 2003-02-01 00:00:00.000') insert into employ values(00007,'职务津贴',52.00,' 2003-02-01 00:00:00.000') insert into employ values(00007,'职务津贴',52.00,' 2003-03-01 00:00:00.000') insert into employ values(00007,'基职', 454.00,' 2003-03-01 00:00:00.000') go select a.员工编号,a.月份,a.金额+b.金额 工资 from employ a inner join employ b on a.员工编号=b.员工编号 and a.月份=b.月份 where a.工资项目名称='基职' and a.金额>100 and b.工资项目名称='职务津贴' and b.金额<50 godrop table employ
员工编号 int ,
工资项目名称 nvarchar(100),
金额 money,
月份 datetime)
go
insert into employ values(00001,'基职', 145.00 ,'2003-02-01 00:00:00.000')
insert into employ values(00001,'职务津贴',14.00,' 2003-02-01 00:00:00.000')
insert into employ values(00001,'职务津贴',14.00,' 2003-03-01 00:00:00.000')
insert into employ values(00001,'基职', 145.00,' 2003-03-01 00:00:00.000')
insert into employ values(00002,'基职', 214.00,' 2003-02-01 00:00:00.000')
insert into employ values(00002,'职务津贴',25.00,' 2003-02-01 00:00:00.000')
insert into employ values(00002,'职务津贴',25.00,' 2003-03-01 00:00:00.000')
insert into employ values(00002,'基职',214.00,' 2003-03-01 00:00:00.000')
insert into employ values(00003,'基职',111.00,' 2003-02-01 00:00:00.000')
insert into employ values(00003,'职务津贴',66.00,' 2003-02-01 00:00:00.000')
insert into employ values(00003,'职务津贴',66.00,' 2003-03-01 00:00:00.000')
insert into employ values(00003,'基职',111.00,' 2003-03-01 00:00:00.000')
insert into employ values(00004,'基职',145.00,' 2003-02-01 00:00:00.000')
insert into employ values(00004,'职务津贴',56.00,' 2003-02-01 00:00:00.000')
insert into employ values(00004,'职务津贴', 56.00,' 2003-03-01 00:00:00.000')
insert into employ values(00004,'基职',145.00,' 2003-03-01 00:00:00.000')
insert into employ values(00006,'基职',56.00,' 2003-02-01 00:00:00.000')
insert into employ values(00006,'职务津贴',565.00,' 2003-02-01 00:00:00.000')
insert into employ values(00006,'职务津贴',565.00,' 2003-03-01 00:00:00.000')
insert into employ values(00006,'基职',56.00,' 2003-03-01 00:00:00.000')
insert into employ values(00007,'基职',454.00,' 2003-02-01 00:00:00.000')
insert into employ values(00007,'职务津贴',52.00,' 2003-02-01 00:00:00.000')
insert into employ values(00007,'职务津贴',52.00,' 2003-03-01 00:00:00.000')
insert into employ values(00007,'基职', 454.00,' 2003-03-01 00:00:00.000')
gosolution 1:
select 员工编号,sum(金额) as 工资,月份 from employ
where ((工资项目名称='基职' and 金额>100) or (工资项目名称='职务津贴' and 金额<50)) group by 员工编号 ,月份solution 2:
select 员工编号,sum(case when 工资项目名称='基职' then 金额 end) as 基职, sum(case when 工资项目名称='职务津贴' then 金额 end) as 职务津贴, 月份
from employ
where ((工资项目名称='基职' and 金额>100) or (工资项目名称='职务津贴' and 金额<50))
group by 员工编号,月份
create table employ(
员工编号 int ,
工资项目名称 nvarchar(100),
金额 money,
月份 datetime)
go
insert into employ values(00001,'基职', 145.00 ,'2003-02-01 00:00:00.000')
insert into employ values(00001,'职务津贴',14.00,' 2003-02-01 00:00:00.000')
insert into employ values(00001,'职务津贴',14.00,' 2003-03-01 00:00:00.000')
insert into employ values(00001,'基职', 145.00,' 2003-03-01 00:00:00.000')
insert into employ values(00002,'基职', 214.00,' 2003-02-01 00:00:00.000')
insert into employ values(00002,'职务津贴',25.00,' 2003-02-01 00:00:00.000')
insert into employ values(00002,'职务津贴',25.00,' 2003-03-01 00:00:00.000')
insert into employ values(00002,'基职',214.00,' 2003-03-01 00:00:00.000')
insert into employ values(00003,'基职',111.00,' 2003-02-01 00:00:00.000')
insert into employ values(00003,'职务津贴',66.00,' 2003-02-01 00:00:00.000')
insert into employ values(00003,'职务津贴',66.00,' 2003-03-01 00:00:00.000')
insert into employ values(00003,'基职',111.00,' 2003-03-01 00:00:00.000')
insert into employ values(00004,'基职',145.00,' 2003-02-01 00:00:00.000')
insert into employ values(00004,'职务津贴',56.00,' 2003-02-01 00:00:00.000')
insert into employ values(00004,'职务津贴', 56.00,' 2003-03-01 00:00:00.000')
insert into employ values(00004,'基职',145.00,' 2003-03-01 00:00:00.000')
insert into employ values(00006,'基职',56.00,' 2003-02-01 00:00:00.000')
insert into employ values(00006,'职务津贴',565.00,' 2003-02-01 00:00:00.000')
insert into employ values(00006,'职务津贴',565.00,' 2003-03-01 00:00:00.000')
insert into employ values(00006,'基职',56.00,' 2003-03-01 00:00:00.000')
insert into employ values(00007,'基职',454.00,' 2003-02-01 00:00:00.000')
insert into employ values(00007,'职务津贴',52.00,' 2003-02-01 00:00:00.000')
insert into employ values(00007,'职务津贴',52.00,' 2003-03-01 00:00:00.000')
insert into employ values(00007,'基职', 454.00,' 2003-03-01 00:00:00.000')
go
select a.员工编号,a.月份,a.金额+b.金额 工资 from employ a inner join employ b
on a.员工编号=b.员工编号 and a.月份=b.月份
where a.工资项目名称='基职' and a.金额>100 and b.工资项目名称='职务津贴' and b.金额<50
godrop table employ