一道简单又不简单的sql查询语句

benny

发布于 2019-03-07 10:54:10

5750

发布于 2019-03-07 10:54:10

抛砖引玉

无意间在简书上浏览的时候看到一篇写数据库查询的练习题，其实也好无奈，大数据啊大数据，自从看了几天有关编程和程序员方面的文章，首页推荐的内容都是关于这样的技术贴，太那啥了，想看点小故事小情感类的文章都要自己搜，好吧。今天没有小故事，可能会比较枯燥（毕竟数据库嘛）

题目是：查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

先看两个表，然后我梳理了一下知识要点，最后大家就可以解题啦（当然并不一定要用这个解法，有兴趣可以在下方留言你的查询sql语句）

数据库表

学生表

Student(SId,Sname,Sage,Ssex) --SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');

成绩表

SC(SId,CId,score) --SId 学生编号,CId 课程编号,score 分数

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

建议

建议大家先自己花上一点时间思考一下要怎么写这条sql，当然最好是直接写出来。

知识点

CONCAT(str1,str2)

这里用到了mysql的concat函数，concat(str1,str2),连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL 例如：

mysql> select concat('hello','world','!');
+------------------------+
| concat('hello','world','!') |
+------------------------+
| helloworld!|
+------------------------+
mysql> select concat('hello','world',null);
+------------------------+
| concat('hello','world',null) |
+------------------------+
| NULL   |
+------------------------+

CONCAT_WS(separator,str1,str2)

Separator是分隔符，分隔符为 NULL，则结果为 NULL，但不会因为str1或者str2为null而结果值为null 例如：

mysql> select concat_ws(',','hello','world','!');

+-------------------------------+
| concat_ws(',','hello','world','!') |
+-------------------------------+
| hello,world,!|
+-------------------------------+
mysql> select concat_ws(',','hello','world',NULL);
+-------------------------------+
| concat_ws(',','hello','world',NULL) |
+-------------------------------+
| hello,world|
+-------------------------------+

GROUP_CONCAT()

函数GROUP_CONCAT(([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符']) 例如查询学生表：

mysql> select group_concat(Sname) from Student group by Ssex;
+------+--------------------+
 group_concat(Sname) |
+------+--------------------+
|赵雷,钱电,孙风,李云 |
|周梅,吴兰,郑竹,张三,李四,李四,赵六,孙七|
+------+--------------------+

REPEAT()

用来复制字符串

mysql> select repeat('ab',2);

    +----------------+
| repeat('ab',2) |
+----------------+
| abab           |
+----------------+

结果

熟悉了知识点后，有没有觉得开篇给出来的那道题目很容易就可以解出来了？

SELECT
    * 
FROM
    student 
WHERE
    SId IN (
SELECT
    a. SId
FROM
    ( SELECT SId, GROUP_CONCAT( CId ORDER BY CId ) AS gc FROM sc GROUP BY SId ) a 
WHERE
    a.gc = ( SELECT GROUP_CONCAT( CId ORDER BY CId ) FROM sc WHERE SId = '01' ) 
    AND a.SId <> '01' 
)

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2019-01-19，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库