在网上看到这样一个题目，有人会没？
有员工表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
每种员工的数量；
我只能想到下面这种方法，别的方法我想不出来。（MS sql中操作的）
create table empinfo(
Fempno nvarchar(10) not null primary key,
Fempname nvarchar(20) not null,
Fage int not null,
Fsalary int not null
)
select count(t1.Fempno) , count(t2.Fempno),count(t3.Fempno),count(t4.Fempno)
from (select * from empinfo where Fsalary>9999 and fage>35) t1,
(select * from empinfo where Fsalary>9999 and fage<35) t2,
(select * from empinfo where Fsalary<9999 and fage>35) t3,
(select * from empinfo where Fsalary<9999 and fage<35) t4;
------解决方案--------------------
- SQL code
select sum(case when fsalary>9999 and fage > 35 then 1 else 0 end), sum(case when fsalary>9999 and fage < 35 then 1 else 0 end), sum(case when fsalary<9999 and fage > 35 then 1 else 0 end), sum(case when fsalary<9999 and fage < 35 then 1 else 0 end)from empinfo
------解决方案--------------------
这条语句和你的执行计划效率一样 declare @total int
declare @fs_less_9999 int
declare @fa_less_35 int
declare @ff_less_9999_35 int
select @total =(select COUNT(1) as total from empinfo),
@fs_less_9999=( select COUNT(1) as fs_less_9999 from empinfo where Fsalary <9999),
@fa_less_35 =(select COUNT(1) as fa_less_35 from empinfo where Fage <35),
@ff_less_9999_35 =( select COUNT(1) as ff_less_9999_35 from empinfo where Fsalary <9999 and Fage <35)
print('fasalary<9999 and fage<35 count is:'+cast (@ff_less_9999_35 as varchar(10))
+' fasalary>9999 and fage<35 count is:'+cast (@[email protected]_less_9999_35 as varchar(10))
+' fasalary<9999 and fage>35 count is:'+cast (@[email protected]_less_9999_35 as varchar(10))
+' fasalary>9999 and fage>35 count is:'+cast (@[email protected][email protected][email protected]_less_9999_35 as varchar(10))
)
------解决方案--------------------
这条用游标写的……执行计划的评估也是一样，当然是在无数据的情况下，如果有1000万的数据，可能计划会不一样 --n1 n2 n3 n4 分别表示fasalary<9999 and fage<35、fasalary>9999 and fage<35、fasalary>9999 and fage>35、fasalary<9999 and fage>35的员工数
declare @n1 int=0,@n2 int=0,@n3 int=0,@n4 int=0,@fs int=0,@fa int=0
declare cs cursor for select Fsalary,Fage from empinfo option (table hint(empinfo) )
open cs;
fetch next from cs into @fs,@fa
while(@@FETCH_STATUS =0)
begin
if(@fs<9999)
begin
if(@fa <35)
set @[email protected]+1;
else
set @[email protected]+1;
end
else
begin
if(@fa<35)
set @[email protected]+1;
else
set @[email protected]+1;
end
fetch next from cs into @fs,@fa
end
close cs;
deallocate cs;
print('fasalary<9999 and fage<35 count is:'+cast (@n1 as varchar(10))
+' fasalary>9999 and fage<35 count is:'+cast (@n2 as varchar(10))
+' fasalary>9999 and fage>35 count is:'+cast (@n3 as varchar(10))
+' fasalary<9999 and fage>35 count is:'+cast (@n4 as varchar(10))
)
------解决方案--------------------
这条使用临时表做的……执行计划和你的相比为38%，主要用在一次全表扫描和计算中（这是我最想看到的结果），但是临时表可能会耗费大量的空间
declare @n1 int=0,@n2 int=0,@n3 int=0,@n4 int=0,@fs int=0,@fa int=0,@i int=1,@total int=0;
--先执行一下不然评估计划出不来select row_number() over(order by Fempno) as rid ,Fsalary,Fage into #temp from empinfo