SQL查询非最大值的所有记录
答案:2 悬赏:50 手机版
解决时间 2021-03-17 14:35
- 提问者网友：人生佛魔见
- 2021-03-16 20:28
SQL查询非最大值的所有记录
最佳答案
- 五星知识达人网友：往事埋风中
- 2021-03-16 21:23
SELECt *
FROM score s1
WHERe sno IN (
SELECt sno
FROM score
GROUP BY sno
HAVINg count(cno) > 1)
AND DEGREE <> (
SELECt max(degree)
FROM score s2
WHERe s1.cno = s2.cno
GROUP BY cno
)来自：求助得到的回答
FROM score s1
WHERe sno IN (
SELECt sno
FROM score
GROUP BY sno
HAVINg count(cno) > 1)
AND DEGREE <> (
SELECt max(degree)
FROM score s2
WHERe s1.cno = s2.cno
GROUP BY cno
)来自：求助得到的回答
全部回答
- 1楼网友：七十二街
- 2021-03-16 22:58
-- 试试看
select t1.sno,t1.cno,t1.score from score as t1 inner join (select max(degree) as degree,cno from score group by cno) as t2
on t1.cno = t2.cno and t1.degree < t2.degree
group by t1.sno,t1.cno,t1.score having count(*) > 1
select t1.sno,t1.cno,t1.score from score as t1 inner join (select max(degree) as degree,cno from score group by cno) as t2
on t1.cno = t2.cno and t1.degree < t2.degree
group by t1.sno,t1.cno,t1.score having count(*) > 1
我要举报
如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！
点此我要举报以上问答信息
大家都在看
推荐资讯