mysql - 使用 JOIN 避免 SELECT-in-SELECT 语句

Question

假设我有一个具有以下结构的数据库表

表记录:

id       INT NOT NULL (PRIMARY_KEY, AUTO_INCREMENT)
parent   INT NOT NULL
priority INT NUL NULL

现在我想选择所有记录，但我需要一个列来匹配每一行到具有相同父级的后续(基于priority，升序) > - 如果它存在，否则 NULL。在这个任意示例中，parent 是一个完全外部表的标识符，与记录没有直接关系。

例如数据集:

| id | parent | priority |
|----|--------|----------|
| 1  | 1      | 2        |
| 2  | 1      | 6        |
| 3  | 1      | 1        |
| 4  | 2      | 4        |
| 5  | 2      | 3        |

应该产生以下内容:

| id | parent | priority | match |
|----|--------|----------|-------|
| 1  | 1      | 2        | 2     |
| 2  | 1      | 6        | NULL  |
| 3  | 1      | 1        | 1     |
| 4  | 2      | 4        | NULL  |
| 5  | 2      | 3        | 4     |

一个有效的 SQL 实现是:

SELECT r1.*, 
    (SELECT r2.id 
        FROM record AS r2 
        WHERE r2.parent = r1.parent 
            AND r2.priority > r1.priority 
        ORDER BY r2.priority ASC 
        LIMIT 1
    ) AS match_id
FROM record AS r1

但是，我非常关心 SELECT-in-SELECT 的可伸缩性。关于如何干净地执行此操作的任何想法？可以使用 JOIN 吗？

Answer 1

假设您对每个家长都有独特的优先级，我相信这会奏效:

select r.id, r.parent, r.priority, r2.id as `match`
from (
    select r.id, r.parent, r.priority, min(r2.priority) as next_priority
    from record r
    left join record r2 on
      r.parent = r2.parent
      and r.priority < r2.priority
    group by r.id, r.parent, r.priority
  ) r
left join record r2 on
    r.parent = r2.parent
    and r.next_priority = r2.priority
order by r.id

match是MySQL的保留关键字，所以需要反引号。

这是如何工作的，我们从高于当前优先级的升序中提取下一个优先级，并基于此(以及我们对每个父级都有唯一优先级的事实)我们可以提取相应的行 ID。

Live DEMO - SQL Fiddle