SELECT ST.*,SC.S_SCORE FROM STUDENT ST JOIN SCORE SC ON ST.S_ID = SC.S_ID AND SC.C_ID=01 LEFT JOIN SCORE CS ON ST.S_ID = CS.S_ID AND CS.C_ID=02 OR cs.C_ID = NULL WHERE SC.S_SCORE>CS.S_SCORE
SELECT ST.*,SC.S_SCORE FROM STUDENT ST JOIN SCORE SC ON ST.S_ID = SC.S_ID AND SC.C_ID=01 left JOIN SCORE CS ON ST.S_ID = CS.S_ID AND CS.C_ID=02 OR CS.C_ID=NULL WHERE SC.S_SCORE<CS.S_SCORE
SELECT a.S_ID,a.S_NAME,AVG(b.S_SCORE) FROM STUDENT a JOIN SCORE b ON a.S_ID=b.S_ID GROUP BY a.S_ID,a.S_NAME HAVING AVG(b.S_SCORE)>=60 //有个平均数是没小数点的 4. SELECT a.S_ID,a.S_NAME,AVG(b.S_SCORE) FROM STUDENT a JOIN SCORE b ON a.S_ID=b.S_ID GROUP BY a.S_ID,a.S_NAME HAVING AVG(b.S_SCORE)<60 //有个平均数是没小数点的+没成绩的没太了解 5. SELECT a.S_ID,a.S_NAME,COUNT(b.C_ID),SUM(b.S_SCORE) FROM STUDENT a JOIN SCORE b ON a.S_ID=b.S_ID GROUP BY a.S_ID,a.S_NAME ORDER BY a.s_id DESC
SELECT COUNT(T_NAME) FROM TEACHER WHERE "T_NAME" like '李%' 条件查询 7. SELECT a.*,d.t_name FROM STUDENT a JOIN SCORE b ON a.S_ID=b.S_ID JOIN COURSE c ON b.C_ID=c.C_ID JOIN TEACHER d ON c.T_ID=d.T_ID WHERE T_NAME='张三'
SELECT a.,d.t_name FROM STUDENT a JOIN SCORE b ON a.S_ID=b.S_ID JOIN COURSE c ON b.C_ID=c.C_ID JOIN TEACHER d ON c.T_ID=d.T_ID WHERE T_NAME!='张三' 9. SELECT a. FROM STUDENT a JOIN SCORE b ON a.S_ID=b.S_ID AND B.C_ID=01 JOIN SCORE C ON a.S_ID=C.S_ID AND C.C_ID=02
SELECT a.* FROM STUDENT a JOIN SCORE b ON a.S_ID=b.S_ID
-- SELECT * -- FROM student stu -- WHERE stu.sno IN ( -- SELECT sno -- FROM sc -- WHERE cno = 1 -- ) AND stu.sno NOT IN ( -- SELECT sno -- FROM sc -- WHERE cno = 2 -- ); 11. SELECT a.* FROM student a WHERE a.S_ID NOT IN ( SELECT b.S_ID FROM SCORE b GROUP BY b.S_ID HAVING COUNT(C_ID) = ( SELECT COUNT(*) FROM course )
SELECT DISTINCT a.* FROM student a JOIN SCORE b ON a.S_ID=b.S_ID AND STUDENT.S_ID <> 1 WHERE b.C_ID IN ( SELECT C_ID FROM SCORE WHERE S_ID= 1 );
SELECT a.* FROM student a JOIN score b ON a.S_ID=b.S_ID WHERE b.C_ID IN ( SELECT C_ID FROM score WHERE S_ID = 1 ) AND a.S_ID <> 1 GROUP BY a.S_ID HAVING COUNT(b.S_ID) = ( SELECT COUNT(*) FROM score WHERE S_ID = 1 );
SELECT S_ID FROM score WHERE C_ID IN ( SELECT C_ID FROM course WHERE T_ID IN ( SELECT T_ID FROM teacher WHERE T_NAME='张三' ) ); 15. SELECT a.S_ID, a.S_Name, AVG(b.S_SCORE) FROM student a JOIN SCORE b ON a.S_ID=b.S_ID WHERE b.S_SCORE < 60 GROUP BY a.S_ID,a.S_Name HAVING COUNT(b.C_ID) >= 2; 16. SELECT a.* FROM student a JOIN SCORE b ON a.S_ID=b.S_ID WHERE b.C_ID = 1 AND b.S_SCORE < 60 ORDER BY b.S_SCORE DESC;
SELECT a.S_ID, a.S_Name,b.C_ID,b.S_SCORE, t.avgScore FROM student a LEFT JOIN SCORE b ON a.S_ID=b.S_ID LEFT JOIN ( SELECT a.S_ID, AVG(b.S_SCORE) AS avgScore FROM student a LEFT JOIN SCORE b ON a.S_ID=b.S_ID GROUP BY a.S_ID ) t ON s.sno = t.sno ORDER BY t.avgScore DESC; //有问题
SELECT c.C_ID, c.C_NAME, COUNT(b.cno) AS "选修人数", MAX(b.S_SCORE) AS "最高分", MIN(b.S_SCORE) AS "最低分", AVG(b.S_SCORE) AS "平均分", SUM(CASE WHEN b.S_SCORE >= 60 THEN 1 ELSE 0 END)/COUNT(1) AS "及格率", SUM(CASE WHEN b.S_SCORE BETWEEN 70 AND 80 THEN 1 ELSE 0 END)/COUNT(1) AS "中等率", SUM(CASE WHEN b.S_SCORE BETWEEN 80 AND 90 THEN 1 ELSE 0 END)/COUNT(1) AS "优良率", SUM(CASE WHEN b.S_SCORE >= 90 THEN 1 ELSE 0 END)/COUNT(1) AS "优秀率" FROM course c JOIN SCORE b ON c.C_ID = b.C_ID GROUP BY c.C_ID, c.C_NAME ORDER BY COUNT(b.C_ID) DESC, c.C_ID;
SELECT b.*, (SELECT COUNT(1) FROM SCORE WHERE C_ID = b.C_ID AND S_SCORE > b.S_SCORE) + 1 AS px FROM SCORE b ORDER BY b.S_SCORE;
SELECT t.*, @rank:=@rank + 1 AS rank FROM ( SELECT s.sno, SUM(score) FROM sc s GROUP BY s.sno ORDER BY SUM(score) DESC ) t, (SELECT @rank:=0) r;
SELECT t.T_ID, t.t_name, c.c_name, AVG(b.S_SCORE) FROM SCORE b, course c, teacher t WHERE b.C_ID = c.C_ID AND c.T_ID = t.T_ID GROUP BY b.C_ID ORDER BY AVG(b.S_SCORE) DESC; 22. SELECT a., t.C_ID, t.S_SCORE FROM student a JOIN ( SELECT * FROM SCORE b WHERE b.C_ID = 1 ORDER BY b.S_SCORE DESC LIMIT 1,2 ) t ON a.S_ID = t.S_ID UNION SELECT a., t.C_ID, t.S_SCORE FROM student a JOIN ( SELECT * FROM SCORE b WHERE b.C_ID = 2 ORDER BY b.S_SCORE DESC LIMIT 1,2 ) t ON a.S_ID = t.S_ID UNION SELECT a.*, t.C_ID, t.S_SCORE FROM student a JOIN ( SELECT * FROM SCORE b WHERE b.C_ID = 3 ORDER BY b.S_SCORE DESC LIMIT 1,2 ) t ON a.S_ID = t.S_ID
SELECT c.C_ID, c.C_NAME, SUM(CASE WHEN b.S_SCORE BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS '[100-85]', SUM(CASE WHEN b.S_SCORE BETWEEN 85 AND 100 THEN 1 ELSE 0 END)/COUNT(b.S_ID) AS '[100-85]百分比', SUM(CASE WHEN b.S_SCORE BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS '[85-70]', SUM(CASE WHEN b.S_SCORE BETWEEN 70 AND 85 THEN 1 ELSE 0 END)/COUNT(b.S_ID) AS '[85-70]百分比', SUM(CASE WHEN b.S_SCORE BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS '[70-60]', SUM(CASE WHEN b.S_SCORE BETWEEN 60 AND 70 THEN 1 ELSE 0 END)/COUNT(b.S_ID) AS '[70-60]百分比', SUM(CASE WHEN b.S_SCORE BETWEEN 0 AND 60 THEN 1 ELSE 0 END) AS '[60-0]', SUM(CASE WHEN b.S_SCORE BETWEEN 0 AND 60 THEN 1 ELSE 0 END)/COUNT(b.S_ID) AS '[60-0]百分比' FROM COURSE c JOIN SCORE b ON c.C_ID = b.C_ID GROUP BY b.C_ID, c.C_NAME;
SELECT t.*, @rownum:=@rownum + 1 AS rowNum FROM ( SELECT b.S_ID, AVG(b.S_SCORE) AS avgScore FROM SCORE b GROUP BY b.S_ID ORDER BY avgScore DESC ) t, (SELECT @rownum:=0) r;
SELECT b.S_ID, b.C_ID, b.S_SCORE FROM SCORE b LEFT JOIN SCORE b2 ON b.C_ID = b2.C_ID AND b.S_SCORE < b2.S_SCORE GROUP BY b.S_ID, b.C_ID, b.S_SCORE HAVING COUNT(b2.S_SCORE) < 3 ORDER BY b.C_ID, b.S_SCORE DESC;
SELECT c.C_ID,c.c_name, COUNT(*) AS "被选修次数" FROM course c LEFT JOIN SCORE b ON c.C_ID = b.C_ID GROUP BY c.C_ID,c.c_name;
SELECT a.S_ID, a.s_name FROM student a WHERE a.S_ID IN ( SELECT S_ID FROM SCORE b GROUP BY b.S_ID HAVING COUNT(b.C_ID) = 2 );
SELECT S_SEX, COUNT(*) AS "性别" FROM student a GROUP BY S_SEX;
SELECT * FROM student a WHERE a.s_name LIKE '%风%'; //oracle 不能用双引号
SELECT a., b.tmrs FROM student a LEFT JOIN ( SELECT sname, gender, COUNT() AS tmrs FROM student GROUP BY sname, gender ) b ON a.s_name = b.s_name AND a.s_sex = b.S_SEX WHERE b.tmrs > 1;
SELECT * FROM student WHERE S_BIRTH=TO_DATE('1990', 'YYYY')
SELECT C_ID, AVG(S_SCORE) AS avgScore FROM SCORE GROUP BY C_ID ORDER BY avgScore DESC, C_ID;
SELECT a.s_id, a.s_name,AVG(b.s_score) FROM student a JOIN SCORE b ON a.s_id = b.s_id GROUP BY a.s_id,a.s_name HAVING AVG(b.s_score) > 85;
SELECT a.s_name, b.s_score FROM student a JOIN SCORE b ON a.s_id = b.s_id WHERE b.C_ID = ( SELECT C_ID FROM course WHERE c_name = '数学' ) AND b.S_SCORE < 60;
SELECT a.*, c.c_id, c.c_name, b.S_SCORE FROM student a LEFT JOIN SCORE b ON a.s_id = b.s_id LEFT JOIN course c ON b.c_id = c.c_id ORDER BY a.s_id;
SELECT a.s_name, c.c_name, b.s_score FROM student a JOIN SCORE b ON a.s_id = b.s_id JOIN course c ON b.c_id = c.c_id WHERE b.s_score > 70 ORDER BY a.s_id, c.c_name;
SELECT DISTINCT b.s_id,c.c_name,b.S_SCORE FROM course c JOIN SCORE b ON c.c_id = b.c_id WHERE b.s_score < 60;
SELECT a.s_id, a.s_name FROM student a JOIN SCORE b ON a.s_id = b.s_id WHERE b.c_id = 1 AND b.s_score >= 80;
SELECT c_id, COUNT(*) FROM SCORE GROUP BY c_id;
WITH z AS( SELECT a.*, b.s_score FROM student a JOIN SCORE b ON a.s_id = b.s_id WHERE b.c_id = ( SELECT c_id FROM course c WHERE c.t_id = ( SELECT t_id FROM teacher t WHERE t.t_name = '张三' ) ) ORDER BY b.s_score DESC ）SELECT * FROM z WHERE ROWNUM='1'
SELECT DISTINCT a.* FROM SCORE a JOIN SCORE b ON a.s_id = b.s_id WHERE a.s_score =b.s_score AND a.c_id = b.c_id ORDER BY a.s_id DESC
SELECT a.* FROM SCORE a WHERE ( SELECT COUNT(1) FROM SCORE b WHERE a.c_id = b.c_id AND a.s_score <= b.s_score ) <= 2 ORDER BY a.c_id;
SELECT a.c_id,COUNT() FROM SCORE a GROUP BY a.c_id HAVING COUNT() > 5 ORDER BY COUNT(*) DESC, a.c_id;
SELECT s_id FROM SCORE GROUP BY s_id HAVING COUNT(c_id) >= 2;
SELECT * FROM STUDENT WHERE s_id=( SELECT a.s_id FROM student a LEFT JOIN SCORE b ON a.s_id = b.s_id GROUP BY a.s_id HAVING COUNT(c_id) = ( SELECT DISTINCT COUNT(*) FROM course ) )
SELECT a.* FROM student a WHERE WEEK(s_birth) = WEEK(NOW(), 1) + 1; 49. SELECT a.* FROM student a WHERE MONTH(s_birth) = MONTH(NOW()); 50. SELECT * FROM STUDENT WHERE EXTRACT(MONTH FROM S_BIRTH) = EXTRACT(MONTH FROM ADD_MONTHS(to_date(S_BIRTH, 'yyyy-mm-dd'), 1))
