join—如何在mysql中组合两个具有不同列的sql查询,而不合并它们的结果行

nwwlzxa7  于 2021-07-24  发布在  Java
关注(0)|答案(3)|浏览(372)

上下文
我正在尝试在我的网站上创建一个“feed”系统,用户可以访问他们的feed,查看他们在网站上可以做的不同事情的所有新通知。例如,在“feed”部分,用户可以看到他们关注的用户是否创建了文章,以及用户是否对文章发表了评论。我当前的feed系统只是使用两个单独的查询来获取这些信息。但是,我想将这两个查询合并为一个查询,这样用户就可以按时间顺序查看他们所关注的那些查询的活动。按照我的系统现在的工作方式,我从用户关注的每个人那里得到五篇文章,放在“feed”部分,然后得到五篇文章评论,并在“feed”部分的同一区域发布。我不想把这些查询分开,而是想把它们组合起来,这样,它们就不会看到一行五篇文章,然后一行五条评论,而是会看到按时间顺序出现的提要,不管其他用户是先创建一篇文章,然后评论,然后创建另一篇文章,还是按什么顺序,而不是总是看到相同顺序的通知。
问题
首先,如果您想重新创建表,让我向您展示我的表创建代码。首先要做的是创建一个 users 我的table 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', 'genericUser@hotmail.com', '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) ,它首先告诉我两个查询中的列数不同,所以我必须删除 idUsers 我的专栏 articlecomments 查询。这实际上让我有点接近,但它的格式不正确:

+------------+-------------------+---------------------+---------+-------------+
| 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 |
+------------+-------------------+---------------------+---------+-------------+

有什么想法吗?如果有任何困惑,请告诉我。谢谢。
Server type: MariaDB Server version: 10.4.8-MariaDB - mariadb.org binary distribution

zlwx9yxi

zlwx9yxi1#

这看起来像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 他们之间的条款。然后,我们根据created date(这是第一个查询中的别名)应用排序,以获得所需顺序的结果。
如果你使用 union ,将从结果中消除重复行。如果你使用 union all ,将保留重复行(如果存在)。 union all 速度更快,因为它结合了两个数据集(只要查询中的列相同)。 union 此外,还必须查找重复行并将其从查询中删除。

mhd8tkvw

mhd8tkvw2#

你没有提到你正在使用的mysql版本,所以我假设它是一个现代版本(MySQL8.x)。可以在每个子集上使用 ROW_NUMBER() 然后是平原 UNION ALL 会成功的。
我不明白你要的确切顺序,第四栏是什么 article_id (because it is referenced in articlecomments table) 手段。如果你详细说明,我可以相应地调整这个答案。
生成所需结果集的查询是:

select *
from ( (
  SELECT 
    a.article_id as id, a.title, a.date, 
    a.article_id, u.uidUsers,
    row_number() over(ORDER BY a.date DESC) as rn
  FROM articles a
  JOIN users u ON a.idUsers = u.idUsers 
  WHERE a.idUsers = '1' AND a.published = 'yes' 
  ORDER BY a.date DESC 
  LIMIT 5
  ) union all (
  SELECT
    c.comment_id, c.message, c.date, 
    c.article_id, u.uidUsers,
    5 + row_number() over(ORDER BY c.date DESC) as rn
  FROM articlecomments c
  JOIN users u ON c.idUsers = u.idUsers 
  WHERE c.idUsers = '1' 
  ORDER BY c.date DESC 
  LIMIT 5
  )
) x
order by rn

结果:

id  title           date                 article_id  uidUsers     rn
--  --------------  -------------------  ----------  -----------  --
 1  first article   2020-07-10 10:37:00           1  genericUser   1
 2  second article  2020-07-10 10:37:00           2  genericUser   2
 3  third article   2020-07-10 10:37:00           3  genericUser   3
 1  first message   2020-07-10 10:37:00           1  genericUser   6
 2  second message  2020-07-10 10:37:00           1  genericUser   7
 3  third message   2020-07-10 10:37:00           1  genericUser   8

请参见db<>fiddle中的运行示例。

34gzjxbg

34gzjxbg3#

你可以这样交叉连接=

select select(1) from FROM [job] WITH (NOLOCK)
WHERE MemberCode = 'pay'
    AND CampaignID = '2'

    cross  join

      select(1)
        FROM [product] WITH (NOLOCK)
        WHERE MemberCode = 'pay'
            AND CampaignID = '2'

相关问题