mysql - 连接两个表，按列分组并获取所有列

Question

**更新:这是架构:http://sqlfiddle.com/#!9/fd6447

我有 2 张 table :

帖子:

-------------------------------------------------------
|   post_id  | post_creator_id  |      post_title     |  
-------------------------------------------------------
|    1       |        100       |   Hello All         |
-------------------------------------------------------
|    2       |        14        |   Good morning      |  
-------------------------------------------------------
|    3       |        213       |   Lovely Day        |
-------------------------------------------------------
|    4       |        55        |   Nice Title!       |
-------------------------------------------------------

评论:

------------------------------------------------------------------------------
| comment_id | post_id  | commenter_id | comment_text |       date           |
------------------------------------------------------------------------------
|    8       |    1     |      98      | Hello world  |  2018-04-27 12:02:22 |
------------------------------------------------------------------------------
|    9       |    4     |      123     |    Hi all    |  2018-04-27 13:11:11 |
------------------------------------------------------------------------------
|    10      |    4     |      77      | Looking good |  2018-04-27 13:20:17 |
------------------------------------------------------------------------------
|    11      |    1     |      101     | Great idea   | 2018-04-27 14:45:15  |
------------------------------------------------------------------------------

在最终结果中，我希望获得每个帖子的最后评论(comment_text)，以及有关该帖子的一些信息(post_title、post_creator_id)。以及评论的日期(而不是帖子的日期 - 这样我就可以按最后评论日期对其进行排序)所以在上面的例子中，结果应该是

-----------------------------------------------------------------------------------------------------------------------
|  comment_id | post_id | commenter_id|  comment_text  | post_creator_id  |      post_title     |       date          |
-----------------------------------------------------------------------------------------------------------------------
|     10      |   4     |     77      |  Looking good  |       55         |    Nice Title!      | 2018-04-27 13:20:17 | 
-----------------------------------------------------------------------------------------------------------------------
|     11      |   1     |    101      |   Great idea   |      100         | Hello All           | 2018-04-27 14:45:15 |
-----------------------------------------------------------------------------------------------------------------------

所以我能做的就是加入带有评论的帖子，但我不知道如何添加附加信息(post_title 和 post_creator_id)。

这是我的查询:

select a.*
from comments a
join (
    select post_id, max(date_entered) as date_entered
    from comments
    group by (post_id)
    ) b on a.post_id = b.post_id and a.date_entered = b.date_entered

给出:

comment_id    post_id     commenter_id   comment_text         date_entered
----------- -----------   -----------   -------------------- -----------------------
   10          4              77             Looking good     2018-04-27 13:20:17.000
   11          1              101            Great idea       2018-04-27 14:45:15.000

但是我缺少“post_title”和“post_creator_id”列，并且我不知道如何进行另一个连接来添加它们？正确的语法是什么？

谢谢!

Answer 1

您只需将 posts 表加入到您的(略有损坏，现已更正)查询中:

SQL Fiddle

MySQL 5.6 架构设置:

Create table posts(
    post_id  int,
    post_creator_id int,   
  post_title  varchar(100)  
);

INSERT INTO posts VALUES (1, 100, 'Hello All');
INSERT INTO posts VALUES (2, 14,'Good morning');
INSERT INTO posts VALUES (3, 213, 'Lovely Day');
INSERT INTO posts VALUES (4, 55, 'Nice Title!');

create table comments(
    comment_id int,
    post_id int,
    commenter_id int,
    comment_text  varchar(100),
    date  datetime
);

insert into comments values (8 , 1, 98, 'Hello world', '2018-04-27 12:02:22' );
insert into comments values (9 , 4, 123, 'Hi all', '2018-04-27 13:11:11' );
insert into comments values (10 , 4, 77, 'Looking good', '2018-04-27 13:20:17' );
insert into comments values (11 , 1, 101, 'Great idea', '2018-04-27 14:45:15' );

查询 1:

select a.*, p.post_title, p.post_creator_id
from comments a
join (
  select post_id, max(date) as date_entered
  from comments
  group by (post_id)
  ) b on a.post_id = b.post_id and a.date = b.date_entered
join posts p on p.post_id = b.post_id

<强> Results :

| comment_id | post_id | commenter_id | comment_text |                 date |  post_title | post_creator_id |
|------------|---------|--------------|--------------|----------------------|-------------|-----------------|
|         11 |       1 |          101 |   Great idea | 2018-04-27T14:45:15Z |   Hello All |             100 |
|         10 |       4 |           77 | Looking good | 2018-04-27T13:20:17Z | Nice Title! |              55 |