基础

讲一下distinct的语法

DISTINCT 关键字在 SQL 中用于从结果集中去除重复的行。它可以用于单个字段，也可以用于多个字段。当用于多个字段时，只有当所有指定的字段的组合都相同时，才会被视为重复并去除。

基本语法

单字段去重

SELECT DISTINCT column1
FROM table_name;

这个查询将返回表 table_name 中 column1 字段的唯一值。

多字段去重

SELECT DISTINCT column1, column2
FROM table_name;

这个查询将返回表 table_name 中 column1 和 column2 组合后的唯一值。

示例

假设有一个名为 employees 的表，包含以下数据：

id	first_name	last_name	department
1	John	Doe	HR
2	Jane	Doe	IT
3	John	Smith	IT
4	John	Doe	HR

单字段去重示例

SELECT DISTINCT first_name
FROM employees;

这个查询将返回所有唯一的 first_name：

first_name
John
Jane

多字段去重示例

SELECT DISTINCT first_name, last_name
FROM employees;

这个查询将返回所有唯一的 first_name 和 last_name 的组合：

first_name	last_name
John	Doe
Jane	Doe
John	Smith

注意事项

• DISTINCT 关键字适用于所有字段的组合。如果两个或多个行的所有字段都相同，则这些行被视为重复行。
• 当在多个字段上使用 DISTINCT 时，SQL 引擎会根据指定的字段组合来检查重复。
• 在大数据集上使用 DISTINCT 可能会影响性能，因为需要对结果进行去重操作。

在查询中使用 DISTINCT

假设我们有一个查询，需要对多个字段进行去重，并且需要对结果进行排序和限制返回的行数。可以使用如下方式：

SELECT DISTINCT column1, column2
FROM table_name
WHERE some_condition
ORDER BY column1, column2
LIMIT 10;

这个查询将对 column1 和 column2 进行去重，然后按照指定的字段排序，最后限制返回的行数为 10。

limit 语句和distinct哪个先执行

在 SQL 查询的执行过程中，DISTINCT 会先于 LIMIT 执行。这意味着在应用 LIMIT 之前，查询会首先通过 DISTINCT 去除重复的行。

执行顺序

1. FROM 子句 - 确定从哪个表或视图中选择数据。
2. WHERE 子句 - 应用过滤条件，筛选出满足条件的行。
3. JOIN 子句 - 执行表的连接操作。
4. GROUP BY 子句 - 对数据进行分组。
5. HAVING 子句 - 筛选分组后的数据。
6. SELECT 子句 - 选择要返回的列，同时可以包含 DISTINCT 去重操作。
7. DISTINCT 操作 - 去除重复的行。
8. ORDER BY 子句 - 对结果集进行排序。
9. LIMIT 子句 - 限制返回的行数。

示例说明

假设有一个名为 employees 的表，包含以下数据：

id	first_name	last_name	department
1	John	Doe	HR
2	Jane	Doe	IT
3	John	Smith	IT
4	John	Doe	HR

查询示例

SELECT DISTINCT first_name, last_name
FROM employees
ORDER BY last_name
LIMIT 2;

执行顺序如下：

1. FROM 子句 - 读取 employees 表中的所有数据。

2. SELECT 子句 - 选择 first_name 和 last_name 列。

3. DISTINCT 操作 - 去除重复的 first_name 和 last_name 组合。结果为：

   first_name	last_name
   John	Doe
   Jane	Doe
   John	Smith

4. ORDER BY 子句 - 按 last_name 排序。结果为：

   first_name	last_name
   John	Doe
   Jane	Doe
   John	Smith

5. LIMIT 子句 - 限制返回的行数为 2。最终结果为：

   first_name	last_name
   John	Doe
   Jane	Doe

总结

在 SQL 查询中，DISTINCT 操作在 LIMIT 之前执行。这意味着查询首先会去除重复的行，然后对去重后的结果集应用 LIMIT 限制返回的行数。

使用SQL判断两表的关联关系

在数据库开发和数据分析中，理解两个表之间的关系是非常重要的。常见的关系类型包括一对一、一对多、多对一和多对多。通过 SQL 查询，我们可以方便地检测和确认这些关系。本文将介绍一个 SQL 查询，它可以帮助我们判断两个表之间的关联关系。

背景

假设我们有两个表 tab_a 和 tab_b，它们通过一个关联字段（例如 关联id）进行关联。我们需要确定 tab_a 和 tab_b 之间的关系类型。

查询结构

为了实现这个目标，我们可以使用 SQL 的 WITH 语句（即公用表表达式，CTE）来创建两个子查询，然后根据这些子查询的结果来判断关系类型。

以下是完整的 SQL 查询：

