有员工表empinfo
(fempno varchar2(10) not null pk,
fempname varchar2(20) not null,
fage number not null,
fsalary number not null);假如数据量很大约1000万条,写一个你认为最高效的sql,用一个sql计算一下四种人:
fsalary>9999 and fage>35
fsalary>9999 and fage<35
fsalary<9999 and fage>35
fsalary<9999 and fage<35
(fempno varchar2(10) not null pk,
fempname varchar2(20) not null,
fage number not null,
fsalary number not null);假如数据量很大约1000万条,写一个你认为最高效的sql,用一个sql计算一下四种人:
fsalary>9999 and fage>35
fsalary>9999 and fage<35
fsalary<9999 and fage>35
fsalary<9999 and fage<35
SELECT COUNT(CASE
WHEN fsalary > 9999 and fage > 35 THEN
1
ELSE
NULL
END) A,
COUNT(CASE
WHEN fsalary > 9999 and fage < 35 THEN
1
ELSE
NULL
END) A,
COUNT(CASE
WHEN fsalary < 9999 and fage > 35 THEN
1
ELSE
NULL
END) A,
COUNT(CASE
WHEN fsalary < 9999 and fage < 35 THEN
1
ELSE
NULL
END) A
FROM empinfo
你直接select count(*) from empinfo;不就完事了。
group by fempno,fempname;
group by fempno,fempname;
SELECT CASE
WHEN fsalary > 9999 and fage > 35 THEN 'fsalary > 9999 and fage > 35'
WHEN fsalary > 9999 and fage < 35 THEN 'fsalary > 9999 and fage < 35'
WHEN fsalary < 9999 and fage > 35 THEN 'fsalary < 9999 and fage > 35'
WHEN fsalary < 9999 and fage < 35 THEN 'fsalary < 9999 and fage < 35'
ELSE 'other' END,COUNT(*)
FROM empinfo
GROUP BY CASE
WHEN fsalary > 9999 and fage > 35 THEN 'fsalary > 9999 and fage > 35'
WHEN fsalary > 9999 and fage < 35 THEN 'fsalary > 9999 and fage < 35'
WHEN fsalary < 9999 and fage > 35 THEN 'fsalary < 9999 and fage > 35'
WHEN fsalary < 9999 and fage < 35 THEN 'fsalary < 9999 and fage < 35'
ELSE 'other' END