这么骚的SQL进阶技巧，不怕被揍么？( 二 ) _SQL

文章插图

用 HAVING 表示如下：
SELECT '存在缺失的编号' AS gapFROM SeqTbl HAVING COUNT(*) <> MAX(seq); ④自连接
针对相同的表进行的连接被称为“自连接”(self join) ，这个技巧常常被人们忽视，其实是有挺多妙用的。

文章插图

删除重复行：上图中有三个橘子，需要把这些重复的行给删掉，用如下自连接可以解决：
DELETE FROM Products P1WHERE id < ( SELECT MAX(P2.id)FROM Products P2WHERE P1.name = P2.nameAND P1.price = P2.price );排序：在 DB 中，我们经常需要按分数，人数，销售额等进行排名，有 Oracle, DB2 中可以使用 RANK 函数进行排名，不过在 MySQL 中 RANK 函数未实现。
这种情况我们可以使用自连接来实现，如对以下 Products 表按价格高低进行排名：

文章插图

使用自连接可以这么写：

-- 排序从 1 开始 。如果已出现相同位次 ， 则跳过之后的位次SELECT P1.name,P1.price,(SELECT COUNT(P2.price)FROM Products P2WHERE P2.price > P1.price) + 1 AS rank_1FROM Products P1ORDER BY rank_1;

结果如下：
name price rank----- ------ ------橘子1001西瓜802苹果503葡萄503香蕉503柠檬306 ⑤巧用 COALESCE 函数
此函数作用返回参数中的第一个非空表达式，假设有如下商品，我们重新格式化一样，如果 city 为 null ，代表商品不在此城市发行。
但我们在展示结果的时候不想展示 null ，而想展示 'N/A', 可以这么做：
SELECTCOALESCE(city, 'N/A')FROMcustomers;

文章插图

【这么骚的SQL进阶技巧，不怕被揍么？】SQL 性能优化技巧
①参数是子查询时，使用 EXISTS 代替 IN
如果 IN 的参数是(1 ， 2 ， 3)这样的值列表时，没啥问题，但如果参数是子查询时，就需要注意了。
比如，现在有如下两个表：

文章插图

现在我们要查出同时存在于两个表的员工，即田中和铃木，则以下用 IN 和 EXISTS 返回的结果是一样，但是用 EXISTS 的 SQL 会更快：

-- 慢 SELECT *FROM Class_A WHERE id IN (SELECT idFROMCLASS_B);-- 快 SELECT *FROM Class_A AWHERE EXISTS (SELECT *FROM Class_BBWHERE A.id = B.id);

为啥使用 EXISTS 的 SQL 运行更快呢，有两个原因：

可以`用到索引，如果连接列 (id) 上建立了索引，那么查询 Class_B 时不用查实际的表，只需查索引就可以了。
如果使用 EXISTS ，那么只要查到一行数据满足条件就会终止查询，不用像使用 IN 时一样扫描全表。在这一点上 NOT EXISTS 也一样。

另外如果 IN 后面如果跟着的是子查询，由于 SQL 会先执行 IN 后面的子查询，会将子查询的结果保存在一张临时的工作表里(内联视图) ，然后扫描整个视图。
显然扫描整个视图这个工作很多时候是非常耗时的，而用 EXISTS 不会生成临时表。
当然了，如果 IN 的参数是子查询时，也可以用连接来代替，如下：
-- 使用连接代替 IN SELECT A.id, A.name FROM Class_A A INNER JOIN Class_B B ON A.id = B.id; 用到了「id」列上的索引，而且由于没有子查询，也不会生成临时表。
②避免排序
SQL 是声明式语言，即对用户来说，只关心它能做什么，不用关心它怎么做，这样可能会产生潜在的性能问题：排序。
会产生排序的代表性运算有下面这些：