WITH aToMany AS (
    SELECT a.id,
           COUNT(a.id) AS acount
    FROM tab_a a
    LEFT JOIN tab_b b ON a.关联id = b.关联id
    GROUP BY a.id
    HAVING acount > 1
),
bToMany AS (
    SELECT b.id AS id,
           COUNT(b.id) AS bcount
    FROM tab_b b
    LEFT JOIN tab_a a ON a.关联id = b.关联id
    GROUP BY b.id
    HAVING bcount > 1
)
SELECT CASE
           WHEN (SELECT COUNT(*) FROM aToMany) = 0 AND (SELECT COUNT(*) FROM bToMany) = 0 THEN 'a比b：一对一'
           WHEN (SELECT COUNT(*) FROM aToMany) > 0 AND (SELECT COUNT(*) FROM bToMany) = 0 THEN 'a比b：一对多'
           WHEN (SELECT COUNT(*) FROM aToMany) = 0 AND (SELECT COUNT(*) FROM bToMany) > 0 THEN 'a比b：多对一'
           WHEN (SELECT COUNT(*) FROM aToMany) > 0 AND (SELECT COUNT(*) FROM bToMany) > 0 THEN 'a比b：多对多'
           ELSE '未知关系'
       END AS relationship;

优化一下：

WITH aToMany AS (
    SELECT a.id,
           COUNT(a.id) AS acount
    FROM tab_a a
             INNER JOIN tab_b b ON a.关联id = b.关联id
    GROUP BY a.id
    HAVING acount > 1
    LIMIT 3
),
     bToMany AS (
         SELECT b.id AS id,
                COUNT(b.id) AS bcount
         FROM tab_b b
                  INNER JOIN tab_a a ON a.关联id = b.关联id
         GROUP BY b.id
         HAVING bcount > 1
         LIMIT 3
     )
SELECT CASE
           WHEN (SELECT COUNT(*) FROM aToMany) = 0 AND (SELECT COUNT(*) FROM bToMany) = 0 THEN 'a比b：一对一'
           WHEN (SELECT COUNT(*) FROM aToMany) > 0 AND (SELECT COUNT(*) FROM bToMany) = 0 THEN 'a比b：一对多'
           WHEN (SELECT COUNT(*) FROM aToMany) = 0 AND (SELECT COUNT(*) FROM bToMany) > 0 THEN 'a比b：多对一'
           WHEN (SELECT COUNT(*) FROM aToMany) > 0 AND (SELECT COUNT(*) FROM bToMany) > 0 THEN 'a比b：多对多'
           ELSE '未知关系'
           END AS relationship;

详细解释

1. aToMany 子查询：

   WITH aToMany AS (
       SELECT a.id,
              COUNT(a.id) AS acount
       FROM tab_a a
       LEFT JOIN tab_b b ON a.关联id = b.关联id
       GROUP BY a.id
       HAVING acount > 1
   )
   

   这个子查询计算了 tab_a 中每个 id 对应的记录数。如果某个 id 对应的记录数大于 1，则说明 tab_a 中存在一个对 tab_b 的多对一关系。

2. bToMany 子查询：

   bToMany AS (
       SELECT b.id AS id,
              COUNT(b.id) AS bcount
       FROM tab_b b
       LEFT JOIN tab_a a ON a.关联id = b.关联id
       GROUP BY b.id
       HAVING bcount > 1
   )
   

   这个子查询计算了 tab_b 中每个 id 对应的记录数。如果某个 id 对应的记录数大于 1，则说明 tab_b 中存在一个对 tab_a 的多对一关系。

3. 关系判断：

   SELECT CASE
              WHEN (SELECT COUNT(*) FROM aToMany) = 0 AND (SELECT COUNT(*) FROM bToMany) = 0 THEN 'a比b：一对一'
              WHEN (SELECT COUNT(*) FROM aToMany) > 0 AND (SELECT COUNT(*) FROM bToMany) = 0 THEN 'a比b：一对多'
              WHEN (SELECT COUNT(*) FROM aToMany) = 0 AND (SELECT COUNT(*) FROM bToMany) > 0 THEN 'a比b：多对一'
              WHEN (SELECT COUNT(*) FROM aToMany) > 0 AND (SELECT COUNT(*) FROM bToMany) > 0 THEN 'a比b：多对多'
              ELSE '未知关系'
          END AS relationship;
   

   最后，我们通过一个 CASE 语句来判断和返回两个表之间的关系。具体判断逻辑如下：

   • 如果 aToMany 和 bToMany 都没有记录，则说明是 一对一 关系。
   • 如果只有 aToMany 有记录，则说明是 一对多 关系。
   • 如果只有 bToMany 有记录，则说明是 多对一 关系。
   • 如果 aToMany 和 bToMany 都有记录，则说明是 多对多 关系。
   • 如果上述情况都不满足，则返回 未知关系。

总结

通过上述 SQL 查询，我们可以清晰地判断两个表之间的关联关系。这个方法利用了公用表表达式（CTE）和聚合函数，使得查询逻辑清晰易懂。希望这篇博客能帮助你更好地理解和使用 SQL 来分析表之间的关系。

