sql 小技巧

最新推荐文章于 2023-06-17 22:30:59 发布

原创最新推荐文章于 2023-06-17 22:30:59 发布 · 273 阅读

0 ·

CC 4.0 BY-SA版权

本文深入探讨了SQL中一些鲜为人知但极其实用的技巧，包括如何利用HAVING子句检查数据连续性，自连接用于删除重复记录及排序，以及COALESCE函数在处理NULL值时的应用，帮助读者提升数据库操作效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

巧用 HAVING 子句

一般 HAVING 是与 GROUP BY 结合使用的，但其实它是可以独立使用的，假设有如下表，第一列 seq 叫连续编号，但其实有些编号是缺失的，怎么知道编号是否缺失呢？

用 HAVING 表示如下：

SELECT '存在缺失的编号' AS gap
  FROM SeqTbl
HAVING COUNT(*) <> MAX(seq);

自连接

针对相同的表进行的连接被称为“自连接”(self join)，这个技巧常常被人们忽视，其实是有挺多妙用的。

删除重复行：上图中有三个橘子，需要把这些重复的行给删掉，用如下自连接可以解决：

DELETE FROM Products P1
 WHERE id < ( SELECT MAX(P2.id)
                   FROM Products P2
                  WHERE P1.name = P2.name
                    AND P1.price = P2.price );

排序：在 DB 中，我们经常需要按分数，人数，销售额等进行排名，有 Oracle, DB2 中可以使用 RANK 函数进行排名，不过在 MySQL 中 RANK 函数未实现。

这种情况我们可以使用自连接来实现，如对以下 Products 表按价格高低进行排名：

使用自连接可以这么写：

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

结果如下：

name price rank
----- ------ ------
橘子 100 1
西瓜 80 2
苹果 50 3
葡萄 50 3
香蕉 50 3
柠檬 30 6

巧用 COALESCE 函数

此函数作用返回参数中的第一个非空表达式，假设有如下商品，我们重新格式化一样，如果 city 为 null，代表商品不在此城市发行。

但我们在展示结果的时候不想展示 null，而想展示 'N/A', 可以这么做：

SELECT 
    COALESCE(city, 'N/A')
  FROM
    customers;