select 学生名, count(1) as 选课总数, ltrim(cast(sum(case when 成绩 < 60 then 1 else 0 end)*100.0/count(1) as dec(9,2))) + '%' from tb group by 学生名
select 学生名, count(1) as 选课总数, sum(case when 学生选课成绩<60 then 1 else 0 end)*100.0/count(1) 分比 from ta group by 学生名
select 学生名, count(1) as 选课总数, sum(case when 成绩<60 then 1 else 0 end)*100.0/count(1) as 不及格比例 from tb group by 学生名
CREATE TABLE XUE ( MING VARCHAR(10), KE VARCHAR(10), FEN NUMERIC(18,2) )INSERT INTO XUE VALUES ('张三','语文',100) INSERT INTO XUE VALUES ('张三','数学',30) INSERT INTO XUE VALUES ('张三','化学',35) INSERT INTO XUE VALUES ('张三','物理',57) INSERT INTO XUE VALUES ('李四','语文',34) INSERT INTO XUE VALUES ('李四','数学',80) SELECT MING,COUNT(*),SUM( CASE WHEN FEN > 60 THEN 0 ELSE 1 END )/CAST(COUNT(*) AS NUMERIC(18,2)) FROM XUE GROUP BY MING
count(1) as 选课总数,
ltrim(cast(sum(case when 成绩 < 60 then 1 else 0 end)*100.0/count(1) as dec(9,2))) + '%'
from tb
group by 学生名
select
学生名,
count(1) as 选课总数,
sum(case when 学生选课成绩<60 then 1 else 0 end)*100.0/count(1) 分比
from ta
group by 学生名
学生名,
count(1) as 选课总数,
sum(case when 成绩<60 then 1 else 0 end)*100.0/count(1) as 不及格比例
from tb
group by 学生名
CREATE TABLE XUE
(
MING VARCHAR(10),
KE VARCHAR(10),
FEN NUMERIC(18,2)
)INSERT INTO XUE VALUES ('张三','语文',100)
INSERT INTO XUE VALUES ('张三','数学',30)
INSERT INTO XUE VALUES ('张三','化学',35)
INSERT INTO XUE VALUES ('张三','物理',57)
INSERT INTO XUE VALUES ('李四','语文',34)
INSERT INTO XUE VALUES ('李四','数学',80)
SELECT MING,COUNT(*),SUM( CASE WHEN FEN > 60 THEN 0 ELSE 1 END )/CAST(COUNT(*) AS NUMERIC(18,2)) FROM XUE GROUP BY MING