表A
name class flag male number address phone...
liming 1 Y
wangqiang 1 N
zhangfeng 2 Y
xiehui 2 N
wangqian 2 Y
...
求一个SQL语句,求出每个班级的及格率。(flag=Y为及格) 输出格式如下:
name class flag rate male number address phone...
liming 1 Y 50%
wangqiang 1 N 50%
zhangfeng 2 Y 75%
xiehui 2 N 75%
wangqian 2 Y 75%
pengjun 2 Y 75%
name class flag male number address phone...
liming 1 Y
wangqiang 1 N
zhangfeng 2 Y
xiehui 2 N
wangqian 2 Y
...
求一个SQL语句,求出每个班级的及格率。(flag=Y为及格) 输出格式如下:
name class flag rate male number address phone...
liming 1 Y 50%
wangqiang 1 N 50%
zhangfeng 2 Y 75%
xiehui 2 N 75%
wangqian 2 Y 75%
pengjun 2 Y 75%
cast((select count(1) from a where class = t.class and flag='Y') * 100.0 / (select count(1) from a where class = t.class ) as decimal(18,2))
from a t
insert into a values('liming' , 1 ,'Y')
insert into a values('wangqiang', 1 ,'N')
insert into a values('zhangfeng', 2 ,'Y')
insert into a values('xiehui' , 2 ,'N')
insert into a values('wangqian' , 2 ,'Y ')
goselect t.* ,
cast((select count(1) from a where class = t.class and flag='Y') * 100.0 / (select count(1) from a where class = t.class ) as decimal(18,2))
from a t
/*name class flag
-------------------- ----------- ---------- --------------------
liming 1 Y 50.00
wangqiang 1 N 50.00
zhangfeng 2 Y 66.67
xiehui 2 N 66.67
wangqian 2 Y 66.67(所影响的行数为 5 行)
*/
select t.* ,
cast(cast((select count(1) from a where class = t.class and flag='Y') * 100.0 / (select count(1) from a where class = t.class ) as decimal(18,2)) as varchar) + '%'
from a t
/*
name class flag
-------------------- ----------- ---------- -------------------------------
liming 1 Y 50.00%
wangqiang 1 N 50.00%
zhangfeng 2 Y 66.67%
xiehui 2 N 66.67%
wangqian 2 Y 66.67%(所影响的行数为 5 行)
*/
drop table a