;with wang as( select * from exam s where not exists(select 1 from exam where category=s.category and studentName=s.studentName and score<60) )select studentname from wang group by studentname having COUNT(*)>=4
select category,min(score) as 最低分,studentName group by studentName,category having 最低分<60
select distinct studentName from exam where score < 60
-- 这个是 四科不过不让毕业的, 那个是只要一科没过就不让毕业的。 select studentName , COUNT(*) from exam where score < 60 group by studentName having COUNT(*) >=4
-- 这个是 四科不过不让毕业的, 那个是只要一科没过就不让毕业的。 select studentName , COUNT(*) from exam where score < 60 group by studentName having COUNT(*) >=4
这样科目一样的会重复算了吧
select studentName from ( select *,ROW_NUMBER() over(partition by studentName,category order by score) rn from exam ) t where rn=1 and score>=60 group by studentName having COUNT(*)>3
wang as(
select * from exam s
where not exists(select 1 from exam where category=s.category and studentName=s.studentName and score<60)
)select studentname
from wang
group by studentname
having COUNT(*)>=4
select distinct studentName from exam where score < 60
-- 这个是 四科不过不让毕业的, 那个是只要一科没过就不让毕业的。
select studentName , COUNT(*) from exam where score < 60 group by studentName having COUNT(*) >=4
-- 这个是 四科不过不让毕业的, 那个是只要一科没过就不让毕业的。
select studentName , COUNT(*) from exam where score < 60 group by studentName having COUNT(*) >=4
这样科目一样的会重复算了吧
select studentName from
(
select *,ROW_NUMBER() over(partition by studentName,category order by score) rn from exam
) t
where rn=1 and score>=60
group by studentName
having COUNT(*)>3