select * from score where averageScore>60 and classID=1 and (ChineseScore<60 or mathScore<60 or EnglishScore<60.....)
select studentName,ChineseScore as sc from score where averageScore>60 and classID=1 and ChineseScore<60 union all select studentName,mathScore from score where averageScore>60 and classID=1 and mathScore<60 union all... .....
是不是N列就要写N个sql语句?在jsp里处理方法就要写一堆了,有没一个语句可以实现的?
select * from (select姓名,课程,分数 from score where averageScore>60 and classID=1 unpivot (分数 for 课程 in(ChineseScore,mathScore ,.....,politicsScore)) t) where 课程<60
select * from (select姓名,课程,分数 from score where averageScore>60 and classID=1 unpivot (分数 for 课程 in(ChineseScore,mathScore ,.....,politicsScore)) t) a where 课程<60
列名:
studentNumber
studentName
classID
totalScore
ChineseScore
mathScore
EnglishScore
physicalScore
chemistryScore
biologicalScore
geographyScore
historyScore
politicsScore
averageScore 我想查询averageScore过60,但是其它单科成绩有低于60的学生详细成绩。并且classID=1。
from score where averageScore>60 and classID=1
and (ChineseScore<60 or mathScore<60 or EnglishScore<60.....)
union all
select studentName,mathScore from score where averageScore>60 and classID=1 and mathScore<60
union all...
.....
是不是N列就要写N个sql语句?在jsp里处理方法就要写一堆了,有没一个语句可以实现的?
(select姓名,课程,分数 from score where averageScore>60 and classID=1
unpivot (分数 for 课程 in(ChineseScore,mathScore ,.....,politicsScore)) t)
where 课程<60
(select姓名,课程,分数 from score where averageScore>60 and classID=1
unpivot (分数 for 课程 in(ChineseScore,mathScore ,.....,politicsScore)) t) a
where 课程<60