1.单表查询
员工表empinfo结构如下:
create table empinfo(
Fempno varchar(20) not null primary key,
Fempname varchar(20) not null,
Fage number not null,
Fsalary number not null
);
假设该表有大于1000万条记录;写一个最高效的SQL查询语句,用一个SQL语句计算以下4种人种每种员工的数量。
fsalary > 9999 and fage >35
fsalary > 9999 and fage < 35
fsalary < 9999 and fage >35
fsalart < 9999 and fage > 35
select sum(case when tt.fsalary >9999 and tt.fage >35
then 1
else 0
end
) as "fsalary > 9999 and fage > 35",sum(case when tt.fsalary > 9999 and tt.fage <35
then 1
else 0
end
) as "fsalary > 9999 and fage < 35",sum(case when tt.fsalary < 9999 and tt.fage > 35
then 1
else 0
end
) as "fsalary < 9999 and fage > 35",sum(case when tt.fsalary < 9999 and fage <35
then 1
else 0
end
)as "fsalary < 9999 and fage < 35"
from empinfo tt
2.多表联合查询
请用一个SQL语句得出结果:从table1、table2中取出如table3所列格式数据(注意提供的数据及结果不准确,只是作为一个格式参考)。
表结构如下:
table1:
月份(month) 部门(dep) 业绩(yj)
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8
table2:
部门(dep) 部门名称(dname)
01 国内业务一部
02 国内业务二部
03 国内业务三部
04 国内业务部
table3:
部门(dep) 一月份 二月份 三月份
01 10 null null
02 10 8 null
03 null 5 8
04 null null 9
SQL语句:
select a.dep,sum(case when b.month=1 then b.yj else 0 end) as "一月份",
sum(case when b.month=2 then b.yj else 0 end) as "二月份",
sum(case when b.month=3 then b.yj else 0 end) as "三月份",
sum(case when b.month=4 then b.yj else 0 end) as "四月份",
sum(case when b.month=5 then b.yj else 0 end) as "五月份",
sum(case when b.month=6 then b.yj else 0 end) as "六月份",
sum(case when b.month=7 then b.yj else 0 end) as "七月份",
sum(case when b.month=8 then b.yj else 0 end) as "八月份",
sum(case when b.month=9 then b.yj else 0 end) as "九月份",
sum(case when b.month=10 then b.yj else 0 end) as "十月份",
sum(case when b.month=11 then b.yj else 0 end) as "十一月份",
sum(case when b.month=12 then b.yj else 0 end) as "十二月份",
from table2 a left join table1 b on a.dep=b.dep