每个连接的 MySQL 查询限制

Question

我以前试过问问题，但如果没有正确的术语我不太熟悉，很难具体问。所以这是一个例子

以这个查询为例:

(
SELECT *
FROM comments
WHERE depth = 0
ORDER BY id DESC
LIMIT 2
)
UNION ALL
(
  SELECT c.*
    FROM comments c JOIN 
    (
      SELECT id
      FROM comments
      WHERE depth = 0
      ORDER BY id DESC
      LIMIT 2
    ) p ON c.parent_id = p.id
    LIMIT 5
)


id  parent_id   depth   title
1   0           0       Title 1
2   0           0       Title 2
3   1           1       Title 3
4   1           1       Title 4
5   1           1       Title 5
6   1           1       Title 6
7   1           1       Title 7

我得到两个深度为 0 的行，并且在连接中我也得到了这两个返回查询的 5 个子元素。我想要得到的是获得这两个查询中每一个的 5 个子元素，总共 10 行(深度为 1)。例如:

id  parent_id   depth   title
1   0           0       Title 1
2   0           0       Title 2
3   1           1       Title 3
4   1           1       Title 4
5   1           1       Title 5
6   1           1       Title 6
7   1           1       Title 7
8   2           1       Title 8
9   2           1       Title 9
10  2           1       Title 10
11  2           1       Title 11
12  2           1       Title 12

如果使用邻接表并要求将所有内容作为并集(扁平)返回，这是否可能？

编辑:感谢 Bill Karwin 的回答，我现在开始工作了。我仍然想知道是否有更短的方法来写这个。我有 6 (0-5) 个深度级别，所以我的查询相当长(而且可能不是最佳的)。这是三个级别的外观(您可以想象完整的级别)。

-- DEPTH LEVEL 0
(
  SELECT * FROM (
    SELECT *, 1 as _rn, @parent:=0
    FROM comments
    WHERE depth = 0
    ORDER BY id DESC
    LIMIT 2
  ) as D0
)
union all

-- DEPTH LEVEL 1
(
SELECT *
FROM (
  SELECT c.*, @row:=IF(@parent=c.comment_id,@row+1,1) AS _rn, @parent:=c.comment_id
    FROM (SELECT @parent:=null) AS _init 
    STRAIGHT_JOIN comments c 
    INNER JOIN 
    (
      SELECT id
      FROM comments
      WHERE depth = 0
      ORDER BY id DESC
      LIMIT 2
    ) p ON c.comment_id = p.id
  ) AS _ranked
WHERE _ranked._rn <= 5
)
union all

-- DEPTH LEVEL 2
(
SELECT *
FROM (
  SELECT c.*, @row:=IF(@parent=c.comment_id,@row+1,1) AS _rn, @parent:=c.comment_id
    FROM (SELECT @parent:=null) AS _init 
    STRAIGHT_JOIN comments c 
    INNER JOIN 
    (

      (
      SELECT *
      FROM (
        SELECT c.*, @row:=IF(@parent=c.comment_id,@row+1,1) AS _rn, @parent:=c.comment_id
          FROM (SELECT @parent:=null) AS _init 
          STRAIGHT_JOIN comments c 
          INNER JOIN 
          (
            SELECT id
            FROM comments
            WHERE depth = 0
            ORDER BY id DESC
            LIMIT 2
          ) p ON c.comment_id = p.id
        ) AS _ranked
      WHERE _ranked._rn <= 2
      )

    ) p ON c.comment_id = p.id
  ) AS _ranked
WHERE _ranked._rn <= 2
)

Answer 1

你不能用 LIMIT 这样做，因为 LIMIT 是在结果集完全完成之后，在所有的连接、分组、排序等之后应用的。

您正在使用 greatest-n-per-group 的变体查询类型。在 MySQL 中执行此操作很棘手，因为 MySQL 不支持许多其他 SQL 数据库支持的 ROW_NUMBER() 窗口函数。

这是 MySQL 的解决方法，其中用户定义的变量可以代替分区行号:

SELECT *
FROM (
  SELECT c.*, @row:=IF(@parent=c.parent_id,@row+1,1) AS _rn, @parent:=c.parent_id
    FROM (SELECT @parent:=null) AS _init 
    STRAIGHT_JOIN comments c 
    INNER JOIN 
    (
      SELECT id
      FROM comments
      WHERE depth = 0
      ORDER BY id DESC
      LIMIT 2
    ) p ON c.parent_id = p.id
  ) AS _ranked
WHERE _ranked._rn <= 5