{"id":200123,"date":"2025-05-06T08:53:46","date_gmt":"2025-05-06T00:53:46","guid":{"rendered":"https:\/\/server.hk\/cnblog\/200123\/"},"modified":"2025-05-06T08:53:46","modified_gmt":"2025-05-06T00:53:46","slug":"mysql-%e5%9f%ba%e6%9c%ac%e7%b2%be%e9%80%89%e7%bb%83%e4%b9%a0%e9%a2%98%e5%8f%8a%e7%ad%94%e6%a1%88","status":"publish","type":"post","link":"https:\/\/server.hk\/cnblog\/200123\/","title":{"rendered":"MySQL \u57fa\u672c\u7cbe\u9009\u7ec3\u4e60\u9898\u53ca\u7b54\u6848"},"content":{"rendered":"<p><b><\/b> <\/p>\n<h1>MySQL \u57fa\u672c\u7cbe\u9009\u7ec3\u4e60\u9898\u53ca\u7b54\u6848<\/h1>\n<p>\u4eca\u65e5\u4e0d\u80af\u57cb\u5934\uff0c\u660e\u65e5\u4f55\u4ee5\u62ac\u5934\uff01\u6bcf\u65e5\u4e00\u53e5\u52aa\u529b\u81ea\u5df1\u7684\u8bdd\u54c8\u54c8~\u54c8\u55bd\uff0c\u4eca\u5929\u6211\u5c06\u7ed9\u5927\u5bb6\u5e26\u6765\u4e00\u7bc7\uff0c\u4e3b\u8981\u5185\u5bb9\u662f\u8bb2\u89e3\u7b49\u7b49\uff0c\u611f\u5174\u8da3\u7684\u670b\u53cb\u53ef\u4ee5\u6536\u85cf\u6216\u8005\u6709\u66f4\u597d\u7684\u5efa\u8bae\u5728\u8bc4\u8bba\u63d0\u51fa\uff0c\u6211\u90fd\u4f1a\u8ba4\u771f\u770b\u7684\uff01\u5927\u5bb6\u4e00\u8d77\u8fdb\u6b65\uff0c\u4e00\u8d77\u5b66\u4e60\uff01<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.17golang.com\/uploads\/20240805\/172284231866b07cce22f94.jpg\" class=\"aligncenter\"><\/p>\n<h3> \u8868\u540d\u548c\u5b57\u6bb5\uff08mysql\uff09 <\/h3>\n<ol>\n<li>\n<p>\u5b66\u751f\u684c<br \/> \u5b66\u751f\uff08s_id\uff0cs_name\uff0cs_birth\uff0cs_sex\uff09<br \/> \u5b66\u751f\u8bc1\u3001\u5b66\u751f\u59d3\u540d\u3001\u51fa\u751f\u65e5\u671f\u3001\u5b66\u751f\u6027\u522b<\/p>\n<\/li>\n<li>\n<p>\u8bfe\u7a0b\u8868<\/p>\n<p> \u8bfe\u7a0b(c_id, c_name, t_id)<br \/> \u8bfe\u7a0bid\u3001\u8bfe\u7a0b\u540d\u79f0\u3001\u6559\u5e08id<\/p>\n<\/li>\n<li>\n<p>\u8001\u5e08\u684c<\/p>\n<p> \u8001\u5e08\uff08t_id\uff0ct_name\uff09<br \/> \u8001\u5e08id\u3001\u8001\u5e08\u59d3\u540d<\/p>\n<\/li>\n<li>\n<p>\u5206\u6570\u8868<\/p>\n<p> \u5206\u6570(s_id, c_id, s_score)<br \/> \u5b66\u751fid\u3001\u8bfe\u7a0bid\u3001\u5206\u6570<\/p>\n<\/li>\n<\/ol>\n<h3> \u6d4b\u8bd5\u6570\u636e &#8211; \u521b\u5efa\u8868 <\/h3>\n<ol>\n<li>\u5b66\u751f\u684c <\/li>\n<\/ol>\n<pre>create table  `student`(  \n`s_id`  varchar(20),  \n`s_name`  varchar(20) not null default '',  \n`s_birth`  varchar(20) not null default '',  \n`s_sex`  varchar(10) not null default '',  \nprimary key(`s_id`)  \n);\n<\/pre>\n<ol>\n<li>\u8bfe\u7a0b\u8868 <\/li>\n<\/ol>\n<pre>create table  `course`(  \n`c_id`  varchar(20),  \n`c_name`  varchar(20) not null default '',  \n`t_id`  varchar(20) not null,  \nprimary key(`c_id`)  \n);\n<\/pre>\n<ol>\n<li>\u8001\u5e08\u684c <\/li>\n<\/ol>\n<pre>create table  `teacher`(  \n`t_id`  varchar(20),  \n`t_name`  varchar(20) not null default '',  \nprimary key(`t_id`)  \n);\n<\/pre>\n<ol>\n<li>\u5206\u6570\u8868 <\/li>\n<\/ol>\n<pre>create table  `score`(  \n`s_id`  varchar(20),  \n`c_id`  varchar(20),  \n`s_score`  int(3),  \nprimary key(`s_id`,`c_id`)  \n);\n<\/pre>\n<ol>\n<li>\u5c06\u6d4b\u8bd5\u6570\u636e\u63d2\u5165student\u8868 <\/li>\n<\/ol>\n<pre>insert into student values('01', 'john doe', '1990-01-01', 'male');  \ninsert into student values('02', 'jane smith', '1990-12-21', 'male');  \ninsert into student values('03', 'michael brown', '1990-05-20', 'male');  \ninsert into student values('04', 'emily davis', '1990-08-06', 'male');  \ninsert into student values('05', 'lucy johnson', '1991-12-01', 'female');  \ninsert into student values('06', 'sophia williams', '1992-03-01', 'female');  \ninsert into student values('07', 'olivia taylor', '1989-07-01', 'female');  \ninsert into student values('08', 'victoria king', '1990-01-20', 'female');\n<\/pre>\n<ol>\n<li>\u5c06\u6d4b\u8bd5\u6570\u636e\u63d2\u5165\u8bfe\u7a0b\u8868 <\/li>\n<\/ol>\n<pre>insert into course values('01', 'literature', '02');  \ninsert into course values('02', 'mathematics', '01');  \ninsert into course values('03', 'english', '03');\n<\/pre>\n<ol>\n<li>\u5c06\u6d4b\u8bd5\u6570\u636e\u63d2\u5165\u6559\u5e08\u8868 <\/li>\n<\/ol>\n<pre>insert into teacher values('01', 'andrew');  \ninsert into teacher values('02', 'bethany');  \ninsert into teacher values('03', 'charlie');\n<\/pre>\n<ol>\n<li>\u6210\u7ee9\u5355\u6d4b\u8bd5\u6570\u636e <\/li>\n<\/ol>\n<pre>insert into score values('01' , '01' , 80);\ninsert into score values('01' , '02' , 90);\ninsert into score values('01' , '03' , 99);\ninsert into score values('02' , '01' , 70);\ninsert into score values('02' , '02' , 60);\ninsert into score values('02' , '03' , 80);\ninsert into score values('03' , '01' , 80);\ninsert into score values('03' , '02' , 80);\ninsert into score values('03' , '03' , 80);\ninsert into score values('04' , '01' , 50);\ninsert into score values('04' , '02' , 30);\ninsert into score values('04' , '03' , 20);\ninsert into score values('05' , '01' , 76);\ninsert into score values('05' , '02' , 87);\ninsert into score values('06' , '01' , 31);\ninsert into score values('06' , '03' , 34);\ninsert into score values('07' , '02' , 89);\ninsert into score values('07' , '03' , 98);\n<\/pre>\n<h3> \u7ec3\u4e60\u9898\u548c sql \u8bed\u53e5 <\/h3>\n<ol>\n<li>\u68c0\u7d22\u201c01\u201d\u8bfe\u7a0b\u6210\u7ee9\u9ad8\u4e8e\u201c02\u201d\u8bfe\u7a0b\u6210\u7ee9\u7684\u5b66\u751f\u7684\u4fe1\u606f\u548c\u8bfe\u7a0b\u6210\u7ee9 <\/li>\n<\/ol>\n<pre>select a.*, b.s_score as '01_score', c.s_score as '02_score'  \nfrom student a  \njoin score b on a.s_id = b.s_id and b.c_id = '01'  \nleft join score c on a.s_id = c.s_id and c.c_id = '02'  \nwhere b.s_score &gt; coalesce(c.s_score, 0); -- using coalesce instead of or c.c_id = null  \n\n-- alternatively  \nselect a.*, b.s_score as '01_score', c.s_score as '02_score'  \nfrom student a, score b, score c  \nwhere a.s_id = b.s_id  \nand a.s_id = c.s_id  \nand b.c_id = '01'  \nand c.c_id = '02'  \nand b.s_score &gt; c.s_score;\n<\/pre>\n<ol>\n<li>\u68c0\u7d22\u201c01\u201d\u8bfe\u7a0b\u6210\u7ee9\u4f4e\u4e8e\u201c02\u201d\u8bfe\u7a0b\u6210\u7ee9\u7684\u5b66\u751f\u7684\u4fe1\u606f\u548c\u8bfe\u7a0b\u6210\u7ee9 <\/li>\n<\/ol>\n<pre>select a.*, b.s_score as '01_score', c.s_score as '02_score'  \nfrom student a  \nleft join score b on a.s_id = b.s_id and b.c_id = '01'  \njoin score c on a.s_id = c.s_id and c.c_id = '02'  \nwhere coalesce(b.s_score, 0) &lt; c.s_score; -- using coalesce for clarity\n\n<\/pre>\n<ol>\n<li>\u68c0\u7d22\u5e73\u5747\u520660\u5206\u4ee5\u4e0a\u5b66\u751f\u7684\u5b66\u53f7\u3001\u59d3\u540d\u3001\u5e73\u5747\u5206 <\/li>\n<\/ol>\n<pre>select b.s_id, b.s_name, round(avg(a.s_score), 2) as avg_score  \nfrom student b  \njoin score a on b.s_id = a.s_id  \ngroup by b.s_id, b.s_name  \nhaving avg(a.s_score) &gt;= 60;\n<\/pre>\n<ol>\n<li>\u68c0\u7d22\u5e73\u5747\u5206\u4f4e\u4e8e60\u5206\u7684\u5b66\u751f\uff08\u5305\u62ec\u6ca1\u6709\u5206\u6570\u7684\u5b66\u751f\uff09\u7684\u5b66\u53f7\u3001\u59d3\u540d\u3001\u5e73\u5747\u5206 <\/li>\n<\/ol>\n<pre>select b.s_id, b.s_name, round(avg(a.s_score), 2) as avg_score  \nfrom student b  \nleft join score a on b.s_id = a.s_id  \ngroup by b.s_id, b.s_name  \nhaving avg(a.s_score) &lt; 60  \nunion  \nselect a.s_id, a.s_name, 0 as avg_score  \nfrom student a  \nwhere a.s_id not in (select distinct s_id from score);\n<\/pre>\n<ol>\n<li>\u68c0\u7d22\u5b66\u751f id\u3001\u59d3\u540d\u3001\u6240\u9009\u8bfe\u7a0b\u603b\u6570\u4ee5\u53ca\u6240\u6709\u8bfe\u7a0b\u7684\u603b\u6210\u7ee9 <\/li>\n<\/ol>\n<pre>select a.s_id, a.s_name, count(b.c_id) as sum_course, sum(b.s_score) as sum_score  \nfrom student a  \nleft join score b on a.s_id = b.s_id  \ngroup by a.s_id, a.s_name;\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u59d3\u201csmith\u201d\u7684\u8001\u5e08\u6570\u91cf <\/li>\n<\/ol>\n<pre>select  count(t_id) from teacher where t_name like  'smith%';\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u4e0a\u8fc7\u201cjohn doe\u201d\u8001\u5e08\u6388\u8bfe\u7684\u5b66\u751f\u4fe1\u606f <\/li>\n<\/ol>\n<pre>select a.*  \nfrom student a  \njoin score b on a.s_id = b.s_id  \nwhere b.c_id in (  \n    select c_id from course  \n    where t_id = (  \n        select t_id from teacher  \n        where t_name = 'john doe'  \n    )  \n);\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u672a\u4e0a\u8fc7\u201cjohn doe\u201d\u8001\u5e08\u6388\u8bfe\u7684\u5b66\u751f\u4fe1\u606f <\/li>\n<\/ol>\n<pre>select *  \nfrom student c  \nwhere c.s_id not in (  \n    select a.s_id  \n    from student a  \n    join score b on a.s_id = b.s_id  \n    where b.c_id in (  \n        select a.c_id  \n        from course a  \n        join teacher b on a.t_id = b.t_id  \n        where t_name = 'john doe'  \n    )  \n);\n<\/pre>\n<ol>\n<li>\u67e5\u8be2id\u4e3a\u201cmath101\u201d\u548c\u201cscience101\u201d\u7684\u4e24\u95e8\u8bfe\u7a0b\u7684\u5b66\u751f\u4fe1\u606f <\/li>\n<\/ol>\n<pre>select a.*  \nfrom student a, score b, score c  \nwhere a.s_id = b.s_id  \nand a.s_id = c.s_id  \nand b.c_id = 'math101'  \nand c.c_id = 'science101';\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u5df2\u9009\u4fee\u8fc7id\u4e3a\u201cmath101\u201d\u7684\u8bfe\u7a0b\u4f46\u672a\u9009\u4fee\u8fc7id\u4e3a\u201cscience101\u201d\u7684\u8bfe\u7a0b\u7684\u5b66\u751f\u4fe1\u606f <\/li>\n<\/ol>\n<pre>select a.*\nfrom student a\nwhere a.s_id in (select s_id from score where c_id = 'math101')\nand a.s_id not in (select s_id from score where c_id = 'science101');\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u672a\u4fee\u5b8c\u6240\u6709\u8bfe\u7a0b\u7684\u5b66\u751f\u4fe1\u606f <\/li>\n<\/ol>\n<pre>-- @wendiepei's approach\nselect s.*\nfrom student s\nleft join score s1 on s1.s_id = s.s_id\ngroup by s.s_id\nhaving count(s1.c_id) &lt; (select count(*) from course);\n-- @k1051785839's approach\nselect *  \nfrom student  \nwhere s_id not in (  \n    select s_id   \n    from score t1    \n    group by s_id   \n    having count(*) = (select count(distinct c_id) from course)  \n);\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u5b66\u8fc7\u81f3\u5c11\u4e00\u95e8\u4e0e\u5b66\u53f7\u201801\u2019\u5171\u540c\u8bfe\u7a0b\u7684\u5b66\u751f\u4fe1\u606f <\/li>\n<\/ol>\n<pre>select *   \nfrom student   \nwhere s_id in (  \n    select distinct a.s_id   \n    from score a   \n    where a.c_id in (  \n        select c_id   \n        from score   \n        where s_id = '01'  \n    )  \n);\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u4e0e\u5b66\u53f7\u201801\u2019\u9009\u4fee\u8fc7\u5b8c\u5168\u76f8\u540c\u8bfe\u7a0b\u7684\u5b66\u751f\u4fe1\u606f <\/li>\n<\/ol>\n<pre>select\n t3.*\nfrom\n (\n  select\n   s_id,\n   group_concat(c_id order by c_id) group1\n  from\n   score\n  where\n   s_id &amp;lt;&gt; '01'\n  group by\n   s_id\n ) t1\ninner join (\n select\n  group_concat(c_id order by c_id) group2\n from\n  score\n where\n  s_id = '01'\n group by\n  s_id\n) t2 on t1.group1 = t2.group2\ninner join student t3 on t1.s_id = t3.s_id\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u6ca1\u6709\u4e0a\u8fc7\u201ctom\u201d\u8001\u5e08\u6240\u6559\u8bfe\u7a0b\u7684\u5b66\u751f\u59d3\u540d <\/li>\n<\/ol>\n<pre>select a.s_name from student a where a.s_id not in (\n    select s_id from score where c_id = \n                (select c_id from course where t_id =(\n                    select t_id from teacher where t_name = 'tom')));\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u4e24\u95e8\u53ca\u4e24\u95e8\u4ee5\u4e0a\u8bfe\u7a0b\u4e0d\u53ca\u683c\u7684\u5b66\u751f\u7684\u5b66\u53f7\u3001\u59d3\u540d\u3001\u5e73\u5747\u5206 <\/li>\n<\/ol>\n<pre>select a.s_id, a.s_name, round(avg(b.s_score), 2) as average_score  \nfrom student a  \nleft join score b on a.s_id = b.s_id  \nwhere a.s_id in (  \n    select s_id  \n    from score  \n    where s_score &lt; 60  \n    group by s_id  \n    having count(*) &gt;= 2  \n)  \ngroup by a.s_id, a.s_name;\n<\/pre>\n<ol>\n<li>\u68c0\u7d22\u8bfe\u7a0b\u201c01\u201d\u5f97\u5206\u4f4e\u4e8e 60 \u5206\u7684\u5b66\u751f\u4fe1\u606f\uff0c\u6309\u5206\u6570\u964d\u5e8f\u6392\u5217\u3002 <\/li>\n<\/ol>\n<pre>select a.*, b.c_id, b.s_score  \nfrom student a  \njoin score b on a.s_id = b.s_id  \nwhere b.c_id = '01' and b.s_score &lt; 60  \norder by b.s_score desc;\n<\/pre>\n<ol>\n<li>\u663e\u793a\u6240\u6709\u8bfe\u7a0b\u7684\u6210\u7ee9\u4ee5\u53ca\u6bcf\u4e2a\u5b66\u751f\u7684\u5e73\u5747\u6210\u7ee9\uff0c\u6309\u5e73\u5747\u6210\u7ee9\u4ece\u9ad8\u5230\u4f4e\u6392\u5e8f\u3002 <\/li>\n<\/ol>\n<pre>select   \n    a.s_id,  \n    max(case when c_id = '01' then s_score end) as chinese,  \n    max(case when c_id = '02' then s_score end) as math,  \n    max(case when c_id = '03' then s_score end) as english,  \n    round(avg(s_score), 2) as average_score  \nfrom score a  \ngroup by a.s_id  \norder by average_score desc;\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u6bcf\u95e8\u8bfe\u7a0b\u7684\u6700\u9ad8\u5206\u3001\u6700\u4f4e\u5206\u3001\u5e73\u5747\u5206\u3001\u53ca\u683c\u7387\u3001\u4e2d\u7b49\u7387\u3001\u826f\u597d\u7387\u3001\u4f18\u79c0\u7387\u3002\u6309\u4ee5\u4e0b\u683c\u5f0f\u663e\u793a\uff1a\u8bfe\u7a0b id\u3001\u8bfe\u7a0b\u540d\u79f0\u3001\u6700\u9ad8\u5206\u3001\u6700\u4f4e\u5206\u3001\u5e73\u5747\u5206\u3001\u901a\u8fc7\u7387\u3001\u4e2d\u7b49\u7387\u3001\u826f\u597d\u7387\u3001\u4f18\u79c0\u7387\u3002 &#8212; \u901a\u8fc7\u4e3a &gt;=60\uff0c\u4e2d\u7b49\u4e3a 70-80\uff0c\u826f\u597d\u4e3a 80-90\uff0c\u4f18\u79c0\u4e3a &gt;=90 <\/li>\n<\/ol>\n<pre>select   \n    a.c_id,  \n    b.c_name,  \n    max(s_score) as highestscore,  \n    min(s_score) as lowestscore,  \n    round(avg(s_score), 2) as averagescore,  \n    round(100 * (sum(case when s_score &gt;= 60 then 1 else 0 end) \/ count(s_score)), 2) as passrate,  \n    round(100 * (sum(case when s_score between 70 and 80 then 1 else 0 end) \/ count(s_score)), 2) as mediumrate,  \n    round(100 * (sum(case when s_score between 80 and 90 then 1 else 0 end) \/ count(s_score)), 2) as goodrate,  \n    round(100 * (sum(case when s_score &gt;= 90 then 1 else 0 end) \/ count(s_score)), 2) as excellentrate  \nfrom   \n    score a   \nleft join   \n    course b on a.c_id = b.c_id   \ngroup by   \n    a.c_id, b.c_name;\n<\/pre>\n<ol>\n<li>\u6309\u8bfe\u7a0b\u5bf9\u5206\u6570\u8fdb\u884c\u6392\u5e8f\u5e76\u663e\u793a\u6392\u540d\u3002 mysql\u6ca1\u6709\u5185\u7f6e\u7684rank()\u51fd\u6570\uff0c\u56e0\u6b64\u6211\u4eec\u5c06\u4f7f\u7528\u53d8\u91cf\u6765\u6a21\u62df\u5b83\u3002 <\/li>\n<\/ol>\n<pre>select   \n    a.s_id,  \n    a.c_id,  \n    @rank := if(@prev_score = a.s_score, @rank, @rank + 1) as rank_without_ties,  \n    @prev_score := a.s_score as score  \nfrom   \n    (select s_id, c_id, s_score from score order by c_id, s_score desc) a,  \n    (select @rank := 0, @prev_score := null) r  \norder by   \n    a.c_id, a.rank_without_ties;\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u6bcf\u4e2a\u5b66\u751f\u7684\u603b\u6210\u7ee9\u5e76\u8fdb\u884c\u6392\u540d <\/li>\n<\/ol>\n<pre>select   \n    a.s_id,  \n    @rank := if(@prev_score = a.sum_score, @rank, @rank + 1) as rank,  \n    @prev_score := a.sum_score as total_score  \nfrom   \n    (select s_id, sum(s_score) as sum_score from score group by s_id order by sum_score desc) a,  \n    (select @rank := 0, @prev_score := null) r  \norder by   \n    total_score desc;\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u4e0d\u540c\u8001\u5e08\u8bb2\u6388\u7684\u4e0d\u540c\u8bfe\u7a0b\u7684\u5e73\u5747\u5206\uff0c\u4ece\u9ad8\u5230\u4f4e\u6392\u5e8f <\/li>\n<\/ol>\n<pre>select   \n    a.t_id,  \n    c.t_name,  \n    a.c_id,  \n    round(avg(s_score), 2) as avg_score   \nfrom   \n    course a  \nleft join   \n    score b on a.c_id = b.c_id   \nleft join   \n    teacher c on a.t_id = c.t_id  \ngroup by   \n    a.c_id, a.t_id, c.t_name   \norder by   \n    avg_score desc;\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u6240\u6709\u8bfe\u7a0b\u6392\u540d\u7b2c\u4e8c\u3001\u7b2c\u4e09\u540d\u7684\u5b66\u751f\u4fe1\u606f\u53ca\u5176\u6210\u7ee9 <\/li>\n<\/ol>\n<pre>(select   \n    d.*,  \n    c.ranking,  \n    c.s_score,  \n    c.c_id  \nfrom   \n    (select   \n        s_id,   \n        s_score,   \n        c_id,   \n        @rank := if(@prev_cid = c_id, @rank + 1, 1) as ranking,  \n        @prev_cid := c_id  \n    from   \n        score,   \n        (select @rank := 0, @prev_cid := null) as var_init  \n    where   \n        c_id = '01'  \n    order by   \n        c_id, s_score desc  \n    ) c  \nleft join   \n    student d on c.s_id = d.s_id  \nwhere   \n    c.ranking between 2 and 3  \n)  \nunion  \n(select   \n    d.*,  \n    c.ranking,  \n    c.s_score,  \n    c.c_id  \nfrom   \n    (select similar structure as above but with c_id = '02' in the where clause) c  \nleft join   \n    student d on c.s_id = d.s_id  \nwhere   \n    c.ranking between 2 and 3  \n)  \nunion  \n(select similar structure as above but with c_id = '03' in the where clause);\n<\/pre>\n<ol>\n<li>\u8ba1\u7b97\u6bcf\u4e2a\u79d1\u76ee\u6bcf\u4e2a\u5206\u6570\u8303\u56f4\u5185\u7684\u5b66\u751f\u4eba\u6570\uff1a <\/li>\n<\/ol>\n<pre>select distinct f.c_name, a.c_id,\n       b.`85-100`, b.percentage as `[85-100] percentage`,\n       c.`70-85`, c.percentage as `[70-85] percentage`,\n       d.`60-70`, d.percentage as `[60-70] percentage`,\n       e.`0-60`, e.percentage as `[0-60] percentage`\nfrom score a\n    left join (\n        select c_id,\n               sum(case when s_score &gt; 85 and s_score &lt;= 100 then 1 else 0 end) as `85-100`,\n               round(100*(sum(case when s_score &gt; 85 and s_score &lt;= 100 then 1 else 0 end)\/count(*)),2) as percentage\n        from score group by c_id\n    ) b on a.c_id = b.c_id\n    left join (\n        select c_id,\n               sum(case when s_score &gt; 70 and s_score &lt;= 85 then 1 else 0 end) as `70-85`,\n               round(100*(sum(case when s_score &gt; 70 and s_score &lt;= 85 then 1 else 0 end)\/count(*)),2) as percentage\n        from score group by c_id\n    ) c on a.c_id = c.c_id\n    left join (\n        select c_id,\n               sum(case when s_score &gt; 60 and s_score &lt;= 70 then 1 else 0 end) as `60-70`,\n               round(100*(sum(case when s_score &gt; 60 and s_score &lt;= 70 then 1 else 0 end)\/count(*)),2) as percentage\n        from score group by c_id\n    ) d on a.c_id = d.c_id\n    left join (\n        select c_id,\n               sum(case when s_score &gt;= 0 and s_score &lt;= 60 then 1 else 0 end) as `0-60`,\n               round(100*(sum(case when s_score &gt;= 0 and s_score &lt;= 60 then 1 else 0 end)\/count(*)),2) as percentage\n        from score group by c_id\n    ) e on a.c_id = e.c_id\n    left join course f on a.c_id = f.c_id;\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u5b66\u751f\u5e73\u5747\u6210\u7ee9\u53ca\u6392\u540d\uff1a <\/li>\n<\/ol>\n<pre>select a.s_id,\n       @i:=@i+1 as 'no gaps in ranking',\n       @k:=(case when @avg_score=a.avg_s then @k else @i end) as 'with gaps in ranking',\n       @avg_score:=avg_s as 'average score'\nfrom (select s_id, round(avg(s_score),2) as avg_s from score group by s_id order by avg_s desc) a,\n     (select @avg_score:=0, @i:=0, @k:=0) b;\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u5404\u79d1\u76ee\u524d\u4e09\u540d\u5b66\u751f\u7684\u8bb0\u5f55\uff1a <\/li>\n<\/ol>\n<pre>select a.s_id, a.c_id, a.s_score from score a \n    left join score b on a.c_id = b.c_id and a.s_score &lt; b.s_score\n    group by a.s_id, a.c_id, a.s_score \n    having count(b.s_id) &lt; 3\n    order by a.c_id, a.s_score desc;\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u6bcf\u95e8\u8bfe\u7a0b\u7684\u62a5\u540d\u4eba\u6570\uff1a <\/li>\n<\/ol>\n<pre>select c_id, count(s_id) from score group by c_id;\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u6070\u597d\u4fee\u8bfb\u4e24\u95e8\u8bfe\u7a0b\u7684\u5b66\u751f\u7684\u5b66\u53f7\u548c\u59d3\u540d\uff1a <\/li>\n<\/ol>\n<pre>select s_id, s_name from student \n    where s_id in (select s_id from score group by s_id having count(c_id) = 2);\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u7537\u5973\u5b66\u751f\u4eba\u6570\uff1a <\/li>\n<\/ol>\n<pre>select s_sex, count(s_sex) as count from student group by s_sex;\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u59d3\u540d\u4e2d\u5305\u542b\u201ctom\u201d\u5b57\u7b26\u7684\u5b66\u751f\u4fe1\u606f\uff1a <\/li>\n<\/ol>\n<pre>select * from student where s_name like '%tom%';\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u540c\u540d\u540c\u6027\u522b\u7684\u5b66\u751f\u5217\u8868\uff0c\u4ee5\u53ca\u540c\u540d\u5b66\u751f\u7684\u4e2a\u6570\uff1a <\/li>\n<\/ol>\n<pre>select a.s_name, a.s_sex, count(*) as count from student a  \n    join student b on a.s_id != b.s_id and a.s_name = b.s_name and a.s_sex = b.s_sex\n    group by a.s_name, a.s_sex;\n<\/pre>\n<ol>\n<li>1990\u5e74\u51fa\u751f\u5b66\u751f\u67e5\u8be2\u540d\u5355\uff1a <\/li>\n<\/ol>\n<pre>select s_name from student where s_birth like '1990%';\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u6bcf\u95e8\u8bfe\u7a0b\u7684\u5e73\u5747\u6210\u7ee9\uff0c\u6309\u5e73\u5747\u6210\u7ee9\u964d\u5e8f\u6392\u5217\uff0c\u5982\u679c\u5e73\u5747\u6210\u7ee9\u76f8\u540c\u5219\u6309\u8bfe\u7a0bid\u5347\u5e8f\u6392\u5217\uff1a <\/li>\n<\/ol>\n<pre>select c_id, round(avg(s_score), 2) as avg_score from score group by c_id order by avg_score desc, c_id asc;\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u5e73\u5747\u5206&gt;=85\u7684\u5b66\u751f\u7684\u5b66\u53f7\u3001\u59d3\u540d\u3001\u5e73\u5747\u5206\uff1a <\/li>\n<\/ol>\n<pre>select a.s_id, b.s_name, round(avg(a.s_score), 2) as avg_score from score a\n    left join student b on a.s_id = b.s_id group by s_id having avg_score &gt;= 85;\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u201c\u6570\u5b66\u201d\u8bfe\u7a0b\u6210\u7ee9\u4f4e\u4e8e60\u5206\u7684\u5b66\u751f\u59d3\u540d\u548c\u6210\u7ee9\uff1a <\/li>\n<\/ol>\n<pre>select a.s_name, b.s_score from student a \n    join score b on a.s_id = b.s_id \n    where b.c_id = (select c_id from course where c_name = 'mathematics') \n    and b.s_score &lt; 60;\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u6240\u6709\u5b66\u751f\u7684\u8bfe\u7a0b\u6210\u7ee9\u548c\u603b\u6210\u7ee9\uff1a <\/li>\n<\/ol>\n<pre>select a.s_id, a.s_name,\n    sum(case c.c_name when 'history' then b.s_score else 0 end) as 'history',\n    sum(case c.c_name when 'mathematics' then b.s_score else 0 end) as 'mathematics',\n    sum(case c.c_name when 'politics' then b.s_score else 0 end) as 'politics',\n    sum(b.s_score) as 'total score'\nfrom student a \nleft join score b on a.s_id = b.s_id \nleft join course c on b.c_id = c.c_id \ngroup by a.s_id, a.s_name;\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u4efb\u610f\u8bfe\u7a0b70\u5206\u4ee5\u4e0a\u5b66\u751f\u7684\u59d3\u540d\u3001\u8bfe\u7a0b\u540d\u79f0\u548c\u6210\u7ee9\uff1a <\/li>\n<\/ol>\n<pre>select a.s_name, b.c_name, c.s_score from student a \n    left join score c on a.s_id = c.s_id \n    left join course b on c.c_id = b.c_id \n    where c.s_score &gt;= 70;\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u5b66\u751f\u4e0d\u53ca\u683c\u7684\u8bfe\u7a0b\uff1a <\/li>\n<\/ol>\n<pre>select a.s_id, a.c_id, b.c_name, a.s_score from score a \n    left join course b on a.c_id = b.c_id \n    where a.s_score &lt; 60;\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u201801\u2019\u8bfe\u7a0b\u4e2d\u6210\u7ee980\u5206\u4ee5\u4e0a\u7684\u5b66\u751f\u7684\u5b66\u53f7\u548c\u59d3\u540d\uff1a <\/li>\n<\/ol>\n<pre>select a.s_id, b.s_name from score a \n    left join student b on a.s_id = b.s_id \n    where a.c_id = '01' and a.s_score &gt; 80;\n<\/pre>\n<ol>\n<li>\u7edf\u8ba1\u6bcf\u95e8\u8bfe\u7a0b\u7684\u5b66\u751f\u4eba\u6570\uff1a <\/li>\n<\/ol>\n<pre>select count(*) from score group by c_id;\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u201ctom\u201d\u8001\u5e08\u6240\u6559\u8bfe\u7a0b\u4e2d\u5f97\u5206\u6700\u9ad8\u7684\u5b66\u751f\u4fe1\u606f\uff1a &#8212; \u83b7\u53d6\u6559\u5e08id <\/li>\n<\/ol>\n<pre>select c_id from course c, teacher d where c.t_id = d.t_id and d.t_name = 'tom';\n<\/pre>\n<p>&#8212; \u83b7\u5f97\u6700\u9ad8\u5206\uff08\u53ef\u80fd\u6709\u5e73\u5c40\uff09<\/p>\n<pre>select max(s_score) from score where c_id = '02';\n<\/pre>\n<p>&#8211;\u83b7\u53d6\u4fe1\u606f<\/p>\n<pre>select a.*, b.s_score, b.c_id, c.c_name from student a \n    left join score b on a.s_id = b.s_id \n    left join course c on b.c_id = c.c_id \n    where b.c_id = (select c_id from course c, teacher d where c.t_id = d.t_id and d.t_name = 'tom')\n    and b.s_score in (select max(s_score) from score where c_id = '02');\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u5b66\u751fid\u3001\u8bfe\u7a0bid\u3001\u6210\u7ee9\uff0c\u4e0d\u540c\u8bfe\u7a0b\u6210\u7ee9\u76f8\u540c\uff1a <\/li>\n<\/ol>\n<pre>select distinct b.s_id, b.c_id, b.s_score from score a, score b \n    where a.c_id != b.c_id and a.s_score = b.s_score;\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u6bcf\u95e8\u8bfe\u7a0b\u7684\u524d\u4e24\u540d\u6210\u7ee9\uff1a <\/li>\n<\/ol>\n<pre>select a.s_id, a.c_id, a.s_score from score a \n    where (select count(1) from score b where b.c_id = a.c_id and b.s_score &gt;= a.s_score) &lt;= 2 order by a.c_id;\n<\/pre>\n<ol>\n<li>\u7edf\u8ba1\u6bcf\u95e8\u8bfe\u7a0b\u7684\u6ce8\u518c\u5b66\u751f\u4eba\u6570\uff08\u8d85\u8fc75\u540d\u5b66\u751f\u7684\u8bfe\u7a0b\uff09\uff1a <\/li>\n<\/ol>\n<pre>select c_id, count(*) as total from score group by c_id having total &gt; 5 order by total, c_id asc;\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u5df2\u62a5\u540d\u81f3\u5c11\u4e24\u95e8\u8bfe\u7a0b\u7684\u5b66\u751fid\uff1a <\/li>\n<\/ol>\n<pre>select s_id, count(*) as sel from score group by s_id having sel &gt;= 2;\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u5df2\u62a5\u540d\u6240\u6709\u8bfe\u7a0b\u7684\u5b66\u751f\u4fe1\u606f\uff1a <\/li>\n<\/ol>\n<pre>select * from student where s_id in (select s_id from score group by s_id having count(*) = (select count(*) from course));\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u6bcf\u4e2a\u5b66\u751f\u7684\u5e74\u9f84\uff1a &#8212; \u6839\u636e\u51fa\u751f\u65e5\u671f\u8ba1\u7b97\u5e74\u9f84\uff1b\u5982\u679c\u5f53\u524d\u6708\u4efd\/\u65e5\u671f\u65e9\u4e8e\u51fa\u751f\u65e5\u671f\u7684\u6708\u4efd\/\u65e5\u671f\uff0c\u5219\u51cf\u4e00 <\/li>\n<\/ol>\n<pre>select s_birth, (date_format(now(), '%y') - date_format(s_birth, '%y') - \n    (case when date_format(now(), '%m%d') &gt; date_format(s_birth, '%m%d') then 0 else 1 end)) as age\n    from student;\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u672c\u5468\u751f\u65e5\u7684\u5b66\u751f\uff1a <\/li>\n<\/ol>\n<pre>select * from student where week(date_format(now(), '%y%m%d')) = week(s_birth);\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u4e0b\u5468\u751f\u65e5\u7684\u5b66\u751f\uff1a <\/li>\n<\/ol>\n<pre>select * from student where week(date_format(now(), '%y%m%d')) + 1 = week(s_birth);\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u672c\u6708\u751f\u65e5\u7684\u5b66\u751f\uff1a <\/li>\n<\/ol>\n<pre>select * from student where month(date_format(now(), '%y%m%d')) = month(s_birth);\n<\/pre>\n<ol>\n<li>\u67e5\u8be2\u4e0b\u4e2a\u6708\u751f\u65e5\u7684\u5b66\u751f\uff1a <\/li>\n<\/ol>\n<pre>select * from student where month(date_format(now(), '%Y%m%d')) + 1 = month(s_birth);\n<\/pre>\n<p>\u597d\u7684\uff0c\u5982\u679c\u60a8\u89c9\u5f97\u8fd9\u7bc7\u6587\u7ae0\u6709\u5e2e\u52a9\uff0c\u8bf7\u968f\u65f6\u5206\u4eab\u7ed9\u66f4\u591a\u4eba\u3002<\/p>\n<p>\u5982\u679c\u4f60\u60f3\u627e\u4e00\u4e2asql\u5de5\u5177\u6765\u7ec3\u4e60\uff0c\u53ef\u4ee5\u8bd5\u8bd5\u6211\u4eec\u7684sqlynx\uff0c\u5b83\u754c\u9762\u7b80\u5355\uff0c\u6613\u4e8e\u4f7f\u7528\u3002 https:\/\/www.sqlynx.com\/download\/ \u514d\u8d39\u4e0b\u8f7d<\/p>\n<p>\u597d\u4e86\uff0c\u672c\u6587\u5230\u6b64\u7ed3\u675f\uff0c\u5e26\u5927\u5bb6\u4e86\u89e3\u4e86\u300aMySQL \u57fa\u672c\u7cbe\u9009\u7ec3\u4e60\u9898\u53ca\u7b54\u6848\u300b\uff0c\u5e0c\u671b\u672c\u6587\u5bf9\u4f60\u6709\u6240\u5e2e\u52a9\uff01\u5173\u6ce8\u4e3b\u673a\u5b9d\u8d1d\u516c\u4f17\u53f7\uff0c\u7ed9\u5927\u5bb6\u5206\u4eab\u66f4\u591a\u6570\u636e\u5e93\u77e5\u8bc6\uff01<\/p>\n<p> \u7248\u672c\u58f0\u660e \u672c\u6587\u8f6c\u8f7d\u4e8e\uff1adev.to \u5982\u6709\u4fb5\u72af\uff0c\u8bf7\u8054\u7cfb \u5220\u9664 <\/p>\n<dl>\n<dt>\n <\/dt>\n<\/dl>\n","protected":false},"excerpt":{"rendered":"<p>MySQL \u57fa\u672c\u7cbe\u9009\u7ec3\u4e60\u9898\u53ca\u7b54\u6848&#46;&#46;&#46;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[101],"tags":[],"class_list":["post-200123","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/posts\/200123","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/comments?post=200123"}],"version-history":[{"count":0,"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/posts\/200123\/revisions"}],"wp:attachment":[{"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/media?parent=200123"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/categories?post=200123"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/tags?post=200123"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}