不知道这个方法行不行:SUM(CASE WHEN 极格=是 THEN 1 ELSE 0)SELECT 班级, SUM( 总分 ), COUNT( 总分 ), COUNT1=SUM(CASE WHEN 极格=是 THEN 1 ELSE 0 END) FROM "studentscore.DB" GROUP BY 班级
试试: SQL SERVER; SELECT 班级, SUM( 总分 ), COUNT( 总分 ),sun(case 极格 when 是 then 1 else 0) FROM "studentscore.DB" WHERE 极格=是 GROUP BY 班级 Oracle: SELECT 班级, SUM( 总分 ), COUNT( 总分 ),sun(decode 极格, 是 , 1 , 0) FROM "studentscore.DB" WHERE 极格=是 GROUP BY 班级
作一个视图 create view v as select ID,班级,总分,1 as f FROM "studentscore.DB" WHERE 极格=是 union select TD,班级,总分,0 as f FROM "studentscore.DB" WHERE 极格!=是 然后 SELECT 班级, SUM( 总分 ), COUNT( 总分 ),sum(f) FROM v GROUP BY 班级
SQL SERVER;
SELECT 班级, SUM( 总分 ), COUNT( 总分 ),sun(case 极格 when 是 then 1 else 0)
FROM "studentscore.DB" WHERE 极格=是
GROUP BY 班级
Oracle:
SELECT 班级, SUM( 总分 ), COUNT( 总分 ),sun(decode 极格, 是 , 1 , 0)
FROM "studentscore.DB" WHERE 极格=是
GROUP BY 班级
create view v
as
select ID,班级,总分,1 as f
FROM "studentscore.DB" WHERE 极格=是
union
select TD,班级,总分,0 as f
FROM "studentscore.DB" WHERE 极格!=是
然后
SELECT 班级, SUM( 总分 ), COUNT( 总分 ),sum(f)
FROM v
GROUP BY 班级