子查询错误中的 MySQL 引用外表别名

Question

  select c.*
    from posts p
    join comments c on (p.id=c.post_id) 
        and c.status = 'A' 
        and c.id >= (select MIN(id) from (select id from comments where post_id=p.id order by id DESC LIMIT 7) as c2)
    where p.comments_count > 0 AND p.id IN (247,245,244,243,242,241)

在上面的 sql 查询中，我得到了这个 mysql 错误 Unknown column 'p.id' in 'where clause' 似乎在子查询中 p.id 无法被引用。无论如何，我可以获得此子查询中引用的帖子表 ID 吗？

Answer 1

我认为您正在尝试为每个帖子获取最新的 7 条评论。你能试试这个吗？你可以在这里测试 http://www.sqlfiddle.com/#!2/a222e/3/0

第一次尝试

我尝试了以下 SQL。

SELECT *
FROM comments t1
WHERE post_id IN (247,254,244,243,242,241)
AND id IN (
    SELECT id
    FROM comments
    WHERE t1.id = id
    LIMIT 7
);

但是我得到一个错误“这个版本的 MySQL 还不支持‘LIMIT & IN/ALL/ANY/SOME subquery’

另一种方法

因此，我尝试在评论中自行加入以生成序列号。

SELECT id
FROM (
    SELECT t1.id, COUNT(*) AS cnt
    FROM comments t1 INNER JOIN comments t2
      ON t1.post_id = t2.post_id
    WHERE t1.id <= t2.id
      AND t1.post_id IN (247,254,244,243,242,241)
      AND t2.post_id IN (247,254,244,243,242,241)
    GROUP BY t1.id
) x
WHERE cnt <= 7;

内部子查询 使用自连接并生成 cnt 列，该列具有帖子的每个评论 ID 的顺序值。

但前面的查询只获取评论的id

最后要获取评论表的所有列，应执行以下查询。

SELECT *
FROM comments c INNER JOIN (
    SELECT id
    FROM (
        SELECT t1.id, COUNT(*) AS cnt
        FROM comments t1 INNER JOIN comments t2
          ON t1.post_id = t2.post_id
        WHERE t1.id <= t2.id
          AND t1.post_id IN (247,254,244,243,242,241)
          AND t2.post_id IN (247,254,244,243,242,241)
        GROUP BY t1.id
    ) x
    WHERE cnt <= 7
) t USING (id);

使用用户变量

实际上，您还有另一个机会使用 MySQL 用户变量。我没有提到这个有趣的 MySQL 特性，因为我不确定我是否正确理解了你的问题。

SELECT * 
FROM (
    SELECT post_id, id,
        IF (@pid = post_id, @cnt := @cnt + 1, @cnt := 1) AS cnt,
        @pid := post_id
    FROM comments, (SELECT @pid := 0, @cnt := 0) tmp
    WHERE post_id IN (247,254,244,243,242,241)
    ORDER BY post_id, id DESC
) x
WHERE cnt <= 7;

之前的 SQL 看起来比旧的连接版本更简单(意味着性能好)。但未在大数据集上进行测试。