----1、每门课程成绩都在90分以上的人的学号。 select sno from SC group by sno having min(grade) > 90;
select sno, MAX(grade) OVER (PARTITION BY grade order by cno) AS grade_max from SC;
--1题 SELECT sno FROM s WHERE 90 > ANY (SELECT sc.grade FROM sc WHERE sc.sno = s.sno); --2题 SELECT DISTINCT sno FROM sc WHERE (sc.cno, sc.grade) IN (SELECT cno, MAX(grade) grade FROM sc GROUP BY cno); --3题 SELECT sno FROM (SELECT rownum rn, sno, allgrade FROM (SELECT sc.sno, SUM(sc.grade) allgrade FROM sc GROUP BY sc.sno ORDER BY allgrade DESC)) WHERE rn = 1;
----1、每门课程成绩都在90分以上的人的学号。 select sno from SC group by sno having min(grade) > 90; ----2、每门课程成绩最高的人的学号(课程号,成绩)。 select * from sc where cno||grade in (select cno||max(grade) from sc group by cno); ----3、所有课程成绩最高的人的学号。 select sno from SC group by sno having sum(grade) >= all(select sum(grade) from SC group by sno);
不好意思,上面回复第一题有误,应该这样 --1题 SELECT sno FROM s WHERE 90 < all (SELECT sc.grade FROM sc WHERE sc.sno = s.sno); --2题 SELECT DISTINCT sno FROM sc WHERE (sc.cno, sc.grade) IN (SELECT cno, MAX(grade) grade FROM sc GROUP BY cno); --3题 SELECT sno FROM (SELECT rownum rn, sno, allgrade FROM (SELECT sc.sno, SUM(sc.grade) allgrade FROM sc GROUP BY sc.sno ORDER BY allgrade DESC)) WHERE rn = 1;
select sno, MAX(grade) OVER (PARTITION BY cno order by grade) AS grade_max from SC;
2.select max(sno)keep(dense_rank last order by grade)sno, max(grade)grade from sc group by cno; 3.select max(sno)keep(dense_rank last order by sum(grade))sno, max(sum(grade))grade from sc group by sno;
select sno from SC group by sno having min(grade) > 90;
MAX(grade) OVER (PARTITION BY grade order by cno) AS grade_max from SC;
SELECT sno FROM s WHERE 90 > ANY (SELECT sc.grade FROM sc WHERE sc.sno = s.sno);
--2题
SELECT DISTINCT sno
FROM sc
WHERE (sc.cno, sc.grade) IN (SELECT cno, MAX(grade) grade FROM sc GROUP BY cno);
--3题
SELECT sno FROM (SELECT rownum rn, sno, allgrade
FROM (SELECT sc.sno, SUM(sc.grade) allgrade
FROM sc
GROUP BY sc.sno
ORDER BY allgrade DESC))
WHERE rn = 1;
----1、每门课程成绩都在90分以上的人的学号。
select sno from SC group by sno having min(grade) > 90;
----2、每门课程成绩最高的人的学号(课程号,成绩)。
select * from sc where cno||grade in (select cno||max(grade) from sc group by cno);
----3、所有课程成绩最高的人的学号。
select sno from SC group by sno having sum(grade) >= all(select sum(grade) from SC group by sno);
--1题
SELECT sno FROM s WHERE 90 < all (SELECT sc.grade FROM sc WHERE sc.sno = s.sno);
--2题
SELECT DISTINCT sno
FROM sc
WHERE (sc.cno, sc.grade) IN (SELECT cno, MAX(grade) grade FROM sc GROUP BY cno);
--3题
SELECT sno FROM (SELECT rownum rn, sno, allgrade
FROM (SELECT sc.sno, SUM(sc.grade) allgrade
FROM sc
GROUP BY sc.sno
ORDER BY allgrade DESC))
WHERE rn = 1;
MAX(grade) OVER (PARTITION BY cno order by grade) AS grade_max from SC;
2.select max(sno)keep(dense_rank last order by grade)sno,
max(grade)grade
from sc
group by cno;
3.select max(sno)keep(dense_rank last order by sum(grade))sno,
max(sum(grade))grade
from sc
group by sno;
一般的查询和统计问题都能用SQL搞定随着SQL越来越熟练,你写存储过程的机会就越来越少
请问第二问中,where后面为啥要用"||"连接呢?我用","连接就出错!!!