sql - 如何在 MySQL 中组合两个具有不同列的 SQL 查询而不组合其结果行

Question

上下文

我正在尝试在我的网站上创建一个“提要”系统，用户可以在其中访问他们的提要并查看他们可以在网站上执行的不同操作的所有新通知。例如，在“提要”部分，用户可以查看他们关注的用户是否创建了文章以及用户是否对文章发表了评论。我当前的提要系统仅使用两个单独的查询来获取此信息。但是，我想将这两个查询合并为一个，以便用户可以按时间顺序查看他们关注的人的事件。我的系统现在的工作方式是，我从用户关注的每个人那里获取五篇文章，并将其放入“提要”部分，然后获取五篇文章评论并将其发布在“提要”部分的同一区域中。我不想将查询分开，而是将它们组合起来，这样他们就不会看到连续的五篇文章帖子和连续的五篇文章评论，而是会看到按时间顺序发生的提要帖子，无论其他用户是否创建先一篇文章，然后评论，然后创建另一篇文章，或者无论顺序是什么，而不是总是看到相同的通知顺序。

问题

首先，如果您想重新创建表，请让我向您展示我的表创建代码。首先要做的是创建一个 users 表，我的 articles 和 articlecomments 表引用它:

CREATE TABLE users (
    idUsers int(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
    uidUsers TINYTEXT NOT NULL,
    emailUsers VARCHAR(100) NOT NULL,
    pwdUsers LONGTEXT NOT NULL,
    created DATETIME NOT NULL, 
    UNIQUE (emailUsers),
    FULLTEXT(uidUsers)
) ENGINE=InnoDB;

接下来，让我们创建 articles 表:

CREATE TABLE articles (
    article_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    title TEXT NOT NULL,
    article TEXT NOT NULL,
    date DATETIME NOT NULL,
    idUsers int(11) NOT NULL,
    topic VARCHAR(50) NOT NULL,
    published VARCHAR(50) NOT NULL,
    PRIMARY KEY (article_id),
    FULLTEXT(title, article),
    FOREIGN KEY (idUsers) REFERENCES users (idUsers) ON DELETE CASCADE ON UPDATE 
CASCADE
) ENGINE=InnoDB;

最后，我们需要 articlecomments 表:

CREATE TABLE articlecomments (
    comment_id INT(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
    message TEXT NOT NULL,
    date DATETIME NOT NULL,
    article_id INT(11) UNSIGNED NOT NULL,
    idUsers INT(11) NOT NULL,
    seen TINYTEXT NOT NULL,
    FOREIGN KEY (article_id) REFERENCES articles (article_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (idUsers) REFERENCES users (idUsers) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

为了为本示例充分填充表格，我们将使用以下语句:

INSERT INTO users (uidUsers, emailUsers, pwdUsers, created) VALUES ('genericUser', '<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="781f1d161d0a111b2d0b1d0a3810170c15191114561b1715" rel="noreferrer noopener nofollow">[email protected]</a>', 'password', NOW());

INSERT INTO articles (title, article, date, idUsers, topic, published) VALUES ('first article', 'first article contents', NOW(), '1', 'other', 'yes');
INSERT INTO articles (title, article, date, idUsers, topic, published) VALUES ('second article', 'second article contents', NOW(), '1', 'other', 'yes');
INSERT INTO articles (title, article, date, idUsers, topic, published) VALUES ('third article', 'third article contents', NOW(), '1', 'other', 'yes');

INSERT INTO articlecomments (message, date, article_id, idUsers, seen) VALUES ('first message', NOW(), '1', '1', 'false');
INSERT INTO articlecomments (message, date, article_id, idUsers, seen) VALUES ('second message', NOW(), '1', '1', 'false');
INSERT INTO articlecomments (message, date, article_id, idUsers, seen) VALUES ('third message', NOW(), '1', '1', 'false');

我用来从 articles 和 articlecomments 表获取数据的两个查询如下:

查询1:

SELECT 
  articles.article_id, articles.title, articles.date, 
  articles.idUsers, users.uidUsers 
FROM articles 
JOIN users ON articles.idUsers = users.idUsers 
WHERE articles.idUsers = '1' AND articles.published = 'yes' 
ORDER BY articles.date DESC 
LIMIT 5

查询2:

SELECT
   articlecomments.comment_id, articlecomments.message,
   articlecomments.date, articlecomments.article_id, users.uidUsers 
FROM articlecomments 
JOIN users ON articlecomments.idUsers = users.idUsers 
WHERE articlecomments.idUsers = '1' 
ORDER BY articlecomments.date DESC 
LIMIT 5

我如何组合这两个包含不同信息和列的查询，以便它们根据创建日期(articles.date 和 articlecomments.date)进行排序，分别)？我希望它们位于不同的行中，而不是同一行中。因此，应该就像我分别查询它们并将结果行简单地组合在一起。如果有三篇文章和三篇文章评论，我希望总共返回六行。

这就是我想要的样子。假设有三篇文章和三篇文章评论，并且评论是在文章之后创建的，这就是组合上面的查询后的结果应该是什么样的(我不确定这种描述是否可能给定不同的列名称，但我我想知道是否可以完成类似的事情):

+-------------------------------+-------------------+---------------------+----------------------------------------------------------------+---------+-------------+
| id (article_id or comment_id) |   title/message   |        date         | article_id (because it is referenced in articlecomments table) | idUsers |  uidUsers   |
+-------------------------------+-------------------+---------------------+----------------------------------------------------------------+---------+-------------+
|                             1 | first message     | 2020-07-07 11:27:15 |                                                              1 |       1 | genericUser |
|                             2 | second message    | 2020-07-07 11:27:15 |                                                              1 |       1 | genericUser |
|                             3 | third message     | 2020-07-07 11:27:15 |                                                              1 |       1 | genericUser |
|                             2 |    second article | 2020-07-07 10:47:35 |                                                              2 |       1 | genericUser |
|                             3 |    third article  | 2020-07-07 10:47:35 |                                                              3 |       1 | genericUser |
|                             1 |    first article  | 2020-07-07 10:46:51 |                                                              1 |       1 | genericUser |
+-------------------------------+-------------------+---------------------+----------------------------------------------------------------+---------+-------------+

我尝试过的事情

我读到这可能涉及 JOIN 或 UNION 运算符，但我不确定如何在这种情况下实现它们。我确实尝试通过简单地使用 (Query 1) UNION (Query 2) 来组合这两个查询，它首先告诉我两个查询中的列数不同，因此我必须删除我的 articlecomments 查询中的 idUsers 列。这实际上让我有点接近，但它的格式不正确:

+------------+-------------------+---------------------+---------+-------------+
| article_id |       title       |        date         | idUsers |  uidUsers   |
+------------+-------------------+---------------------+---------+-------------+
|          2 | first message     | 2020-07-07 10:47:35 |       1 | genericUser |
|          3 | third article     | 2020-07-07 10:47:35 |       1 | genericUser |
|          1 | first article     | 2020-07-07 10:46:51 |       1 | genericUser |
|          1 |    second article | 2020-07-07 11:27:15 |       1 | genericUser |
|          2 |    third article  | 2020-07-07 11:27:15 |       1 | genericUser |
|          3 |    first article  | 2020-07-07 11:27:15 |       1 | genericUser |
+------------+-------------------+---------------------+---------+-------------+

有什么想法吗？如果有任何困惑，请告诉我。谢谢。

服务器类型:MariaDB

服务器版本:10.4.8-MariaDB - mariadb.org 二进制发行版

Answer 1

这看起来像MySQL。你可以这样做:

select * from (SELECT articles.article_id as id_article_comment, articles.title as title_message, articles.date as created, 'article' AS contenttype, articles.article_id as article_id, articles.idUsers, users.uidUsers FROM articles JOIN users ON articles.idUsers = users.idUsers WHERE articles.idUsers = '1' AND articles.published = 'yes' ORDER BY articles.date DESC LIMIT 5) a
union all
select * from (SELECT articlecomments.comment_id, articlecomments.message, articlecomments.date, 'article comment' AS contenttype, articlecomments.article_id, articlecomments.idUsers, users.uidUsers FROM articlecomments JOIN users ON articlecomments.idUsers = users.idUsers WHERE articlecomments.idUsers = '1' ORDER BY articlecomments.date DESC LIMIT 5) b
order by created DESC

请参阅此处的示例:https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=26280a9c1c5f62fc33d00d93ab84adf3

结果如下:

id_article_comment | title_message  | created             | article_id | uidUsers   -----------------: | :------------- | :------------------ | ---------: | :----------                 1 | first article  | 2020-07-09 05:59:18 |          1 | genericUser                 2 | second article | 2020-07-09 05:59:18 |          1 | genericUser                 3 | third article  | 2020-07-09 05:59:18 |          1 | genericUser                 1 | first message  | 2020-07-09 05:59:18 |          1 | genericUser                 2 | second message | 2020-07-09 05:59:18 |          1 | genericUser                 3 | third message  | 2020-07-09 05:59:18 |          1 | genericUser

说明

由于我们要使用 order by 和 limit，因此我们将从第一行创建一个子查询，并从第一个子查询中选择所有列。我们将按照我们想要的输出方式命名每个字段。

我们对第二个查询执行相同的操作，并在它们之间添加一个 union all 子句。然后，我们根据创建日期(这是第一个查询中的别名)应用排序，以按照您想要的顺序获得您想要的结果。

如果您使用union，重复的行将从结果中消除。如果您使用union all，重复的行(如果存在)将被保留。 union all 速度更快，因为它组合了 2 个数据集(只要查询中的列相同)。此外，union 还必须查找重复行并将其从查询中删除。