求一高效SQl

[grayhoundd]

获取一张表中符合条件总记录数，和，字段status值为"normal","late"的数据总条数、百分比，要求在一行中显示

如
lbob 1 NULL
lbob 0 NULL
Xu 0 Normal
Xu 1 NULL
lbob 1 NULL
gyi 1 NULL
lbob 0 NULL
Tim 1 Normal
Jock 1 Normal
Nick 0 Normal
Tim 1 Normal
Jock 1 Normal
Nick 0 Normal
lbob 0 NULL
lbob 1 NULL
lbob 0 NULL
lbob 0 NULL
lbob 0 NULL
lbob 1 NULL
lbob 1 NULL
lbob 0 NULL
lbob 0 NULL
lbob 1 Normal
Xu 1 Absent
lbob 0 NULL
lbob 1 Late
Xu 0 Absent
lbob 1 NULL
gyi 0 NULL

应返回：9（Normal、Late条数）、25（总条数）、32%

[水族杰纶]

select sum(case when status='Normal' then 1 else 0 end)Normal条数,
       sum(case when status='Late' then 1 else 0 end)Late条数,
       count(*)总条数
from tb


       

[水族杰纶]

总条数不对吧

[百年树人]

select 
  sum(case when status in('Normal','Late') then 1 else 0 end) as [Normal、Late条数],
  count(1)总条数,
  ltrim(sum(case when status in('Normal','Late') then 1 else 0 end)*100/count(1))+'%'
from 
  tb
where xxx

[龙马]

[Quote=引用 3 楼 josy 的回复:]

SQL code
select
sum(case when status in('Normal','Late') then 1 else 0 end) as [Normal、Late条数],
count(1)总条数,
ltrim(sum(case when status in('Normal','Late') then 1 else 0 end)*100/count(1))+'%……
[/Quote]

[dawugui]

create table tb(name varchar(10), type int, status varchar(10))
insert into tb values('lbob' ,1 ,NULL)
insert into tb values('lbob' ,0 ,NULL)
insert into tb values('Xu  ' ,0 ,'Normal')
insert into tb values('Xu  ' ,1 ,NULL)
insert into tb values('lbob' ,1 ,NULL)
insert into tb values('gyi ' ,1 ,NULL)
insert into tb values('lbob' ,0 ,NULL)
insert into tb values('Tim ' ,1 ,'Normal')
insert into tb values('Jock' ,1 ,'Normal')
insert into tb values('Nick' ,0 ,'Normal')
insert into tb values('Tim ' ,1 ,'Normal')
insert into tb values('Jock' ,1 ,'Normal')
insert into tb values('Nick' ,0 ,'Normal')
insert into tb values('lbob' ,0 ,NULL)
insert into tb values('lbob' ,1 ,NULL)
insert into tb values('lbob' ,0 ,NULL)
insert into tb values('lbob' ,0 ,NULL)
insert into tb values('lbob' ,0 ,NULL)
insert into tb values('lbob' ,1 ,NULL)
insert into tb values('lbob' ,1 ,NULL)
insert into tb values('lbob' ,0 ,NULL)
insert into tb values('lbob' ,0 ,NULL)
insert into tb values('lbob' ,1 ,'Normal')
insert into tb values('Xu  ' ,1 ,'Absent')
insert into tb values('lbob' ,0 ,NULL)
insert into tb values('lbob' ,1 ,'Late')
insert into tb values('Xu  ' ,0 ,'Absent')
insert into tb values('lbob' ,1 ,NULL)
insert into tb values('gyi ' ,0 ,NULL)
go

select 
[Normal、Late条数] = (select count(1) from tb where status = 'Normal' or status = 'Late'),
[总条数] = (select count(1) from tb),
[百分比] = cast((select count(1) from tb where status = 'Normal' or status = 'Late')*100.0/(select count(1) from tb) as decimal(18,2))

drop table tb

/*
Normal、Late条数 总条数         百分比                  
------------- ----------- -------------------- 
9             29          31.03

（所影响的行数为 1 行）
*/