--5项分都都等100分:select count(1) [5项分都都等100分的个数] from tb where SCORE1=100 and SCORE2=100 and SCORE3=100 and SCORE4=100 and SCORE5 =100 --5项分总和等100分:select count(1) [5项分总和等100分的个数] from tb where SCORE1+SCORE2+SCORE3+SCORE4+SCORE5 =100
调试欢乐多
num=sum(case when score1=100 then 1 else 0 end+
case when score2=100 then 1 else 0 end+
case when score3=100 then 1 else 0 end+
case when score4=100 then 1 else 0 end+
case when score5=100 then 1 else 0 end)
from tb
group by id
Select ID, SCORE1, SCORE2, SCORE3, SCORE4, SCORE5 from t
where SCORE1+SCORE2+SCORE3+SCORE4 +SCORE5=100
select id,score1 as score union allselect id,score2 as score union all
select id,score3 as score union all
select id,score4 as score union all
select id,score5 as score
)a
where score=100
select sum(num) num from
(select count(1) num from tb where SCORE1=100
union all
select count(1) from tb where SCORE2=100
union all
select count(1) from tb where SCORE3=100
union all
select count(1) from tb where SCORE4=100
union all
select count(1) from tb where SCORE5=100) a
select ID,
sum((case when SCORE1=100 then 1 else 0 end)+(case when SCORE1=100 then 1 else 0 end)+
(case when SCORE1=100 then 1 else 0 end)+(case when SCORE1=100 then 1 else 0 end)+
(case when SCORE1=100 then 1 else 0 end))
from TB
group by ID
sum(case when Score3=100 then 1 else 0 end)+sum(case when Score4=100 then 1 else 0 end)
+sum(case when Score5=100 then 1 else 0 end)+sum(case when Score6=100 then 1 else 0 end)
ID SCORE1 SCORE2 SCORE3 SCORE4 SCORE5
1001 100 98 100 87 100
我的意思就是按学号查询的同学各科成绩是100的个数 比如1001同学100的个数是3
Select count(*)from t
where SCORE1=100 or SCORE2=100 or SCORE3=100 or SCORE4=100 or SCORE5=100理解樓主的意思應該是這五個成績任意一個為100的記錄數.
case when score2=100 then 1 else 0 end+
case when score3=100 then 1 else 0 end+
case when score4=100 then 1 else 0 end+
case when score5=100 then 1 else 0 end as num
from tb
select
sum(case when Score1=100 then 1 else 0 end)+sum(case when Score2=100 then 1 else 0 end)
sum(case when Score3=100 then 1 else 0 end)+sum(case when Score4=100 then 1 else 0 end)
+sum(case when Score5=100 then 1 else 0 end)+sum(case when Score6=100 then 1 else 0 end)
from TB
select
sum(case when Score1=100 then 1 else 0 end)+sum(case when Score2=100 then 1 else 0 end)
sum(case when Score3=100 then 1 else 0 end)+sum(case when Score4=100 then 1 else 0 end)
+sum(case when Score5=100 then 1 else 0 end)+sum(case when Score6=100 then 1 else 0 end)
from TB
SELECT COUNT(1) AS TOTAL
FROM TABLE_NAME
WHERE SCORE1 = 100 AND SCORE2 = 100 AND SCORE3 = 100 AND SCORE4 = 100 AND SCORE5 = 100