mysql - 如何统一 3 个不同的 SELECT 查询，以便它们可用于从 3 个不同的类别中分别提取 10 个值以插入到一个临时表中？

Question

有一个 latest_comments 表，每 N 分钟重写一次。内容从 posts 和 comments 表中提取并合并在一起。有3 个不同的类别(红色、绿色、蓝色)，每个类别的 10 条最新评论 应在 latest_comments 中表示。

没有统一/合并查询看起来像:

INSERT INTO `latest_comments` (`post_id`, `comment_id`, `date`, `comment`, `category`)
SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
LEFT JOIN `posts` AS p
ON c.post_id = p.id
WHERE p.category = 'red'
ORDER BY c.date DESC
LIMIT 0, 10;

INSERT INTO `latest_comments` (`post_id`, `comment_id`, `date`, `comment`, `category`)
SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
LEFT JOIN `posts` AS p
ON c.post_id = p.id
WHERE p.category = 'green'
ORDER BY c.date DESC
LIMIT 0, 10;

INSERT INTO `latest_comments` (`post_id`, `comment_id`, `date`, `comment`, `category`)
SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
LEFT JOIN `posts` AS p
ON c.post_id = p.id
WHERE p.category = 'blue'
ORDER BY c.date DESC
LIMIT 0, 10;

如何合并所有 3 个 SELECT 查询，以便只能使用 1 个 INSERT 查询，同时在性能方面尽可能优化？

我已经尝试了所有我能找到的和我能尝试的来提出我的逻辑。它们都不起作用，我认为 3 个单独的 INSERT 查询正在减慢函数的速度。例如，我希望能起作用:

INSERT INTO `latest_comments` (`post_id`, `comment_id`, `date`, `comment`, `category`)
(SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
LEFT JOIN `posts` AS p
ON c.post_id = p.id
WHERE p.category = 'red'
ORDER BY c.date DESC
LIMIT 0, 10),

(SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
LEFT JOIN `posts` AS p
ON c.post_id = p.id
WHERE p.category = 'green'
ORDER BY c.date DESC
LIMIT 0, 10),

(SELECT
p.id AS `post_id`,
c.post_id AS `comment_id`,
c.date AS `date`,
c.comment AS `comment`,
p.category AS `category`
FROM `comments` AS c
LEFT JOIN `posts` AS p
ON c.post_id = p.id
WHERE p.category = 'blue'
ORDER BY c.date DESC
LIMIT 0, 10);

Answer 1

如果你想要 10 行形成 eacj 查询你可以使用 UNION ALL

        INSERT INTO `latest_comments` (`post_id`, `comment_id`, `date`, `comment`, `category`)
        (SELECT
            p.id AS `post_id`,
            c.post_id AS `comment_id`,
            c.date AS `date`,
            c.comment AS `comment`,
            p.category AS `category`
        FROM `comments` AS c
        LEFT JOIN `posts` AS p ON c.post_id = p.id
        WHERE p.category = 'red'
        ORDER BY c.date DESC
        LIMIT 0, 10)
        UNION ALL
        (SELECT
            p.id AS `post_id`,
            c.post_id AS `comment_id`,
            c.date AS `date`,
            c.comment AS `comment`,
            p.category AS `category`
        FROM `comments` AS c
        LEFT JOIN `posts` AS p
        ON c.post_id = p.id WHERE p.category = 'green'
        ORDER BY c.date DESC LIMIT 0, 10 )
        UNION ALL
        (SELECT
            p.id AS `post_id`,
            c.post_id AS `comment_id`,
            c.date AS `date`,
            c.comment AS `comment`,
            p.category AS `category`
        FROM `comments` AS c
        LEFT JOIN `posts` AS p
        ON c.post_id = p.id WHERE p.category = 'blue'
        ORDER BY c.date DESC LIMIT 0, 10)

无论如何，您在 where 子句中使用左连接表列 WHERE p.category = 'blue' 这用作内部连接 ​​

        INSERT INTO `latest_comments` (`post_id`, `comment_id`, `date`, `comment`, `category`)
        (SELECT
            p.id AS `post_id`,
            c.post_id AS `comment_id`,
            c.date AS `date`,
            c.comment AS `comment`,
            p.category AS `category`
        FROM `comments` AS c
        INNER JOIN `posts` AS p ON c.post_id = p.id and p.category = 'red'
        ORDER BY c.date DESC
        LIMIT 0, 10)

        UNION ALL
        (SELECT
            p.id AS `post_id`,
            c.post_id AS `comment_id`,
            c.date AS `date`,
            c.comment AS `comment`,
            p.category AS `category`
        FROM `comments` AS c
        INNER JOIN `posts` AS p ON c.post_id = p.id AND p.category = 'green'
        ORDER BY c.date DESC LIMIT 0, 10)
        UNION ALL
        (SELECT
            p.id AS `post_id`,
            c.post_id AS `comment_id`,
            c.date AS `date`,
            c.comment AS `comment`,
            p.category AS `category`
        FROM `comments` AS c
        INNER JOIN `posts` AS p ON c.post_id = p.id AND p.category = 'blue'
        ORDER BY c.date DESC LIMIT 0, 10);

或者如果您只需要 30 行，您可以使用 IN 子句

        INSERT INTO `latest_comments` (`post_id`, `comment_id`, `date`, `comment`, `category`)
        SELECT
            p.id AS `post_id`,
            c.post_id AS `comment_id`,
            c.date AS `date`,
            c.comment AS `comment`,
            p.category AS `category`
        FROM `comments` AS c
        INNER JOIN `posts` AS p ON c.post_id = p.id and p.category IN ( 'red', 'green', 'blue')
        ORDER BY c.date DESC
        LIMIT 0, 30;