{"id":141,"date":"2023-06-28T14:14:55","date_gmt":"2023-06-28T06:14:55","guid":{"rendered":"https:\/\/www.hyw.life\/?p=141"},"modified":"2025-10-21T17:15:30","modified_gmt":"2025-10-21T09:15:30","slug":"mysql%e7%bb%83%e4%b9%a0%ef%bc%9a%e7%bb%8f%e5%85%b850%e9%81%93%e5%9f%ba%e7%a1%80%e9%a2%98","status":"publish","type":"post","link":"https:\/\/www.hyw.life\/?p=141","title":{"rendered":"mysql\u7ec3\u4e60\uff1a\u7ecf\u517850\u9053\u57fa\u7840\u9898"},"content":{"rendered":"\n<\/span>

\u4e00\u3001\u73af\u5883\u51c6\u5907<\/h2>\n\n\n\n

\u5efa\u8868\u51714\u5f20\u8868\uff0c\u5206\u522b\u5bf9\u5e94\u5b66\u751f\u4fe1\u606f\uff08Student\uff09\u3001\u8bfe\u7a0b\u4fe1\u606f\uff08Course\uff09\u3001\u6559\u5e08\u4fe1\u606f\uff08Teacher\uff09\u4ee5\u53ca\u6210\u7ee9\u4fe1\u606f\uff08SC\uff09<\/p>\n\n\n\n

--  \u5b66\u751f\u8868\ncreate table Student(sidvarchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));\ninsert into Student values('01' , '\u8d75\u96f7' , '1990-01-01' , '\u7537');\ninsert into Student values('02' , '\u94b1\u7535' , '1990-12-21' , '\u7537');\ninsert into Student values('03' , '\u5b59\u98ce' , '1990-05-20' , '\u7537');\ninsert into Student values('04' , '\u674e\u4e91' , '1990-08-06' , '\u7537');\ninsert into Student values('05' , '\u5468\u6885' , '1991-12-01' , '\u5973');\ninsert into Student values('06' , '\u5434\u5170' , '1992-03-01' , '\u5973');\ninsert into Student values('07' , '\u90d1\u7af9' , '1989-07-01' , '\u5973');\ninsert into Student values('08' , '\u738b\u83ca' , '1990-01-20' , '\u5973');\n-- \u8bfe\u7a0b\u8868createtable Course(cid varchar(10),cname varchar(10),tid varchar(10));\ninsert into Course values('01' , '\u8bed\u6587' , '02');\ninsert into Course values('02' , '\u6570\u5b66' , '01');\ninsert into Course values('03' , '\u82f1\u8bed' , '03');\n-- \u6559\u5e08\u8868createtable Teacher(tid varchar(10),tname varchar(10));\ninsert into Teacher values('01' , '\u5f20\u4e09');\ninsert into Teacher values('02' , '\u674e\u56db');\ninsert into Teacher values('03' , '\u738b\u4e94');\n-- \u6210\u7ee9\u8868createtable SC(sidvarchar(10),cid varchar(10),score decimal(18,1));\ninsert into SC values('01' , '01' , 80);insertinto SC values('01' , '02' , 90);\ninsert into SC values('01' , '03' , 99);insertinto SC values('02' , '01' , 70);\ninsert into SC values('02' , '02' , 60);insertinto SC values('02' , '03' , 80);\ninsert into SC values('03' , '01' , 80);insertinto SC values('03' , '02' , 80);\ninsert into SC values('03' , '03' , 80);insertinto SC values('04' , '01' , 50);\ninsert into SC values('04' , '02' , 30);insertinto SC values('04' , '03' , 20);\ninsert into SC values('05' , '01' , 76);insertinto SC values('05' , '02' , 87);\ninsert into SC values('06' , '01' , 31);insertinto SC values('06' , '03' , 34);\ninsert into SC values('07' , '02' , 89);insertinto SC values('07' , '03' , 98);<\/code><\/pre>\n\n\n\n<\/span>
50\u9053\u9898\u76ee\u7ec3\u4e60<\/h2>\n\n\n\n<\/span>
1\u3001\u67e5\u8be2”01″\u8bfe\u7a0b\u6bd4”02″\u8bfe\u7a0b\u6210\u7ee9\u9ad8\u7684\u5b66\u751f\u7684\u4fe1\u606f\u53ca\u8bfe\u7a0b\u5206\u6570\u3000<\/h3>\n\n\n\n
SELECT student.*,t3.sid FROM \n(SELECT t1.sid,t1.score FROM \n    (SELECT sid,score FROM sc WHERE cid = \"01\") as t1 \n    JOIN \n    (SELECT sid,score FROM sc WHERE cid = \"02\") as t2\n    ON \n    t1.sid = t2.sid WHERE t1.score > t2.score) as t3 \nJOIN student\nON t3.sid = student.sid;<\/code><\/pre>\n\n\n\n
\u7ed3\u679c\uff1a<\/p>\n\n\n\n
+-----+-------+---------------------+------+-----+\n| sid | sname | sage                | ssex | sid |\n+-----+-------+---------------------+------+-----+\n| 02  | \u94b1\u7535  | 1990-12-21 00:00:00 | \u7537   | 02  |\n| 04  | \u674e\u4e91  | 1990-08-06 00:00:00 | \u7537   | 04  |\n+-----+-------+---------------------+------+-----+\n2 rows in set<\/code><\/pre>\n\n\n\n
\u89e3\u6790\uff1a
\u5148\u5c06\u8bfe\u7a0b\u4e3a01\u548c02\u7684\u8bfe\u7a0b\u53ca\u5bf9\u5e94\u5206\u6570\u7b5b\u9009\u51fa\u6765\uff0c\u518djoin\uff0con\u4e3a01.sid = 02.sid\uff0c\u6761\u4ef6\u4e3a01.score >02.score\uff0c\u7ed3\u679c’\u5b58’\u4e3a\u65b0\u8868t3\uff0c\u518d\u5c06Student\u8868\u548ct3\u8868join<\/p>\n\n\n\n<\/span>
2\u3001\u67e5\u8be2\u5b66\u751f\u9009\u8bfe\u5b58\u5728” 01 “\u8bfe\u7a0b\u4f46\u53ef\u80fd\u4e0d\u5b58\u5728” 02 “\u8bfe\u7a0b\u7684\u60c5\u51b5\uff08\u4e0d\u5b58\u5728\u65f6\u663e\u793a\u4e3a null\uff09<\/h3>\n\n\n\n
SELECT * FROM \n(SELECT * FROM sc WHERE cid = \"01\") as t1 \nLEFT JOIN \n(SELECT * FROM sc WHERE cid = \"02\") as t2\nON t1.sid = t2.sid;<\/code><\/pre>\n\n\n\n
\u7ed3\u679c\uff1a<\/p>\n\n\n\n
+-----+-----+-------+------+------+-------+\n| sid | cid | score | sid  | cid  | score |\n+-----+-----+-------+------+------+-------+\n| 01  | 01  | 80.0  | 01   | 02   | 90.0  |\n| 02  | 01  | 70.0  | 02   | 02   | 60.0  |\n| 03  | 01  | 80.0  | 03   | 02   | 80.0  |\n| 04  | 01  | 50.0  | 04   | 02   | 30.0  |\n| 05  | 01  | 76.0  | 05   | 02   | 87.0  |\n| 06  | 01  | 31.0  | NULL | NULL | NULL  |\n+-----+-----+-------+------+------+-------+\n6 rows in set<\/code><\/pre>\n\n\n\n
\u89e3\u6790\uff1a
\u5373\u627e\u51fa\u5b66\u751f\u9009\u4e8601\u8bfe\u7a0b\u6ca1\u6709\u900902\u8bfe\u7a0b\u7684\u60c5\u51b5\uff0c\u7528left join\u5373\u53ef<\/p>\n\n\n\n<\/span>
3\u3001\u67e5\u8be2\u5e73\u5747\u6210\u7ee9\u5927\u4e8e\u7b49\u4e8e 60 \u5206\u7684\u540c\u5b66\u7684\u5b66\u751f\u7f16\u53f7\u548c\u5b66\u751f\u59d3\u540d\u548c\u5e73\u5747\u6210\u7ee9<\/h3>\n\n\n\n
#\u591a\u8868\u8054\u5408\u67e5\u8be2\nSELECT  sc.sid,student.sname,avg(sc.score) FROM sc ,student WHERE sc.sid = student.sid  GROUP BY  sc.sid  HAVING avg(sc.score) > 60;\n#\u591a\u8868\u8fde\u63a5\u67e5\u8be2\nSELECT  sc.sid,student.sname,avg(sc.score) FROM sc JOIN student on sc.sid = student.sid  GROUP BY  sc.sid  HAVING avg(sc.score) > 60;<\/code><\/pre>\n\n\n\n
\u7ed3\u679c\uff1a<\/p>\n\n\n\n
+-----+-------+---------------+\n| sid | sname | avg(sc.score) |\n+-----+-------+---------------+\n| 01  | \u8d75\u96f7  | 89.66667      |\n| 02  | \u94b1\u7535  | 70.00000      |\n| 03  | \u5b59\u98ce  | 80.00000      |\n| 05  | \u5468\u6885  | 81.50000      |\n| 07  | \u90d1\u7af9  | 93.50000      |\n+-----+-------+---------------+\n5 rows in set<\/code><\/pre>\n\n\n\n
\u89e3\u6790\uff1a
\u9996\u5148\u786e\u5b9a\u7684\u662f\u4e24\u5f20\u8868\uff0cstudent\u548csc\uff0c\u8fd9\u91cc\u4f7f\u7528\u591a\u8868\u8054\u5408\u67e5\u8be2\u548c\u591a\u8868\u8fde\u63a5\u67e5\u7684\u65b9\u5f0f\u90fd\u53ef\u4ee5\uff0c\u5173\u8054\u6761\u4ef6\u662fsid\uff0c\u7136\u540e\u5206\u7ec4\uff0c\u6700\u540e\u52a0\u4e00\u4e2ahaving\u51fd\u6570\uff0c\u6761\u4ef6\u662f\u5e73\u5747\u6210\u7ee9\u5927\u4e8e60\uff0c\u5373\u53ef\u67e5\u8be2\u51fa\u6765<\/p>\n\n\n\n<\/span>
4\u3001\u67e5\u8be2\u5728 SC \u8868\u5b58\u5728\u6210\u7ee9\u7684\u5b66\u751f\u4fe1\u606f<\/h3>\n\n\n\n
#\u591a\u8868\u8054\u5408\u67e5\u8be2\u65b9\u5f0f\nSELECT  t1.*,t2.score FROM student t1, sc t2 WHERE t1.sid = t2.sid  GROUP BY t1.sid;\n \n#\u591a\u8868\u8fde\u63a5\u67e5\u8be2\u65b9\u5f0f\nSELECT a.*,b.score FROM student as a \n    JOIN sc AS b \n    ON a.sid = b.sid \n    GROUP BY a.sid;<\/code><\/pre>\n\n\n\n
\u7ed3\u679c\uff1a<\/p>\n\n\n\n
+-----+-------+---------------------+------+-------+\n| sid | sname | sage                | ssex | score |\n+-----+-------+---------------------+------+-------+\n| 01  | \u8d75\u96f7  | 1990-01-01 00:00:00 | \u7537   | 80.0  |\n| 02  | \u94b1\u7535  | 1990-12-21 00:00:00 | \u7537   | 70.0  |\n| 03  | \u5b59\u98ce  | 1990-05-20 00:00:00 | \u7537   | 80.0  |\n| 04  | \u674e\u4e91  | 1990-08-06 00:00:00 | \u7537   | 50.0  |\n| 05  | \u5468\u6885  | 1991-12-01 00:00:00 | \u5973   | 76.0  |\n| 06  | \u5434\u5170  | 1992-03-01 00:00:00 | \u5973   | 31.0  |\n| 07  | \u90d1\u7af9  | 1989-07-01 00:00:00 | \u5973   | 89.0  |\n+-----+-------+---------------------+------+-------+\n7 rows in set<\/code><\/pre>\n\n\n\n
\u89e3\u6790\uff1a
\u786e\u5b9a\u662f\u4e24\u4e2a\u8868,student\u548csc\uff0c\u5173\u8054\u6761\u4ef6\u8fd8\u662fsid\u6d88\u9664\u7b1b\u5361\u5c14\u79ef\uff0c\u7136\u540e\u518dgroup by\uff0c\u6700\u540eselect \u53d6\u9700\u8981\u7684\u4fe1\u606f<\/p>\n\n\n\n<\/span>
5\u3001\u67e5\u8be2\u6240\u6709\u540c\u5b66\u7684\u5b66\u751f\u7f16\u53f7\u3001\u5b66\u751f\u59d3\u540d\u3001\u9009\u8bfe\u603b\u6570\u3001\u6240\u6709\u8bfe\u7a0b\u7684\u6210\u7ee9\u603b\u548c<\/h3>\n\n\n\n
#\u591a\u8868\u8054\u5408\u67e5\u8be2\u65b9\u5f0f\nSELECT t1.sid as \u5b66\u751f\u7f16\u53f7,t1.sname as \u5b66\u751f\u59d3\u540d,COUNT(t2.cid) as \u9009\u8bfe\u603b\u6570,SUM(t2.score) as \u8bfe\u7a0b\u6210\u7ee9\u603b\u548c FROM student t1, sc t2 WHERE t1.sid = t2.sid  GROUP BY t1.sid;\n \n#\u591a\u8868\u8fde\u63a5\u67e5\u8be2\nSELECT t1.sid as \u5b66\u751f\u7f16\u53f7,t1.sname as \u5b66\u751f\u59d3\u540d,COUNT(t2.cid) as \u9009\u8bfe\u603b\u6570,SUM(t2.score) as \u8bfe\u7a0b\u6210\u7ee9\u603b\u548c FROM student t1 JOIN sc t2 ON t1.sid = t2.sid  GROUP BY t1.sid;\n<\/code><\/pre>\n\n\n\n
\u7ed3\u679c\uff1a<\/p>\n\n\n\n
+----------+----------+----------+--------------+\n| \u5b66\u751f\u7f16\u53f7 | \u5b66\u751f\u59d3\u540d | \u9009\u8bfe\u603b\u6570 | \u8bfe\u7a0b\u6210\u7ee9\u603b\u548c |\n+----------+----------+----------+--------------+\n| 01       | \u8d75\u96f7     |        3 | 269.0        |\n| 02       | \u94b1\u7535     |        3 | 210.0        |\n| 03       | \u5b59\u98ce     |        3 | 240.0        |\n| 04       | \u674e\u4e91     |        3 | 100.0        |\n| 05       | \u5468\u6885     |        2 | 163.0        |\n| 06       | \u5434\u5170     |        2 | 65.0         |\n| 07       | \u90d1\u7af9     |        2 | 187.0        |\n+----------+----------+----------+--------------+\n7 rows in set<\/code><\/pre>\n\n\n\n
\u89e3\u6790\uff1a
\u4e24\u4e2a\u805a\u5408\u51fd\u6570\uff08\u7edf\u8ba1\u51fd\u6570\uff09\u4e00\u4e2acount(cid),\u4e00\u4e2asum(score),\u540c\u6837join student\u8868\u548csc\u8868\uff0c\u518dgroup by sid\u5373\u53ef<\/p>\n\n\n\n<\/span>
6\u3001\u67e5\u8be2\u300c\u674e\u300d\u59d3\u8001\u5e08\u7684\u6570\u91cf<\/h3>\n\n\n\n
SELECT COUNT(t.tid) FROM teacher t WHERE t.tname like\"%\u674e%\";<\/code><\/pre>\n\n\n\n
\u7ed3\u679c\uff1a<\/p>\n\n\n\n
+--------------+\n| COUNT(t.tid) |\n+--------------+\n|            1 |\n+--------------+\n1 row in set<\/code><\/pre>\n\n\n\n
\u89e3\u6790\uff1a
count\u52a0\u6761\u4ef6\u51fd\u6570\u52a0\u901a\u914d\u7b26\u5373\u53ef<\/p>\n\n\n\n<\/span>
7\u3001\u67e5\u8be2\u5b66\u8fc7\u300c\u5f20\u4e09\u300d\u8001\u5e08\u6388\u8bfe\u7684\u540c\u5b66\u7684\u4fe1\u606f<\/h3>\n\n\n\n
SELECT s.*,y.tname FROM \n    (SELECT sc.sid,x.tname FROM \n        (SELECT t.tname,c.cid FROM teacher AS t\n            JOIN course AS c \n            ON t.tid = c.tid\n                WHERE t.tname = '\u5f20\u4e09') AS x\n        JOIN sc\n        ON x.cid = sc.cid) AS y\nJOIN student AS s\nON y.sid = s.sid;<\/code><\/pre>\n\n\n\n
\u7ed3\u679c\uff1a<\/p>\n\n\n\n
+-----+-------+---------------------+------+-------+\n| sid | sname | sage                | ssex | tname |\n+-----+-------+---------------------+------+-------+\n| 01  | \u8d75\u96f7  | 1990-01-01 00:00:00 | \u7537   | \u5f20\u4e09  |\n| 02  | \u94b1\u7535  | 1990-12-21 00:00:00 | \u7537   | \u5f20\u4e09  |\n| 03  | \u5b59\u98ce  | 1990-05-20 00:00:00 | \u7537   | \u5f20\u4e09  |\n| 04  | \u674e\u4e91  | 1990-08-06 00:00:00 | \u7537   | \u5f20\u4e09  |\n| 05  | \u5468\u6885  | 1991-12-01 00:00:00 | \u5973   | \u5f20\u4e09  |\n| 07  | \u90d1\u7af9  | 1989-07-01 00:00:00 | \u5973   | \u5f20\u4e09  |\n+-----+-------+---------------------+------+-------+\n6 rows in set<\/code><\/pre>\n\n\n\n
\u89e3\u6790\uff1a
\u56db\u8868\u8fde\u63a5\uff0cteacher\u8868\u91cc\u7684tid\u4e0ecourse\u8868\u91cc\u7684tid\uff0c\u6761\u4ef6\u4e3atname=\u2018\u5f20\u4e09\u2019\uff0c\u518dcourse\u8868\u91cc\u7684cid\u4e0esc\u8868\u91cc\u7684cid\uff0c\u6700\u540esc\u8868\u91cc\u7684sid\u4e0estudent\u91cc\u7684sid<\/p>\n\n\n\n<\/span>
8\u3001\u67e5\u8be2\u6ca1\u6709\u5b66\u5168\u6240\u6709\u8bfe\u7a0b\u7684\u540c\u5b66\u7684\u4fe1\u606f<\/h3>\n\n\n\n
SELECT a.*,count(b.cid) AS \u6240\u5b66\u8bfe\u7a0b\u6570\nFROM student AS a\n    LEFT JOIN sc AS b\n    ON a.sid = b.sid\n        GROUP BY a.sid\n            HAVING COUNT(b.cid) < (SELECT COUNT(c.cid) FROM course as c);<\/code><\/pre>\n\n\n\n
\u7ed3\u679c\uff1a<\/p>\n\n\n\n
+-----+-------+---------------------+------+------------+\n| sid | sname | sage                | ssex | \u6240\u5b66\u8bfe\u7a0b\u6570 |\n+-----+-------+---------------------+------+------------+\n| 05  | \u5468\u6885  | 1991-12-01 00:00:00 | \u5973   |          2 |\n| 06  | \u5434\u5170  | 1992-03-01 00:00:00 | \u5973   |          2 |\n| 07  | \u90d1\u7af9  | 1989-07-01 00:00:00 | \u5973   |          2 |\n| 08  | \u738b\u83ca  | 1990-01-20 00:00:00 | \u5973   |          0 |\n+-----+-------+---------------------+------+------------+<\/code><\/pre>\n\n\n\n
\u89e3\u6790\uff1a
\u5148\u67e5\u8be2\u603b\u8bfe\u7a0b\u6570\uff0c\u518d\u67e5\u8be2\u6240\u6709\u540c\u5b66\u7684\u4fe1\u606f\uff0c\u7b5b\u9009\u6761\u4ef6\u4e3a\u5176\u6240\u5b66\u8bfe\u7a0b\u6570\u5c0f\u4e8e\u603b\u8bfe\u7a0b\u6570<\/p>\n\n\n\n<\/span>
9\u3001\u67e5\u8be2\u81f3\u5c11\u6709\u4e00\u95e8\u8bfe\u4e0e\u5b66\u53f7\u4e3a”01″\u7684\u540c\u5b66\u6240\u5b66\u76f8\u540c\u7684\u540c\u5b66\u7684\u4fe1\u606f<\/h3>\n\n\n\n
SELECT s.* FROM student AS s\n    JOIN sc\n    ON s.sid  = sc.sid \n        WHERE sc.cid in \n                    (SELECT sc.cid FROM sc AS sc WHERE sc.sid = '01') \n        GROUP bY s.sid \n             HAVING s.sid != '01';<\/code><\/pre>\n\n\n\n
\u7ed3\u679c\uff1a<\/p>\n\n\n\n
+-----+-------+---------------------+------+\n| sid | sname | sage                | ssex |\n+-----+-------+---------------------+------+\n| 02  | \u94b1\u7535  | 1990-12-21 00:00:00 | \u7537   |\n| 03  | \u5b59\u98ce  | 1990-05-20 00:00:00 | \u7537   |\n| 04  | \u674e\u4e91  | 1990-08-06 00:00:00 | \u7537   |\n| 05  | \u5468\u6885  | 1991-12-01 00:00:00 | \u5973   |\n| 06  | \u5434\u5170  | 1992-03-01 00:00:00 | \u5973   |\n| 07  | \u90d1\u7af9  | 1989-07-01 00:00:00 | \u5973   |\n+-----+-------+---------------------+------+\n6 rows in set<\/code><\/pre>\n\n\n\n
\u89e3\u6790\uff1a
\u5148\u4ece\u6210\u7ee9\u8868\u91cc\u67e5\u8be2\u5b66\u53f7\u4e3a01\u7684\u540c\u5b66\u6240\u5b66\u7684\u8bfe\u7a0b\u7f16\u53f7\uff0c\u7b5b\u9009\u6761\u4ef6\u4e3asc.cid in 01\u540c\u5b66\u6240\u5b66\u7f16\u53f7\uff0c\u518d\u4f7f\u7528\u5b66\u751f\u8868\u548c\u6210\u7ee9\u8868\u4e24\u8868\u5173\u8054\uff0c\u5173\u8054\u5b57\u6bb5\u4e3asid\uff0c\u5e76\u4e14\u628a\u8bfe\u7a0b\u7f16\u53f7\u4f5c\u4e3a\u5b50\u67e5\u8be2\u7684\u6761\u4ef6\uff0c\u5237\u9009\uff0c\u7136\u540e\u518dgroup by sid \u6700\u540e\u901a\u8fc7having\u7b5b\u9009sid \u4e0d\u7b49\u4e8e01<\/p>\n\n\n\n<\/span>
10\u3001\u67e5\u8be2\u548c”01″\u53f7\u7684\u540c\u5b66\u5b66\u4e60\u7684\u8bfe\u7a0b\u5b8c\u5168\u76f8\u540c\u7684\u5176\u4ed6\u540c\u5b66\u7684\u4fe1\u606f<\/h3>\n\n\n\n
select * from student t1 where t1.sid not in\n        (select p.sid fro\n \n                (select t.*,sc.sid sc_sid ,sc.cid sc_cid from\n                        (select * from student ,\n                                (select cid from sc where sid = \"01\") s ) t \n                        left join sc on t.sid = sc.sid and t.cid = sc.cid) p where sc_sid is null)\n        and t1.sid != \"01\"\nand (SELECT count(t3.cid) from sc t3 where t3.sid = t1.sid) = (SELECT COUNT(*) FROM sc t2 WHERE t2.sid = \"01\");\n<\/code><\/pre>\n\n\n\n
\u7ed3\u679c\uff1a<\/p>\n\n\n\n
+-----+-------+---------------------+------+---------------+\n| sid | sname | sage                | ssex | count(t3.cid) |\n+-----+-------+---------------------+------+---------------+\n| 02  | \u94b1\u7535  | 1990-12-21 00:00:00 | \u7537   |             3 |\n| 03  | \u5b59\u98ce  | 1990-05-20 00:00:00 | \u7537   |             3 |\n| 04  | \u674e\u4e91  | 1990-08-06 00:00:00 | \u7537   |             3 |\n+-----+-------+---------------------+------+---------------+\n3 rows in set<\/code><\/pre>\n\n\n\n
\u89e3\u6790\uff1a
\u5148\u4ece\u6210\u7ee9\u8868\u4e2d\u67e5\u8be2\u5b66\u53f7\u4e3a01\u7684\u603b\u8bfe\u7a0b\u6570\uff0c\u7136\u540e\u4f7f\u7528\u5b66\u751f\u8868\u548c\u6210\u7ee9\u8868\u5173\u8054\u67e5\u8be2\uff0c\u5173\u8054\u5b57\u6bb5\u4e3asid\uff0c\u6d88\u9664\u7b1b\u5361\u5c14\u79ef\uff0cwhere\u6761\u4ef6\u8bed\u53e5\u8fc7\u6ee4\u5b66\u53f701\uff0c\u5e76\u4e14\u7528\u5b66\u53f7\u5b57\u6bb5\u5206\u7ec4\uff0c\u5e76\u4e14\u4f7f\u7528having\u51fd\u6570\uff0c\u7edf\u8ba1\u8bfe\u7a0b\u603b\u6570=\u5b66\u53f7\u4e3a1\u7684\u8bfe\u7a0b\u603b\u6570<\/p>\n\n\n\n<\/span>
11\u3001\u67e5\u8be2\u6ca1\u5b66\u8fc7”\u5f20\u4e09”\u8001\u5e08\u8bb2\u6388\u7684\u4efb\u4e00\u95e8\u8bfe\u7a0b\u7684\u5b66\u751f\u59d3\u540d<\/h3>\n\n\n\n
#\u591a\u8868\u8054\u5408\u67e5\u8be2\u65b9\u5f0f\nSELECT student.sname FROM student \n    WHERE student.sid NOT IN \n        (SELECT sc.sid FROM sc     \n                    JOIN course \n                    ON sc.cid=course.cid\n                    JOIN teacher \n                    ON course.tid=teacher.tid \n        WHERE tname='\u5f20\u4e09' );\n \n#\u591a\u8868\u8fde\u63a5\u67e5\u8be2\u65b9\u5f0f\nSELECT student.sname FROM student \n    WHERE student.sid NOT IN \n        (SELECT sc.sid FROM sc,course,teacher where sc.cid = course.cid and course.tid=teacher.tid and tname='\u5f20\u4e09');\n<\/code><\/pre>\n\n\n\n
\u7ed3\u679c\uff1a<\/p>\n\n\n\n
+-------+\n| sname |\n+-------+\n| \u5434\u5170  |\n| \u738b\u83ca  |\n+-------+\n2 rows in set<\/code><\/pre>\n\n\n\n
\u89e3\u6790\uff1a
\u5148\u627e\u51fa\u6240\u6709\u5b66\u751f\u9009\u8bfe\u4fe1\u606f\u53casid\uff0c\u518d\u627e\u51fa\u5f20\u4e09\u8001\u5e08\u6388\u8bfe\u8bfe\u7a0b\uff0c\u5c06\u5176\u8fde\u63a5\uff0c\u518d\u7528student\u91cc\u7684sid not in \u524d\u9762\u7684sid<\/p>\n\n\n\n<\/span>
12\u3001\u67e5\u8be2\u4e24\u95e8\u53ca\u5176\u4ee5\u4e0a\u4e0d\u53ca\u683c\u8bfe\u7a0b\u7684\u540c\u5b66\u7684\u5b66\u53f7\uff0c\u59d3\u540d\u53ca\u5176\u5e73\u5747\u6210\u7ee9<\/h3>\n\n\n\n
SELECT c.sname, b.*\nFROM student c\n    JOIN ((\n        SELECT sid, COUNT(cid)\n        FROM sc\n        WHERE score < 60\n        GROUP BY sid\n        HAVING COUNT(cid) >= 2\n    ) a\n        JOIN (\n            SELECT sid, avg(score)\n            FROM sc\n            GROUP BY sid\n        ) b\n        ON a.sid = b.sid) ON c.sid = b.sid;<\/code><\/pre>\n\n\n\n
\u7ed3\u679c\uff1a<\/p>\n\n\n\n
+-------+-----+------------+\n| sname | sid | avg(score) |\n+-------+-----+------------+\n| \u674e\u4e91  | 04  | 33.33333   |\n| \u5434\u5170  | 06  | 32.50000   |\n+-------+-----+------------+\n2 rows in set<\/code><\/pre>\n\n\n\n
\u89e3\u6790\uff1a
\u5148\u67e5\u8be2\u51fa\u4e0d\u53ca\u683c\u4e24\u95e8\u6216\u4e24\u95e8\u4ee5\u4e0a\u7684\u6570\u636e\uff0c\u518d\u67e5\u8be2\u51fa\u4e0d\u53ca\u683c\u7684\u5e73\u5747\u6210\u7ee9\uff0c\u518d\u4e09\u5f20\u8868\u5d4c\u5957\u5173\u8054<\/p>\n\n\n\n<\/span>
13\u3001\u67e5\u8be2”01″\u8bfe\u7a0b\u5206\u6570\u5c0f\u4e8e 60\uff0c\u6309\u5206\u6570\u964d\u5e8f\u6392\u5217\u7684\u5b66\u751f\u4fe1\u606f<\/h3>\n\n\n\n
#\u591a\u8868\u8054\u5408\u67e5\u8be2\u65b9\u5f0f\nSELECT b.*, a.score\nFROM student b\n    JOIN (\n        SELECT *\n        FROM sc\n        WHERE cid = '01'\n            AND score < 60\n        ORDER BY score DESC\n    ) a\n    ON a.sid = b.sid;\n \n#\u591a\u8868\u8fde\u63a5\u67e5\u8be2\u65b9\u5f0f\nSELECT b.*, a.score\nFROM student b,\n    (\n        SELECT *\n        FROM sc\n        WHERE cid = '01'\n            AND score < 60\n        ORDER BY score DESC\n    ) a\n    where a.sid = b.sid;<\/code><\/pre>\n\n\n\n
\u7ed3\u679c\uff1a<\/p>\n\n\n\n
+-----+-------+---------------------+------+-------+\n| sid | sname | sage                | ssex | score |\n+-----+-------+---------------------+------+-------+\n| 04  | \u674e\u4e91  | 1990-08-06 00:00:00 | \u7537   | 50.0  |\n| 06  | \u5434\u5170  | 1992-03-01 00:00:00 | \u5973   | 31.0  |\n+-----+-------+---------------------+------+-------+\n2 rows in set<\/code><\/pre>\n\n\n\n
\u89e3\u6790\uff1a
\u5148\u67e5\u8be2\u51fa01\u8bfe\u7a0b\u5206\u6570\u5c0f\u4e8e60\u7684sid \uff0c\u6309\u7167\u5206\u6570\u964d\u5e8f\uff0c\u7136\u540e\u548c\u5b66\u751f\u8868\u5173\u8054<\/p>\n\n\n\n<\/span>
14\u3001\u6309\u5e73\u5747\u6210\u7ee9\u4ece\u9ad8\u5230\u4f4e\u663e\u793a\u6240\u6709\u5b66\u751f\u7684\u6240\u6709\u8bfe\u7a0b\u7684\u6210\u7ee9\u4ee5\u53ca\u5e73\u5747\u6210\u7ee9<\/h3>\n\n\n\n
SELECT a.sid, a.score, a.cid, b.`\u5e73\u5747\u6210\u7ee9`\nFROM sc a\n    JOIN (\n        SELECT sid, avg(score) AS \u5e73\u5747\u6210\u7ee9\n        FROM sc\n        GROUP BY sid\n    ) b\n    ON a.sid = b.sid\nORDER BY b.`\u5e73\u5747\u6210\u7ee9` DESC;<\/code><\/pre>\n\n\n\n
\u7ed3\u679c\uff1a<\/p>\n\n\n\n
+-----+-------+-----+----------+\n| sid | score | cid | \u5e73\u5747\u6210\u7ee9 |\n+-----+-------+-----+----------+\n| 07  | 89.0  | 02  | 93.50000 |\n| 07  | 98.0  | 03  | 93.50000 |\n| 01  | 80.0  | 01  | 89.66667 |\n| 01  | 90.0  | 02  | 89.66667 |\n| 01  | 99.0  | 03  | 89.66667 |\n| 05  | 76.0  | 01  | 81.50000 |\n| 05  | 87.0  | 02  | 81.50000 |\n| 03  | 80.0  | 01  | 80.00000 |\n| 03  | 80.0  | 02  | 80.00000 |\n| 03  | 80.0  | 03  | 80.00000 |\n| 02  | 70.0  | 01  | 70.00000 |\n| 02  | 60.0  | 02  | 70.00000 |\n| 02  | 80.0  | 03  | 70.00000 |\n| 04  | 50.0  | 01  | 33.33333 |\n| 04  | 30.0  | 02  | 33.33333 |\n| 04  | 20.0  | 03  | 33.33333 |\n| 06  | 31.0  | 01  | 32.50000 |\n| 06  | 34.0  | 03  | 32.50000 |\n+-----+-------+-----+----------+\n18 rows in set<\/code><\/pre>\n\n\n\n
\u89e3\u6790\uff1a
\u5148\u6c42\u5e73\u5747\u6210\u7ee9\uff0c\u6ce8\u610f\uff0c\u8fd9\u91cc\u7684\u5e73\u5747\u6210\u7ee9\u4e00\u5b9a\u8981\u53d6\u522b\u540d\uff0c\u7136\u540e\u53d6\u6240\u6709\u4eba\u7684\u6210\u7ee9\uff0c\u518d\u5173\u8054\uff0c\u7136\u540e\u6309\u7167\u5e73\u5747\u6210\u7ee9\u964d\u5e8f\u6392\u5217<\/p>\n\n\n\n<\/span>
15\u3001\u67e5\u8be2\u5404\u79d1\u6210\u7ee9\u6700\u9ad8\u5206\u3001\u6700\u4f4e\u5206\u548c\u5e73\u5747\u5206<\/h3>\n\n\n\n
\u4ee5\u5982\u4e0b\u5f62\u5f0f\u663e\u793a\uff1a<\/p>\n\n\n\n
\u3000\u3000\u8bfe\u7a0b id\uff0c\u6700\u9ad8\u5206\uff0c\u6700\u4f4e\u5206\uff0c\u5e73\u5747\u5206\uff0c\u53ca\u683c\u7387\uff0c\u4e2d\u7b49\u7387\uff0c\u4f18\u826f\u7387\uff0c\u4f18\u79c0\u7387
\u3000\u3000\u53ca\u683c\u4e3a>=60\uff0c\u4e2d\u7b49\u4e3a\uff1a[70,80)\uff0c\u4f18\u826f\u4e3a\uff1a[80-90)\uff0c\u4f18\u79c0\u4e3a\uff1a>=90
\u3000\u3000\u8981\u6c42\u8f93\u51fa\u8bfe\u7a0b\u53f7\u548c\u9009\u4fee\u4eba\u6570\uff0c\u67e5\u8be2\u7ed3\u679c\u6309\u4eba\u6570\u964d\u5e8f\u6392\u5217\uff0c\u82e5\u4eba\u6570\u76f8\u540c\uff0c\u6309\u8bfe\u7a0b\u53f7\u5347\u5e8f<\/p>\n\n\n\n
SELECT cid AS \u8bfe\u7a0bid, MAX(score) AS \u6700\u9ad8\u5206, MIN(score) AS \u6700\u4f4e\u5206, AVG(score) AS \u5e73\u5747\u5206\n    , SUM(CASE \n        WHEN score >= 60 THEN 1\n        ELSE 0\n    END) \/ COUNT(sid) AS \u53ca\u683c\u7387\n    , SUM(CASE \n        WHEN score >= 70\n            AND score < 80\n        THEN 1\n        ELSE 0\n    END) \/ count(sid) AS \u4e2d\u7b49\u7387\n    , SUM(CASE \n        WHEN score >= 80\n            AND score < 90\n        THEN 1\n        ELSE 0\n    END) \/ count(sid) AS \u4f18\u826f\u7387\n    , SUM(CASE \n        WHEN score >= 90 THEN 1\n        ELSE 0\n    END) \/ count(sid) AS \u4f18\u79c0\u7387\nFROM sc\nGROUP BY cid\nORDER BY cid ASC;<\/code><\/pre>\n\n\n\n
\u7ed3\u679c\uff1a<\/p>\n\n\n\n
+--------+--------+--------+----------+--------+--------+--------+--------+\n| \u8bfe\u7a0bid | \u6700\u9ad8\u5206 | \u6700\u4f4e\u5206 | \u5e73\u5747\u5206   | \u53ca\u683c\u7387 | \u4e2d\u7b49\u7387 | \u4f18\u826f\u7387 | \u4f18\u79c0\u7387 |\n+--------+--------+--------+----------+--------+--------+--------+--------+\n| 01     | 80.0   | 31.0   | 64.50000 | 0.6667 | 0.3333 | 0.3333 | 0.0000 |\n| 02     | 90.0   | 30.0   | 72.66667 | 0.8333 | 0.0000 | 0.5000 | 0.1667 |\n| 03     | 99.0   | 20.0   | 68.50000 | 0.6667 | 0.0000 | 0.3333 | 0.3333 |\n+--------+--------+--------+----------+--------+--------+--------+--------+\n3 rows in set<\/code><\/pre>\n\n\n\n
\u89e3\u6790\uff1a
\u91cd\u70b9\u5728case when\u8bed\u53e5\u7684\u7528\u6cd5\uff0c\u5176\u5b9ecase when \u5c31\u7c7b\u4f3c\u4e8e if\u51fd\u6570 if x>\u67d0\u4e2a\u503c\uff0cthen 1 else 0\u3002\u5c31\u53ea\u7528\u4e00\u4e2a\u8868\uff0c\u53ea\u662f\u5bf9\u8868\u5934\u9700\u8981\u505a\u4fee\u6539\uff0c\u7528\u805a\u5408\u51fd\u6570+AS<\/p>\n\n\n\n<\/span>
16\u3001\u6309\u5404\u79d1\u6210\u7ee9\u8fdb\u884c\u6392\u5e8f\uff0c\u5e76\u663e\u793a\u6392\u540d\uff0c Score \u91cd\u590d\u65f6\u4fdd\u7559\u540d\u6b21\u7a7a\u7f3a<\/h3>\n\n\n\n
-- MySql8.0\u4ee5\u4e0a\nselect *, rank() over(partition by cid order by score desc) AS ranked from sc;\n \n-- MySql5.7\u4e3a\u5b9e\u73b0\u5206\u7ec4\u7ec4\u5185\u6392\u540d\nselect s.*, @rank:=@rank+1  as ranked from sc as s,(SELECT @rank:=0) as p  ORDER BY score desc;<\/code><\/pre>\n\n\n\n
\u7ed3\u679c\uff1a<\/p>\n\n\n\n
+-----+-----+-------+--------+\n| sid | cid | score | ranked |\n+-----+-----+-------+--------+\n| 01  | 01  | 80.0  |      1 |\n| 03  | 01  | 80.0  |      1 |\n| 05  | 01  | 76.0  |      3 |\n| 02  | 01  | 70.0  |      4 |\n| 04  | 01  | 50.0  |      5 |\n| 06  | 01  | 31.0  |      6 |\n| 01  | 02  | 90.0  |      1 |\n| 07  | 02  | 89.0  |      2 |\n| 05  | 02  | 87.0  |      3 |\n| 03  | 02  | 80.0  |      4 |\n| 02  | 02  | 60.0  |      5 |\n| 04  | 02  | 30.0  |      6 |\n| 01  | 03  | 99.0  |      1 |\n| 07  | 03  | 98.0  |      2 |\n| 02  | 03  | 80.0  |      3 |\n| 03  | 03  | 80.0  |      3 |\n| 06  | 03  | 34.0  |      5 |\n| 04  | 03  | 20.0  |      6 |\n+-----+-----+-------+--------+\n18 rows in set<\/code><\/pre>\n\n\n\n
\u89e3\u6790\uff1a
\u6ce8\u610f\uff1amysql8.0\u4e4b\u524d \u662f\u6ca1\u6709rank\u51fd\u6570
MySQL\u53ef\u4ee5\u5b9e\u73b0Oracle\u4e2d\u7684\u6392\u540d\u516c\u5f0f\uff0c\u4e00\u5171\u6709\u4e09\u79cd<\/p>\n\n\n\n
    \n
  1. rank() over(order by col_name desc\uff09<\/li>\n\n\n\n
  2. dense_rank() over()<\/li>\n\n\n\n
  3. row_number() over()
    \u7b2c\u4e00\u4e2a\u662f\u5982\u679c\u51fa\u73b0\u4e86\u76f8\u540c\u6392\u540d\u90fd\u4e3a\u540c\u4e00\u6392\u540d\uff0c\u4e0b\u4e2a\u6392\u540d\u8df3\u8fc7\uff0c\u4f8b\u59821,1,3,4
    \u7b2c\u4e8c\u4e2a\u662f\u5982\u679c\u51fa\u73b0\u4e86\u76f8\u540c\u6392\u540d\u90fd\u4e3a\u540c\u4e00\u6392\u540d\uff0c\u4e0b\u4e2a\u6392\u540d\u4e0d\u8df3\u8fc7\uff0c\u4f8b\u59821,1,2,3
    \u7b2c\u4e09\u4e2a\u662f\u76f4\u63a5\u5bf9\u884c\u8fdb\u884c\u6392\u540d\u4e0d\u5206\u662f\u5426\u6709\u76f8\u540c\u503c<\/li>\n<\/ol>\n\n\n\n
    \u6b64\u9898\u76ee\u8981\u6309\u7167\u5404\u79d1\u6210\u7ee9\u8fdb\u884c\u6392\u5e8f over()\u4e2d\u8981\u586bpartition by col_name order by col_name
    \u7b2c\u4e00\u4e2acolname \u4e3a\u5206\u7ec4\u7684\u5185\u5bb9\uff0c\u7b2c\u4e8c\u4e2a\u662f\u6309\u4ec0\u4e48\u503c\u6392\u7684\u5185\u5bb9<\/p>\n\n\n\n<\/span>
    17\u3001\u67e5\u8be2\u5b66\u751f\u7684\u603b\u6210\u7ee9\uff0c\u5e76\u8fdb\u884c\u6392\u540d\uff0c\u603b\u5206\u91cd\u590d\u65f6\u4fdd\u7559\u540d\u6b21\u7a7a\u7f3a<\/h3>\n\n\n\n
    -- MySql8.0\u4ee5\u4e0a\nSELECT a.*, rank() OVER (ORDER BY a.\u603b\u6210\u7ee9 DESC) AS Ranked\nFROM (\n    SELECT *, SUM(score) AS \u603b\u6210\u7ee9\n    FROM sc\n    GROUP BY sid\n) a;\n \n-- MySql5.7\nSELECT a.*, @rank := @rank + 1 AS ranked\nFROM (\n    SELECT s.*, SUM(score) AS \u603b\u6210\u7ee9\n    FROM sc s\n    GROUP BY sid\n) a, (\n        SELECT @rank := 0\n    ) p\nORDER BY a.\u603b\u6210\u7ee9 DESC;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-----+-----+-------+--------+--------+\n| sid | cid | score | \u603b\u6210\u7ee9 | Ranked |\n+-----+-----+-------+--------+--------+\n| 01  | 01  | 80.0  | 269.0  |      1 |\n| 03  | 01  | 80.0  | 240.0  |      2 |\n| 02  | 01  | 70.0  | 210.0  |      3 |\n| 07  | 02  | 89.0  | 187.0  |      4 |\n| 05  | 01  | 76.0  | 163.0  |      5 |\n| 04  | 01  | 50.0  | 100.0  |      6 |\n| 06  | 01  | 31.0  | 65.0   |      7 |\n+-----+-----+-------+--------+--------+\n7 rows in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    \u8ddf\u4e0a\u9898\u4e00\u6837\u7528rank\uff08\uff09over\uff08\uff09\uff0c\u53ea\u662f\u591a\u4e86\u5c42\u5d4c\u5957<\/p>\n\n\n\n<\/span>
    18\u3001\u67e5\u8be2\u5b66\u751f\u7684\u603b\u6210\u7ee9\uff0c\u5e76\u8fdb\u884c\u6392\u540d\uff0c\u603b\u5206\u91cd\u590d\u65f6\u4e0d\u4fdd\u7559\u540d\u6b21\u7a7a\u7f3a<\/h3>\n\n\n\n
    SELECT a.*, dense_rank() OVER (ORDER BY a.total_socre DESC) AS Ranked\nFROM (\n    SELECT *, SUM(score) AS total_socre\n    FROM sc\n    GROUP BY sid\n) a;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-----+-----+-------+-------------+--------+\n| sid | cid | score | total_socre | Ranked |\n+-----+-----+-------+-------------+--------+\n| 01  | 01  | 80.0  | 269.0       |      1 |\n| 03  | 01  | 80.0  | 240.0       |      2 |\n| 02  | 01  | 70.0  | 210.0       |      3 |\n| 07  | 02  | 89.0  | 187.0       |      4 |\n| 05  | 01  | 76.0  | 163.0       |      5 |\n| 04  | 01  | 50.0  | 100.0       |      6 |\n| 06  | 01  | 31.0  | 65.0        |      7 |\n+-----+-----+-------+-------------+--------+\n7 rows in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    \u548c\u4e0a\u9762\u4e00\u6837\uff0c\u53ea\u662f\u6362\u6210dense_rank () over()\uff0c\u53ea\u662f\u603b\u5206\u6ca1\u6709\u91cd\u590d\u65e0\u6cd5\u770b\u51fa\u533a\u522b<\/p>\n\n\n\n<\/span>
    19\u3001\u7edf\u8ba1\u5404\u79d1\u6210\u7ee9\u5404\u5206\u6570\u6bb5\u4eba\u6570\uff1a\u8bfe\u7a0b\u7f16\u53f7\uff0c[100-85)\uff0c[85-70)\uff0c[70-60)\uff0c[60-0] \u53ca\u6240\u5360\u767e\u5206\u6bd4<\/h3>\n\n\n\n
    SELECT cid AS \u8bfe\u7a0bID, \nSUM(CASE WHEN score <= 60 THEN 1 ELSE 0 END)\/count(sid) AS \u767e\u5206\u6bd41,\nSUM(CASE WHEN score >60 AND score <=70 THEN 1 ELSE 0 END)\/count(sid) AS \u767e\u5206\u6bd42,\nSUM(CASE WHEN score >70 AND score <=85 THEN 1 ELSE 0 END)\/count(sid) AS \u767e\u5206\u6bd43,\nSUM(CASE WHEN score >85 THEN 1 ELSE 0 END)\/count(sid) AS \u767e\u5206\u6bd44\nFROM sc GROUP BY cid ORDER BY cid;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +--------+---------+---------+---------+---------+\n| \u8bfe\u7a0bID | \u767e\u5206\u6bd41 | \u767e\u5206\u6bd42 | \u767e\u5206\u6bd43 | \u767e\u5206\u6bd44 |\n+--------+---------+---------+---------+---------+\n| 01     | 0.3333  | 0.1667  | 0.5000  | 0.0000  |\n| 02     | 0.3333  | 0.0000  | 0.1667  | 0.5000  |\n| 03     | 0.3333  | 0.0000  | 0.3333  | 0.3333  |\n+--------+---------+---------+---------+---------+\n3 rows in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a<\/p>\n\n\n\n
    \u4f7f\u7528case when<\/p>\n\n\n\n<\/span>
    20\u3001\u67e5\u8be2\u5404\u79d1\u6210\u7ee9\u524d\u4e09\u540d\u7684\u8bb0\u5f55<\/h3>\n\n\n\n
    -- MySql8.0\u4ee5\u4e0a\nSELECT * FROM\n(SELECT *,rank() over(PARTITION by cid ORDER BY score desc) as ranked FROM sc) as a\nWHERE a.ranked <=3;\n \n-- MySql5.7\nSELECT *\nFROM sc\nWHERE (\n    SELECT count(*)\n    FROM sc a\n    WHERE sc.CId = a.CId\n        AND sc.score < a.score\n) < 3\nORDER BY CId ASC, sc.score DESC\uff1b<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-----+-----+-------+--------+\n| sid | cid | score | ranked |\n+-----+-----+-------+--------+\n| 01  | 01  | 80.0  |      1 |\n| 03  | 01  | 80.0  |      1 |\n| 05  | 01  | 76.0  |      3 |\n| 01  | 02  | 90.0  |      1 |\n| 07  | 02  | 89.0  |      2 |\n| 05  | 02  | 87.0  |      3 |\n| 01  | 03  | 99.0  |      1 |\n| 07  | 03  | 98.0  |      2 |\n| 02  | 03  | 80.0  |      3 |\n| 03  | 03  | 80.0  |      3 |\n+-----+-----+-------+--------+  \n10 rows in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    \u4e0e\u4e0a\u9762rank\u4e00\u6837\uff0c\u7528rank\uff08\uff09over\uff08\uff09where ranked <=3
    \u6ce8\u610f\uff01where \u7684\u6267\u884c\u987a\u5e8f\u5728select\u524d\uff0c\u5d4c\u5957\u4e00\u4e2aselect \u8bed\u53e5\u5c31\u597d<\/p>\n\n\n\n
    MySql5.7\u7248\u672c\uff1a\u4f7f\u7528\u5d4c\u5957\u5faa\u73af\u627e\u51facid\u76f8\u540c\u5e76\u4e14\u6bd4\u81ea\u5df1score\u5927\u4f46\u4e0d\u8d85\u8fc7\u4e09\u6761\u7684\u6570\u636e\uff08\u524d\u4e09\u540d\uff09<\/p>\n\n\n\n<\/span>
    21\u3001\u67e5\u8be2\u6bcf\u95e8\u8bfe\u7a0b\u88ab\u9009\u4fee\u7684\u5b66\u751f\u6570<\/h3>\n\n\n\n
    SELECT cid AS \u8bfe\u7a0bid, COUNT(sid) AS \u9009\u4fee\u7684\u5b66\u751f\u6570\nFROM sc\nGROUP BY cid\nORDER BY \u8bfe\u7a0bid;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +--------+--------------+\n| \u8bfe\u7a0bid | \u9009\u4fee\u7684\u5b66\u751f\u6570 |\n+--------+--------------+\n| 01     |            6 |\n| 02     |            6 |\n| 03     |            6 |\n+--------+--------------+\n3 rows in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    \u5355\u8868\u67e5\u8be2\uff0c\u4f7f\u7528group by \uff0corder by<\/p>\n\n\n\n<\/span>
    22\u3001\u67e5\u8be2\u51fa\u53ea\u9009\u4fee\u4e24\u95e8\u8bfe\u7a0b\u7684\u5b66\u751f\u5b66\u53f7\u548c\u59d3\u540d<\/h3>\n\n\n\n
    SELECT student.sname, a.*\nFROM student\n    JOIN (\n        SELECT sid, count(cid) AS \u9009\u4fee\u8bfe\u7a0b\u6570\n        FROM sc\n        GROUP BY sid\n        HAVING \u9009\u4fee\u8bfe\u7a0b\u6570 = 2\n    ) a\n    ON student.sid = a.sid;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-------+-----+------------+\n| sname | sid | \u9009\u4fee\u8bfe\u7a0b\u6570 |\n+-------+-----+------------+\n| \u5468\u6885  | 05  |          2 |\n| \u5434\u5170  | 06  |          2 |\n| \u90d1\u7af9  | 07  |          2 |\n+-------+-----+------------+\n3 rows in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    \u5148\u4ece\u6210\u7ee9\u8868\u4e2d\u67e5\u8be2\u51fa\u53ea\u9009\u4fee\u4e24\u95e8\u8bfe\u7a0b\u7684\u5b66\u751fid\u548c\u8bfe\u7a0b\u6570\uff0c\u518d\u548c\u5b66\u751f\u8868\u8fdb\u884c\u5173\u8054\u67e5\u8be2<\/p>\n\n\n\n<\/span>
    23\u3001\u67e5\u8be2\u7537\u751f\u3001\u5973\u751f\u4eba\u6570<\/h3>\n\n\n\n
    SELECT ssex,COUNT(sid) FROM student GROUP BY ssex;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +------+------------+\n| ssex | COUNT(sid) |\n+------+------------+\n| \u7537   |          4 |\n| \u5973   |          4 |\n+------+------------+\n2 rows in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    \u6839\u636essex group by\u540e\u518dcount()<\/p>\n\n\n\n<\/span>
    24\u3001\u67e5\u8be2\u540d\u5b57\u4e2d\u542b\u6709\u300c\u98ce\u300d\u5b57\u7684\u5b66\u751f\u4fe1\u606f<\/h3>\n\n\n\n
    SELECT * FROM student WHERE sname like\"%\u98ce%\";<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-----+-------+---------------------+------+\n| sid | sname | sage                | ssex |\n+-----+-------+---------------------+------+\n| 03  | \u5b59\u98ce  | 1990-05-20 00:00:00 | \u7537   |\n+-----+-------+---------------------+------+\n1 row in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    \u901a\u914d\u7b26\uff0c%\uff0c\u2018%a\u2019a\u7ed3\u5c3e\uff0c\u2018a%\u2019a\u5f00\u5934\uff0c\u2018%a%\u2019\u542b\u6709a<\/p>\n\n\n\n<\/span>
    25\u3001\u67e5\u8be2\u540c\u540d\u540c\u6027\u5b66\u751f\u540d\u5355\uff0c\u5e76\u7edf\u8ba1\u540c\u540d\u4eba\u6570<\/h3>\n\n\n\n
    SELECT *, COUNT(sid) AS \u540c\u540d\u4eba\u6570\nFROM (\n    SELECT a.*\n    FROM student a\n        JOIN student b\n    WHERE a.sname = b.sname\n        AND a.ssex = b.ssex\n) c\nGROUP BY sid\nHAVING \u540c\u540d\u4eba\u6570 >= 2;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    \u89e3\u6790\uff1a
    \u8fde\u63a5\u8868student\u548cstudent on ssname and ssex \u5728group by sid\uff08\u56e0\u4e3aid\u552f\u4e00\uff0cname\u53ef\u80fd\u91cd\u540d\uff09\uff0ccount sid<\/p>\n\n\n\n<\/span>
    26\u3001\u67e5\u8be2 1990 \u5e74\u51fa\u751f\u7684\u5b66\u751f\u540d\u5355<\/h3>\n\n\n\n
    SELECT * FROM student  WHERE YEAR(sage) = 1990;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-----+-------+---------------------+------+\n| sid | sname | sage                | ssex |\n+-----+-------+---------------------+------+\n| 01  | \u8d75\u96f7  | 1990-01-01 00:00:00 | \u7537   |\n| 02  | \u94b1\u7535  | 1990-12-21 00:00:00 | \u7537   |\n| 03  | \u5b59\u98ce  | 1990-05-20 00:00:00 | \u7537   |\n| 04  | \u674e\u4e91  | 1990-08-06 00:00:00 | \u7537   |\n| 08  | \u738b\u83ca  | 1990-01-20 00:00:00 | \u5973   |\n+-----+-------+---------------------+------+\n5 rows in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    sage\u4e00\u5217\u4e3adatetime\u7c7b\u578b\uff0c\u7528\u65f6\u95f4\u51fd\u6570\u3002MySQL\u91cc\u9762\u80fd\u591f\u5bf9datetime\u7c7b\u578b\u51fd\u6570\u622a\u53d6\u5e74\u3001\u6708\u3001\u5468\u3001\u65e5\u7b49\u7b49 \uff0c\u7528YEAR()\u6765\u8868\u793a\u5e74\uff0c\u4ee5\u6b64\u7c7b\u63a8<\/p>\n\n\n\n<\/span>
    27\u3001\u67e5\u8be2\u6bcf\u95e8\u8bfe\u7a0b\u7684\u5e73\u5747\u6210\u7ee9\uff0c\u7ed3\u679c\u6309\u5e73\u5747\u6210\u7ee9\u964d\u5e8f\u6392\u5217\uff0c\u5e73\u5747\u6210\u7ee9\u76f8\u540c\u65f6\uff0c\u6309\u8bfe\u7a0b\u7f16\u53f7\u5347\u5e8f\u6392\u5217<\/h3>\n\n\n\n
    SELECT cid,avg(score) AS \u5e73\u5747\u6210\u7ee9 FROM sc GROUP BY cid ORDER BY \u5e73\u5747\u6210\u7ee9 DESC,cid ASC;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-----+----------+\n| cid | \u5e73\u5747\u6210\u7ee9 |\n+-----+----------+\n| 02  | 72.66667 |\n| 03  | 68.50000 |\n| 01  | 64.50000 |\n+-----+----------+\n3 rows in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    order by x desc,y,z,… \u5148\u6839\u636ex\u6392\u5e8f\uff0c\u518d\u6839\u636ey\uff0c\u7136\u540ez….<\/p>\n\n\n\n<\/span>
    28\u3001 \u67e5\u8be2\u5e73\u5747\u6210\u7ee9\u5927\u4e8e\u7b49\u4e8e 85 \u7684\u6240\u6709\u5b66\u751f\u7684\u5b66\u53f7\u3001\u59d3\u540d\u548c\u5e73\u5747\u6210\u7ee9<\/h3>\n\n\n\n
    SELECT student.sname, a.*\nFROM student\n    JOIN (\n        SELECT sid AS \u5b66\u53f7, avg(score) AS \u5e73\u5747\u6210\u7ee9\n        FROM sc\n        GROUP BY sid\n        HAVING \u5e73\u5747\u6210\u7ee9 > 85\n    ) a\n    ON student.sid = a.\u5b66\u53f7;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-------+------+----------+\n| sname | \u5b66\u53f7 | \u5e73\u5747\u6210\u7ee9 |\n+-------+------+----------+\n| \u8d75\u96f7  | 01   | 89.66667 |\n| \u90d1\u7af9  | 07   | 93.50000 |\n+-------+------+----------+\n2 rows in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    \u5148\u4ece\u6210\u7ee9\u8868\u4e2d\u67e5\u8be2\u51fa\u5e73\u5747\u6210\u7ee9\u5927\u4e8e85\u7684\u5b66\u751f\u597d\u548c\u5e73\u5747\u6210\u7ee9\uff08\u8bb0\u4f4f\uff0c\u8fd9\u91cc\u9700\u8981\u53d6\u522b\u540d\uff09\uff0c\u7136\u540e\u518d\u548c\u5b66\u751f\u8868\u5173\u8054\uff0c\u5173\u8054\u5b57\u6bb5\u4e3asid\uff0c\u83b7\u53d6\u5230\u5b66\u751f\u540d\u5b57<\/p>\n\n\n\n<\/span>
    29\u3001\u67e5\u8be2\u8bfe\u7a0b\u540d\u79f0\u4e3a\u300c\u6570\u5b66\u300d\uff0c\u4e14\u5206\u6570\u4f4e\u4e8e 60 \u7684\u5b66\u751f\u59d3\u540d\u548c\u5206\u6570<\/h3>\n\n\n\n
    SELECT student.sname, c.*\nFROM student\n    JOIN (\n        SELECT t1.cname, t2.score, t2.sid\n        FROM course t1\n            JOIN sc t2 ON t1.cid = t2.cid\n        WHERE t2.score < 60\n            AND t1.cname = '\u6570\u5b66'\n    ) c\n    ON student.sid = c.sid;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-------+-------+-------+-----+\n| sname | cname | score | sid |\n+-------+-------+-------+-----+\n| \u674e\u4e91  | \u6570\u5b66  | 30.0  | 04  |\n+-------+-------+-------+-----+\n1 row in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    \u5148\u628a\u8bfe\u7a0b\u8868\u548c\u6210\u7ee9\u8868\u5173\u8054\uff0c\u83b7\u53d6\u5230\u4f4e\u4e8e60\u5206\u7684\u5b66\u751f\u53f7\u3001\u5206\u6570\u548c\u8bfe\u7a0b\u540d\u79f0\uff0c\u4f5c\u4e3a\u4e34\u65f6\u8868\uff0c\u7136\u540e\u518d\u548c\u5b66\u751f\u8868\u5173\u8054\uff0c\u83b7\u53d6\u5230\u6700\u540e\u4e00\u4e2a\u5b57\u6bb5\uff0c\u5b66\u751f\u59d3\u540d<\/p>\n\n\n\n<\/span>
    30\u3001\u67e5\u8be2\u6240\u6709\u5b66\u751f\u7684\u8bfe\u7a0b\u53ca\u5206\u6570\u60c5\u51b5\uff08\u5b58\u5728\u5b66\u751f\u6ca1\u6210\u7ee9\uff0c\u6ca1\u9009\u8bfe\u7684\u60c5\u51b5\uff09<\/h3>\n\n\n\n
    SELECT student.sname, c.*\nFROM student\n    JOIN (\n        SELECT a.cname, b.sid, b.score\n        FROM course a\n            LEFT JOIN sc b ON a.cid = b.cid\n    ) c\n    ON student.sid = c.sid;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-------+-------+-----+-------+\n| sname | cname | sid | score |\n+-------+-------+-----+-------+\n| \u8d75\u96f7  | \u8bed\u6587  | 01  | 80.0  |\n| \u8d75\u96f7  | \u6570\u5b66  | 01  | 90.0  |\n| \u8d75\u96f7  | \u82f1\u8bed  | 01  | 99.0  |\n| \u94b1\u7535  | \u8bed\u6587  | 02  | 70.0  |\n| \u94b1\u7535  | \u6570\u5b66  | 02  | 60.0  |\n| \u94b1\u7535  | \u82f1\u8bed  | 02  | 80.0  |\n| \u5b59\u98ce  | \u8bed\u6587  | 03  | 80.0  |\n| \u5b59\u98ce  | \u6570\u5b66  | 03  | 80.0  |\n| \u5b59\u98ce  | \u82f1\u8bed  | 03  | 80.0  |\n| \u674e\u4e91  | \u8bed\u6587  | 04  | 50.0  |\n| \u674e\u4e91  | \u6570\u5b66  | 04  | 30.0  |\n| \u674e\u4e91  | \u82f1\u8bed  | 04  | 20.0  |\n| \u5468\u6885  | \u8bed\u6587  | 05  | 76.0  |\n| \u5468\u6885  | \u6570\u5b66  | 05  | 87.0  |\n| \u5434\u5170  | \u8bed\u6587  | 06  | 31.0  |\n| \u5434\u5170  | \u82f1\u8bed  | 06  | 34.0  |\n| \u90d1\u7af9  | \u6570\u5b66  | 07  | 89.0  |\n| \u90d1\u7af9  | \u82f1\u8bed  | 07  | 98.0  |\n+-------+-------+-----+-------+\n18 rows in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    \u5148\u628a\u8bfe\u7a0b\u8868\u548c\u6210\u7ee9\u8868\u5173\u8054\uff0c\u5173\u8054\u5b57\u6bb5\u4e3acid\uff0c\u83b7\u53d6\u5230\u8bfe\u7a0b\u540d\u79f0\uff0c\u5b66\u751f\u53f7\u548c\u5b66\u79d1\u6210\u7ee9\uff0c\u4f5c\u4e3a\u4e34\u65f6\u8868\uff0c\u7136\u540e\u518d\u548c\u5b66\u751f\u8868\u5173\u8054\uff0c\u5173\u8054\u5b57\u6bb5\u4e3asid\uff0c\u83b7\u53d6\u5230\u5b66\u751f\u540d\u5b57<\/p>\n\n\n\n<\/span>
    31\u3001\u67e5\u8be2\u4efb\u4f55\u4e00\u95e8\u8bfe\u7a0b\u6210\u7ee9\u5728 70 \u5206\u4ee5\u4e0a\u7684\u59d3\u540d\u3001\u8bfe\u7a0b\u540d\u79f0\u548c\u5206\u6570<\/h3>\n\n\n\n
    SELECT student.sname, c.*\nFROM student\n    JOIN (\n        SELECT a.cname, b.sid, b.score\n        FROM course a\n            LEFT JOIN sc b ON a.cid = b.cid\n    ) c\n    ON student.sid = c.sid\nWHERE c.score > 70;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-------+-------+-----+-------+\n| sname | cname | sid | score |\n+-------+-------+-----+-------+\n| \u8d75\u96f7  | \u8bed\u6587  | 01  | 80.0  |\n| \u8d75\u96f7  | \u6570\u5b66  | 01  | 90.0  |\n| \u8d75\u96f7  | \u82f1\u8bed  | 01  | 99.0  |\n| \u94b1\u7535  | \u82f1\u8bed  | 02  | 80.0  |\n| \u5b59\u98ce  | \u8bed\u6587  | 03  | 80.0  |\n| \u5b59\u98ce  | \u6570\u5b66  | 03  | 80.0  |\n| \u5b59\u98ce  | \u82f1\u8bed  | 03  | 80.0  |\n| \u5468\u6885  | \u8bed\u6587  | 05  | 76.0  |\n| \u5468\u6885  | \u6570\u5b66  | 05  | 87.0  |\n| \u90d1\u7af9  | \u6570\u5b66  | 07  | 89.0  |\n| \u90d1\u7af9  | \u82f1\u8bed  | 07  | 98.0  |\n+-------+-------+-----+-------+\n11 rows in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    \u5728\u4e0a\u4e00\u9898\u7684\u57fa\u7840\u4e0a\u589e\u52a0score > 70,\u4f7f\u7528where \u6216and\u90fd\u53ef\u4ee5<\/p>\n\n\n\n<\/span>
    32\u3001\u67e5\u8be2\u4e0d\u53ca\u683c\u7684\u8bfe\u7a0b<\/h3>\n\n\n\n
    SELECT cname, a.*\nFROM course\n    JOIN (\n        SELECT score, cid\n        FROM sc\n        WHERE score < 60\n    ) a\n    ON course.cid = a.cid;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c<\/p>\n\n\n\n
    +-------+-------+-----+\n| cname | score | cid |\n+-------+-------+-----+\n| \u8bed\u6587  | 50.0  | 01  |\n| \u6570\u5b66  | 30.0  | 02  |\n| \u82f1\u8bed  | 20.0  | 03  |\n| \u8bed\u6587  | 31.0  | 01  |\n| \u82f1\u8bed  | 34.0  | 03  |\n+-------+-------+-----+\n5 rows in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    \u5148\u4ece\u6210\u7ee9\u8868\u4e2d\u83b7\u53d6\u5230\u4e0d\u53ca\u683c\u7684\u8bfe\u7a0bid\u548c\u6210\u7ee9\uff0c\u7136\u540e\u518d\u548c\u8bfe\u7a0b\u8868\u5173\u8054\uff0c\u5173\u8054\u5b57\u5178\u4e3a\u8bfe\u7a0bid\uff0c\u83b7\u53d6\u5230\u8bfe\u7a0b\u540d\u79f0<\/p>\n\n\n\n<\/span>
    33\u3001\u67e5\u8be2\u8bfe\u7a0b\u7f16\u53f7\u4e3a 01 \u4e14\u8bfe\u7a0b\u6210\u7ee9\u5728 60 \u5206\u4ee5\u4e0a\u7684\u5b66\u751f\u7684\u5b66\u53f7\u548c\u59d3\u540d<\/h3>\n\n\n\n
    SELECT student.sname,c.* FROM student \nJOIN\n(SELECT  b.sid ,b.score,a.cid ,a.cname FROM course as a\nJOIN\nsc as b\nON a.cid = b.cid WHERE a.cid = \"01\" AND b.score > 60) as c  ON student.sid = c.sid;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-------+-----+-------+-----+-------+\n| sname | sid | score | cid | cname |\n+-------+-----+-------+-----+-------+\n| \u8d75\u96f7  | 01  | 80.0  | 01  | \u8bed\u6587  |\n| \u94b1\u7535  | 02  | 70.0  | 01  | \u8bed\u6587  |\n| \u5b59\u98ce  | 03  | 80.0  | 01  | \u8bed\u6587  |\n| \u5468\u6885  | 05  | 76.0  | 01  | \u8bed\u6587  |\n+-------+-----+-------+-----+-------+\n4 rows in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    \u5148\u4ece\u8bfe\u7a0b\u8868\u548c\u6210\u7ee9\u8868\u4e2d\u83b7\u53d6\u5230\u5b66\u751f\u53f7\u3001\u6210\u7ee9\u3001\u8bfe\u7a0b\u53f7\u548c\u8bfe\u7a0b\u540d\u79f0\uff0c\u5173\u8054\u5b57\u6bb5\u4e3a\u8bfe\u7a0b\u53f7\uff0c\u4f5c\u4e3a\u4e34\u65f6\u8868\uff0c\u7136\u540e\u518d\u548c\u5b66\u751f\u8868\u5173\u8054\uff0c\u5173\u8054\u5b57\u6bb5\u4e3a\u5b66\u751f\u53f7\uff0c\u83b7\u53d6\u5230\u5b66\u751f\u540d\u5b57<\/p>\n\n\n\n<\/span>
    34\u3001\u6c42\u6bcf\u95e8\u8bfe\u7a0b\u7684\u5b66\u751f\u4eba\u6570<\/h3>\n\n\n\n
    SELECT course.cname, a.*\nFROM course\n    JOIN (\n        SELECT count(sid), cid\n        FROM sc\n        GROUP BY cid\n    ) a\n    ON course.cid = a.cid;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-------+------------+-----+\n| cname | count(sid) | cid |\n+-------+------------+-----+\n| \u8bed\u6587  |          6 | 01  |\n| \u6570\u5b66  |          6 | 02  |\n| \u82f1\u8bed  |          6 | 03  |\n+-------+------------+-----+\n3 rows in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    \u5148\u4ece\u6210\u7ee9\u8868\u4e2d\u7edf\u8ba1\u51fa\u6bcf\u95e8\u8bfe\u7a0b\u7684\u4eba\u6570\uff0c\u518d\u548c\u8bfe\u7a0b\u8868\u5173\u8054\uff0c\u5173\u8054\u5b57\u6bb5\u4e3a\u8bfe\u7a0b\u53f7\uff0c\u83b7\u53d6\u5230\u8bfe\u7a0b\u540d\u79f0<\/p>\n\n\n\n<\/span>
    35\u3001\u6210\u7ee9\u6ca1\u6709\u91cd\u590d\u7684\u60c5\u51b5\u4e0b\uff0c\u67e5\u8be2\u9009\u4fee\u300c\u5f20\u4e09\u300d\u8001\u5e08\u6240\u6388\u8bfe\u7a0b\u7684\u5b66\u751f\u4e2d\uff0c\u6210\u7ee9\u6700\u9ad8\u7684\u5b66\u751f\u4fe1\u606f\u53ca\u5176\u6210\u7ee9<\/h3>\n\n\n\n
    SELECT student.sname, e.*\nFROM student\n    JOIN (\n        SELECT MAX(d.score), c.*, d.sid\n        FROM sc d\n            JOIN (\n                SELECT a.tid, a.tname, b.cid, b.cname\n                FROM teacher a\n                    JOIN course b ON a.tid = b.tid\n                WHERE a.tname = '\u5f20\u4e09'\n            ) c\n            ON d.cid = c.cid\n    ) e\n    ON student.sid = e.sid;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-------+--------------+-----+-------+-----+-------+-----+\n| sname | MAX(d.score) | tid | tname | cid | cname | sid |\n+-------+--------------+-----+-------+-----+-------+-----+\n| \u8d75\u96f7  | 90.0         | 01  | \u5f20\u4e09  | 02  | \u6570\u5b66  | 01  |\n+-------+--------------+-----+-------+-----+-------+-----+\n1 row in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    \u6559\u5e08\u8868\u548c\u8bfe\u7a0b\u8868\u5173\u8054\uff0c\u83b7\u53d6\u5230\u6559\u5e08\u7f16\u53f7\u3001\u6559\u5e08\u540d\u79f0\u548c\u8bfe\u7a0b\u7f16\u53f7\u548c\u8bfe\u7a0b\u540d\u79f0\uff0c\u5173\u8054\u5b57\u6bb5\u4e3a\u6559\u5e08\u7f16\u53f7
    \u4f5c\u4e3a\u4e34\u65f6\u8868\u518d\u548c\u6210\u7ee9\u8868\u5173\u8054\uff0c\u5173\u8054\u5b57\u6bb5\u4e3a\u8bfe\u7a0b\u7f16\u53f7
    \u4f5c\u4e3a\u4e34\u65f6\u8868\u518d\u548c\u5b66\u751f\u8868\u5173\u8054\uff0c\u5173\u8054\u5b57\u6bb5\u4e3a\u5b66\u751f\u53f7<\/p>\n\n\n\n<\/span>
    36\u3001\u6210\u7ee9\u6709\u91cd\u590d\u7684\u60c5\u51b5\u4e0b\uff0c\u67e5\u8be2\u9009\u4fee\u300c\u5f20\u4e09\u300d\u8001\u5e08\u6240\u6388\u8bfe\u7a0b\u7684\u5b66\u751f\u4e2d\uff0c\u6210\u7ee9\u6700\u9ad8\u7684\u5b66\u751f\u4fe1\u606f\u53ca\u5176\u6210\u7ee9<\/h3>\n\n\n\n
    SELECT student.sname, e.*\nFROM student\n    JOIN (\n        SELECT MAX(d.score), c.*, d.sid\n            , rank() OVER (ORDER BY MAX(d.score)) AS Ranked\n        FROM sc d\n            JOIN (\n                SELECT a.tid, a.tname, b.cid, b.cname\n                FROM teacher a\n                    JOIN course b ON a.tid = b.tid\n                WHERE a.tname = '\u5f20\u4e09'\n            ) c\n            ON d.cid = c.cid\n    ) e\n    ON student.sid = e.sid\nWHERE e.Ranked;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-------+--------------+-----+-------+-----+-------+-----+--------+\n| sname | MAX(d.score) | tid | tname | cid | cname | sid | Ranked |\n+-------+--------------+-----+-------+-----+-------+-----+--------+\n| \u8d75\u96f7  | 90.0         | 01  | \u5f20\u4e09  | 02  | \u6570\u5b66  | 01  |      1 |\n+-------+--------------+-----+-------+-----+-------+-----+--------+\n1 row in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    \u7528rank\u51fd\u6570\uff0c\u7136\u540e\u518d\u5d4c\u5957\u4e00\u4e2aselect\uff0cwhere rank = 1<\/p>\n\n\n\n<\/span>
    37\u3001\u67e5\u8be2\u4e0d\u540c\u8bfe\u7a0b\u6210\u7ee9\u76f8\u540c\u7684\u5b66\u751f\u7684\u5b66\u751f\u7f16\u53f7\u3001\u8bfe\u7a0b\u7f16\u53f7\u3001\u5b66\u751f\u6210\u7ee9<\/h3>\n\n\n\n
    SELECT DISTINCT a.*\nFROM sc a\n    JOIN sc b\n    ON a.score = b.score\n        AND a.cid != b.cid;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-----+-----+-------+\n| sid | cid | score |\n+-----+-----+-------+\n| 02  | 03  | 80.0  |\n| 03  | 02  | 80.0  |\n| 03  | 03  | 80.0  |\n| 01  | 01  | 80.0  |\n| 03  | 01  | 80.0  |\n+-----+-----+-------+\n5 rows in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    sc\u8868\u81ea\u8fde\uff0cdistinct\u53bb\u91cd\uff0ccid \u4e0d\u540c\uff0cscore\u76f8\u540c<\/p>\n\n\n\n<\/span>
    38\u3001\u67e5\u8be2\u6bcf\u95e8\u8bfe\u7a0b\u6210\u7ee9\u6700\u597d\u7684\u524d\u4e24\u540d<\/h3>\n\n\n\n
    -- MySql8.0\u4ee5\u4e0a\nSELECT * FROM\n    (SELECT *,dense_rank()over(PARTITION BY cid ORDER BY score DESC) AS ranked FROM sc ) a\nWHERE a.ranked <=2;\n \n-- MySql5.7\nSELECT *\nFROM sc\nWHERE (\n    SELECT count(*)\n    FROM sc a\n    WHERE sc.CId = a.CId\n        AND sc.score < a.score\n) < 2\nORDER BY CId ASC, sc.score DESC;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-----+-----+-------+--------+\n| sid | cid | score | ranked |\n+-----+-----+-------+--------+\n| 01  | 01  | 80.0  |      1 |\n| 03  | 01  | 80.0  |      1 |\n| 05  | 01  | 76.0  |      2 |\n| 01  | 02  | 90.0  |      1 |\n| 07  | 02  | 89.0  |      2 |\n| 01  | 03  | 99.0  |      1 |\n| 07  | 03  | 98.0  |      2 |\n+-----+-----+-------+--------+\n7 rows in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    \u6211\u8ba4\u4e3a\u6700\u597d\u7684\u524d\u4e24\u540d\u662f\u6392\u540d\u7684\u524d2\u4e2a\uff0c\u5373\u7b2c\u4e00\u4e2a\u6392\u540d1 \u548c\u7b2c\u4e8c\u4e2a\u6392\u540d2\uff0c\u5982\u679c\u6709\u4e24\u4e2a\u5e76\u5217\u7b2c\u4e00\uff0c\u4e00\u4e2a\u7b2c\u4e8c\uff0c\u90a3\u4e48\u524d\u4e24\u540d\u5e94\u8be5\u662f3\u4e2a\u4eba\uff0c\u7528dense_rank\uff0c\u6392\u540d\u4e0d\u8df3\u8fc7\uff1b\u5982\u679c\u8bf4\u662f\u6700\u597d\u7684\u524d\u4e24\u4e2a\u4eba\uff0c\u5c31\u7528rank\uff0c\u6392\u540d\u8df3\u8fc7<\/p>\n\n\n\n<\/span>
    39\u3001\u7edf\u8ba1\u6bcf\u95e8\u8bfe\u7a0b\u7684\u5b66\u751f\u9009\u4fee\u4eba\u6570\uff08\u8d85\u8fc7 5 \u4eba\u7684\u8bfe\u7a0b\u624d\u7edf\u8ba1\uff09<\/h3>\n\n\n\n
    SELECT  course.cname,a.* FROM course \nJOIN\n(SELECT  cid,COUNT(sid) as \u9009\u4fee\u4eba\u6570 FROM sc GROUP BY cid HAVING COUNT(sid) >5) as a\nON course.cid = a.cid;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-------+-----+----------+\n| cname | cid | \u9009\u4fee\u4eba\u6570 |\n+-------+-----+----------+\n| \u8bed\u6587  | 01  |        6 |\n| \u6570\u5b66  | 02  |        6 |\n| \u82f1\u8bed  | 03  |        6 |\n+-------+-----+----------+\n3 rows in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    group by\uff0chaving\u805a\u5408<\/p>\n\n\n\n<\/span>
    40\u3001\u68c0\u7d22\u81f3\u5c11\u9009\u4fee\u4e24\u95e8\u8bfe\u7a0b\u7684\u5b66\u751f\u5b66\u53f7<\/h3>\n\n\n\n
    SELECT student.sname, a.*\nFROM student\n    JOIN (\n        SELECT sid, COUNT(cid) AS \u9009\u4fee\u8bfe\u7a0b\u603b\u6570\n        FROM sc\n        GROUP BY sid\n        HAVING \u9009\u4fee\u8bfe\u7a0b\u603b\u6570 >= 2\n    ) a\n    ON student.sid = a.sid;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-------+-----+--------------+\n| sname | sid | \u9009\u4fee\u8bfe\u7a0b\u603b\u6570 |\n+-------+-----+--------------+\n| \u8d75\u96f7  | 01  |            3 |\n| \u94b1\u7535  | 02  |            3 |\n| \u5b59\u98ce  | 03  |            3 |\n| \u674e\u4e91  | 04  |            3 |\n| \u5468\u6885  | 05  |            2 |\n| \u5434\u5170  | 06  |            2 |\n| \u90d1\u7af9  | 07  |            2 |\n+-------+-----+--------------+\n7 rows in set\n<\/code><\/pre>\n\n\n\n<\/span>
    41\u3001\u67e5\u8be2\u9009\u4fee\u4e86\u5168\u90e8\u8bfe\u7a0b\u7684\u5b66\u751f\u4fe1\u606f<\/h3>\n\n\n\n
    SELECT student.*, c.`\u9009\u4fee\u8bfe\u7a0b\u603b\u6570`\nFROM student\n    JOIN (\n        SELECT b.sid, COUNT(a.cid) AS \u9009\u4fee\u8bfe\u7a0b\u603b\u6570\n        FROM course a\n            JOIN sc b ON a.cid = b.cid\n        GROUP BY b.sid\n        HAVING COUNT(a.cid) = (\n            SELECT COUNT(cid)\n            FROM course\n        )\n    ) c\n    ON student.sid = c.sid;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-----+-------+---------------------+------+--------------+\n| sid | sname | sage                | ssex | \u9009\u4fee\u8bfe\u7a0b\u603b\u6570 |\n+-----+-------+---------------------+------+--------------+\n| 01  | \u8d75\u96f7  | 1990-01-01 00:00:00 | \u7537   |            3 |\n| 02  | \u94b1\u7535  | 1990-12-21 00:00:00 | \u7537   |            3 |\n| 03  | \u5b59\u98ce  | 1990-05-20 00:00:00 | \u7537   |            3 |\n| 04  | \u674e\u4e91  | 1990-08-06 00:00:00 | \u7537   |            3 |\n+-----+-------+---------------------+------+--------------+\n4 rows in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    \u4ece\u8bfe\u7a0b\u8868\u4e2d\u67e5\u8be2\u51fa\u603b\u7684\u8bfe\u7a0b\u6570\uff0c\u4f5c\u4e3a\u540e\u9762\u5b50\u67e5\u8be2\u7684\u6761\u4ef6
    \u4ece\u6210\u7ee9\u8868\u4e2d\u67e5\u8be2\u51fa\u9009\u4fee\u4e86\u5168\u90e8\u8bfe\u7a0b\u6570\u7684\u7684\u5b66\u751f\u53f7\u548c\u9009\u4fee\u7684\u8bfe\u7a0b\u603b\u6570
    \u4f5c\u4e3a\u4e34\u65f6\u8868\u548c\u5b66\u751f\u8868\u5173\u8054\uff0c\u5173\u8054\u5b57\u6bb5\u4e3a\u5b66\u751f\u53f7\uff0c\u83b7\u53d6\u5230\u5168\u90e8\u7684\u5b66\u751f\u4fe1\u606f<\/p>\n\n\n\n<\/span>
    42\u3001\u67e5\u8be2\u5404\u5b66\u751f\u7684\u5e74\u9f84\uff0c\u53ea\u6309\u5e74\u4efd\u6765\u7b97<\/h3>\n\n\n\n
    SELECT  sname,YEAR(NOW()) - YEAR(sage) as \u5e74\u9f84 FROM student;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-------+------+\n| sname | \u5e74\u9f84 |\n+-------+------+\n| \u8d75\u96f7  |   31 |\n| \u94b1\u7535  |   31 |\n| \u5b59\u98ce  |   31 |\n| \u674e\u4e91  |   31 |\n| \u5468\u6885  |   30 |\n| \u5434\u5170  |   29 |\n| \u90d1\u7af9  |   32 |\n| \u738b\u83ca  |   31 |\n+-------+------+\n8 rows in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    \u4f7f\u7528year\u51fd\u6570<\/p>\n\n\n\n<\/span>
    43\u3001\u6309\u7167\u51fa\u751f\u65e5\u671f\u6765\u7b97\uff0c\u5f53\u524d\u6708\u65e5 < \u51fa\u751f\u5e74\u6708\u7684\u6708\u65e5\u5219\uff0c\u5e74\u9f84\u51cf\u4e00<\/h3>\n\n\n\n
    SELECT sname\n    , CASE \n        WHEN DATE_FORMAT(NOW(), '%m-%d') - DATE_FORMAT(sage, '%m-%d') < 0 THEN YEAR(NOW()) - YEAR(sage) - 1\n        ELSE YEAR(NOW()) - YEAR(sage)\n    END AS age\nFROM student;<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a<\/p>\n\n\n\n
    +-------+-----+\n| sname | age |\n+-------+-----+\n| \u8d75\u96f7  |  31 |\n| \u94b1\u7535  |  30 |\n| \u5b59\u98ce  |  31 |\n| \u674e\u4e91  |  31 |\n| \u5468\u6885  |  29 |\n| \u5434\u5170  |  29 |\n| \u90d1\u7af9  |  32 |\n| \u738b\u83ca  |  31 |\n+-------+-----+\n8 rows in set<\/code><\/pre>\n\n\n\n
    \u89e3\u6790\uff1a
    \u6709\u4e24\u79cd\u65b9\u6cd5\uff0c\u4e00\u79cd\u662f\u5229\u7528date_format\u76f4\u63a5\u622a\u53d6\u65f6\u95f4\u7c7b\u578b\u4e2d\u7684\u6708\u65e5\uff0c\u76f4\u63a5\u6bd4\u5927\u5c0f
    \u53e6\u5916\u4e00\u79cd\u662f\u7528month()\u5148\u6bd4\u5927\u5c0f\uff0c\u76f8\u7b49\u518d\u7528day()\u6bd4\u5927\u5c0f<\/p>\n\n\n\n<\/span>
    44\u3001\u67e5\u8be2\u672c\u5468\u8fc7\u751f\u65e5\u7684\u5b66\u751f<\/h3>\n\n\n\n
    SELECT sname FROM student  WHERE week(NOW()) = WEEK(sage);<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a
    Empty set<\/p>\n\n\n\n
    \u89e3\u6790\uff1a
    week() \u8fd4\u56de\u7684\u662f\u4eca\u5e74\u7684\u7b2c\u51e0\u5468\uff0c\u5373\u5982\u679c\u672c\u5468\u8fc7\u751f\uff0c\u8fd4\u56de\u6570\u5b57\u76f8\u7b49<\/p>\n\n\n\n<\/span>
    45\u3001\u67e5\u8be2\u4e0b\u5468\u8fc7\u751f\u65e5\u7684\u5b66\u751f<\/h3>\n\n\n\n
    SELECT sname FROM student  WHERE week(NOW()) + 1 = WEEK(sage);<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a
    Empty set<\/p>\n\n\n\n
    \u89e3\u6790\uff1a
    \u52a0\u4e00\u5c31\u884c<\/p>\n\n\n\n<\/span>
    46\u3001\u67e5\u8be2\u672c\u6708\u8fc7\u751f\u65e5\u7684\u5b66\u751f<\/h3>\n\n\n\n
    SELECT sname FROM student  WHERE month(NOW()) = month(sage);<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a
    Empty set<\/p>\n\n\n\n
    \u89e3\u6790\uff1a
    \u4f7f\u7528month\u51fd\u6570<\/p>\n\n\n\n<\/span>
    47\u3001\u67e5\u8be2\u4e0b\u6708\u8fc7\u751f\u65e5\u7684\u5b66\u751f<\/h3>\n\n\n\n
    SELECT sname FROM student  WHERE month(NOW()) + 1 = month(sage);<\/code><\/pre>\n\n\n\n
    \u7ed3\u679c\uff1a
    Empty set<\/p>\n\n\n\n<\/span>
    48\u3001\u67e5\u8be2\u4e0b\u5468\u8fc7\u751f\u65e5\u7684\u5b66\u751f<\/h3>\n\n\n\n
    SELECT st.*\nFROM student st\nWHERE week(now()) + 1 = week(date_format(st.s_birth, \u2018 % Y % m % d\u2019))\n<\/code><\/pre>\n\n\n\n<\/span>
    49\u3001\u67e5\u8be2\u672c\u6708\u8fc7\u751f\u65e5\u7684\u5b66\u751f<\/h3>\n\n\n\n
    SELECT st.*\nFROM student st\nWHERE month(now()) = month(date_format(st.s_birth, \u2018 % Y % m % d\u2019))\n<\/code><\/pre>\n\n\n\n<\/span>
    50\u3001\u67e5\u8be2\u4e0b\u6708\u8fc7\u751f\u65e5\u7684\u5b66\u751f<\/h3>\n\n\n\n
    SELECT st.*\nFROM student st\nWHERE month(timestampadd(month, 1, now())) = month(date_format(st.s_birth, \u2018 % Y % m % d\u2019));\n \n-- \u6216\u8005\n \nSELECT st.*\nFROM student st\nWHERE (month(now()) + 1) % 12 = month(date_format(st.s_birth, \u2018 % Y % m % d\u2019));<\/code><\/pre>\n\n\n\n
    \u6ce8\u610f:\u5f53\u5f53\u524d\u6708\u4e3a12\u65f6,\u7528month(now())+1\u4e3a13\u800c\u4e0d\u662f1,\u53ef\u7528timestampadd()\u51fd\u6570\u6216mod\u53d6\u6a21<\/p>\n\n\n\n<\/span>
    \u53c2\u8003\u6587\u7ae0<\/h2>\n\n\n\n
    MYSQL\u7a97\u53e3\u51fd\u6570\uff1ahttps:\/\/blog.csdn.net\/Annabel_CM\/article\/details\/125840831<\/a><\/p>\n\n\n\n
    MYSQL\u57fa\u7840\u5e38\u89c1\u5e38\u7528\u8bed\u53e5200\u6761\uff1ahttps:\/\/blog.csdn.net\/c361604199\/article\/details\/79479398<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
    \u4e00\u3001\u73af\u5883\u51c6\u5907 \u5efa\u8868\u51714\u5f20\u8868\uff0c\u5206\u522b\u5bf9\u5e94\u5b66\u751f\u4fe1\u606f\uff08Student\uff09\u3001\u8bfe\u7a0b\u4fe1\u606f\uff08Course\uff09\u3001\u6559\u5e08\u4fe1\u606f\uff08Teach […]<\/p>\n","protected":false},"author":1,"featured_media":488,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-141","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-hd"],"_links":{"self":[{"href":"https:\/\/www.hyw.life\/index.php?rest_route=\/wp\/v2\/posts\/141","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.hyw.life\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.hyw.life\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.hyw.life\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.hyw.life\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=141"}],"version-history":[{"count":17,"href":"https:\/\/www.hyw.life\/index.php?rest_route=\/wp\/v2\/posts\/141\/revisions"}],"predecessor-version":[{"id":172,"href":"https:\/\/www.hyw.life\/index.php?rest_route=\/wp\/v2\/posts\/141\/revisions\/172"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.hyw.life\/index.php?rest_route=\/wp\/v2\/media\/488"}],"wp:attachment":[{"href":"https:\/\/www.hyw.life\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=141"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.hyw.life\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=141"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.hyw.life\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=141"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}