mysql工程师笔试题 mysql语句笔试题

mysql语句运行顺序深入分析

晚上做了网易的笔试题，编程题的第三道是有关sql语句的一道题，就从这道题入手开始分析sql语句执行过程，尤其是当有多个条件时的执行过程。题目如下：

表结构：

CREATE TABLE `user` (
  `tbaname` varchar(20),
  `username` varchar(20)
)

现执行如下sql语句，让你阅读并理解SQL

select
	a.tbaname,
	b.tbaname,
	count(distinct a.username) as num
from
	user a
inner join
	user b
where
	a.username = b.username and a.tbaname != b.tbaname
group by
	a.tbaname,b.tbaname
order by
	a.tbaname,b.tbaname
having
	num > 2;

再给一个例子

输入：

ddd	kate
ccc	kate
ccc	beal
eee tom
ddd beal
bbb kate
ddd tom
ccc tom

输出：

ccc	ddd	3
ddd ccc 3

题目就是这样的，现在进行分析。

我们看到这个sql语句，首先需要注意sql语句的执行顺序，因为顺序不同，结果必然不同。

SQL语句的定义顺序：

(1) SELECT (2)DISTINCT<select_list>
(3) FROM <left_table>
(4) <join_type> JOIN <right_table>
(5)         ON <join_condition>
(6) WHERE <where_condition>
(7) GROUP BY <group_by_list>
(8) WITH {CUBE|ROLLUP}
(9) HAVING <having_condition>
(10) ORDER BY <order_by_condition>
(11) LIMIT <limit_number>

SQL语句的执行顺序：

(8) SELECT (9)DISTINCT<select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2)         ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
(11) LIMIT <limit_number>

我们可以看到执行的顺序为：

1. FROM:对FROM子句中的左表<left_table>和右表<right_table>执行笛卡儿积，产生虚拟表VT1;
2. ON: 对虚拟表VT1进行ON筛选，只有那些符合<join_condition>的行才被插入虚拟表VT2;
3. JOIN: 如果指定了OUTER JOIN(如LEFT OUTER JOIN、RIGHT OUTER JOIN)，那么保留表中未匹配的行作为外部行添加到虚拟表VT2，产生虚拟表VT3。如果FROM子句包含两个以上的表，则对上一个连接生成的结果表VT3和下一个表重复执行步骤1~步骤3，直到处理完所有的表;
4. WHERE: 对虚拟表VT3应用WHERE过滤条件，只有符合<where_condition>的记录才会被插入虚拟表VT4;
5. GROUP By: 根据GROUP BY子句中的列，对VT4中的记录进行分组操作，产生VT5;
6. CUBE|ROllUP: 对VT5进行CUBE或ROLLUP操作，产生表VT6;
7. HAVING: 对虚拟表VT6应用HAVING过滤器，只有符合<having_condition>的记录才会被插入到VT7;
8. SELECT: 第二次执行SELECT操作，选择指定的列，插入到虚拟表VT8中;
9. DISTINCT: 去除重复，产生虚拟表VT9;
10. ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10;
11. LIMIT: 取出指定行的记录，产生虚拟表VT11，并返回给查询用户

故而，题中SQL语句的执行过程：

• 进行内连接执行笛卡尔积，产生一张8*8 = 64行的虚拟表1；
• where过滤，生成虚拟表2；
• 对虚拟表2进行group by分组，生成虚拟表3；
• 对虚拟表3进行having，生成虚拟表4；
• 对虚拟表4进行select，选择指定行插入虚拟标5；
• 对虚拟表5进行去重，得到虚拟表6；
• 对虚拟表6排序，得到虚拟表7；

我们可以在mysql中建立这样表进行操作，方便理解

insert into user values('ddd','kate');
insert into user values('ccc','kate');
insert into user values('ccc','beal');
insert into user values('eee','tom');
insert into user values('ddd','beal');
insert into user values('bbb','kate');
insert into user values('ddd','tom');
insert into user values('ccc','tom');

首先执行笛卡尔积，select a.tbaname,b.tbaname from user a inner join user b;

之后进行where过滤，我们可以用如下语句查到结果

select a.tbaname,b.tbaname,a.username,b.username from user a inner join user b where a.username = b.username and a.tbaname != b.tbaname;

之后分组

select a.tbaname,b.tbaname,a.username,b.username from user a inner join user b where a.username = b.username and a.tbaname != b.tbaname group by a.tbaname,b.tbaname;

结果如下：

这里我们需要注意，当执行group by语句之后，select语句执行出来的内容只是每个分组的第一条语句。

可以查看此时的num：

select a.tbaname,b.tbaname,a.username,b.username,count(distinct a.username)as num from user a inner join user b where a.username = b.username and a.tbaname != b.tbaname group by a.tbaname,b.tbaname;

为什么这里num是三呢？因为count(distinct a.username)是对于每一个group分别进行的，对于ccc ddd 的这组我们可以看到是有三个不同的username的，如图中红框所选，故而总和为3。同样对于ddd ccc的组，也有三个不同的username，如图中绿框所选，故而总和也为3。而其他的组合都只有一条，故而都为1。

最后加入having和select：

select a.tbaname,b.tbaname,a.username,b.username,count(a.username)as num from user a inner join user b where a.username = b.username and a.tbaname != b.tbaname group by a.tbaname,b.tbaname having num > 2;

按题中所给select后：

select a.tbaname,b.tbaname,count(a.username)as num from user a inner join user b where a.username = b.username and a.tbaname != b.tbaname group by a.tbaname,b.tbaname having num > 2;

最后加入排序即可得到上例正确结果。

总结：本题关键在于明确sql语句的执行顺序，当有多个关键字出现时，首先执行哪个。其次需要明确分组后的所有操作都是对每个组单独进行的。