---

CTE递归查询

语法详解：递归 SQL 的组成部分

以下从 语法结构 和 执行逻辑 两个维度，逐步解析该递归 SQL 的每个关键部分：

---

1. WITH RECURSIVE 子句

WITH RECURSIVE subtree AS (...)

• 作用：
  定义一个递归的公共表表达式（CTE），命名为 subtree。RECURSIVE 关键字表示该 CTE 包含递归逻辑。
• 语法要求：
  • 必须包含至少一个 锚点成员（Anchor Member） 和 递归成员（Recursive Member），用 UNION ALL 连接。
  • 递归成员必须引用 CTE 自身（即 subtree）。

---

2. 锚点成员（Anchor Member）

SELECT node_id, ancestors
FROM uniform_resource
WHERE node_id = #{nodeId}

• 作用：
  初始化递归的起点，即获取目标节点（nodeId）的 node_id 和 ancestors。
• 语法解析：
  • 普通 SELECT 查询，无递归。
  • 结果作为递归的初始数据集。

---

3. 递归成员（Recursive Member）

SELECT ur.node_id, ur.ancestors
FROM uniform_resource ur
INNER JOIN subtree ON ur.ancestors = CONCAT(subtree.ancestors, ',', subtree.node_id)

• 作用：
  基于锚点成员的结果，逐层递归查找子节点。
• 语法解析：
  • INNER JOIN subtree：引用 CTE 自身（subtree），表示基于前一次递归的结果继续查询。
  • CONCAT(subtree.ancestors, ',', subtree.node_id)：拼接父节点的路径，生成子节点的 ancestors 模式。
    例如：父节点 ancestors='1,10', node_id=100 → 子节点 ancestors='1,10,100'。
  • 终止条件：当递归成员不再产生新数据时，递归停止。

---

4. UNION ALL 操作

UNION ALL

• 作用：
  合并锚点成员和递归成员的结果集。
• 语法要求：
  • 必须使用 UNION ALL（而非 UNION），保留重复数据。
  • 递归成员不能包含聚合函数、GROUP BY 或 ORDER BY。

---

5. 主查询（Main Query）

SELECT COUNT(dm.id) 
FROM subtree mch
INNER JOIN device_merchant dm ON dm.merchantId = mch.merchant_id
INNER JOIN device d ON d.id = dm.deviceId AND d.isDeleted = 0
WHERE mch.type = 2;

• 作用：
  从递归生成的 subtree 中统计有效设备数。
• 语法解析：
  • FROM subtree mch：将递归 CTE 的最终结果作为临时表 mch。
  • INNER JOIN：关联设备相关表，过滤无效设备（d.isDeleted = 0）。
  • WHERE mch.type = 2：筛选类型为商户的节点。
  • COUNT(dm.id)：统计符合条件的设备总数。

---

递归执行流程（分步图解）

      递归 CTE 执行过程
      +---------------------+
      | 初始查询（锚点成员） | → 获取 nodeId=200 的节点
      +---------------------+
                 ↓
      +---------------------+
      | 递归成员第一次执行   | → 查找 ancestors='1,10,100,200' 的子节点
      +---------------------+
                 ↓
      +---------------------+
      | 递归成员第二次执行   | → 查找 ancestors='1,10,100,200,201' 的子节点
      +---------------------+
                 ↓
              ...（直到无新数据）
                 ↓
      +---------------------+
      | 合并所有结果集       | → UNION ALL 生成完整的 subtree
      +---------------------+
                 ↓
      +---------------------+
      | 主查询统计设备数     | → 关联设备表并过滤
      +---------------------+

---

语法注意事项

1. 递归成员的限制：

   • 不能直接引用其他 CTE（除非在 WITH 子句中定义）。
   • 必须通过 UNION ALL 合并锚点和递归成员。

2. 终止条件隐式依赖：

   • 递归终止依赖于递归成员不再生成新数据（而非显式的 WHERE 条件）。

3. 字段一致性：

   • 锚点成员和递归成员的字段数量、类型必须一致。

---

对比非递归语法

原方法（使用 LIKE）

WHERE mch.ancestors LIKE CONCAT(..., '%')

• 缺点：路径匹配低效，无法利用索引优化。

递归方法

WITH RECURSIVE subtree AS (...)

• 优点：
  • 精准逐层遍历，避免全表扫描。
  • 天然支持动态层级，代码更易维护。

---

总结

此 SQL 通过 WITH RECURSIVE 语法实现了树形结构的递归遍历，核心步骤为：

1. 锚点成员 初始化目标节点。
2. 递归成员 逐层展开子节点。
3. 主查询 统计最终结果。

递归语法在 处理层级数据 时比传统 LIKE 更高效、更清晰，但需数据库支持（如 MySQL 8.0+）。