MySQL 连接三个表，统计并获取最后一个用户名

Question

我的 sql 查询有问题。我需要连接三个表，计算行数，获取最后一行，然后创建自定义列。我的表格示例如下:

表名称:文章

_______________________________________
idarticle     idwriter     title
---------------------------------------
1             1            Title One
2             3            Title Two
3             2            Title Three

表名称:评论

________________________________________________________________________________
idcomment     idarticle     idcommented     content          datetime
--------------------------------------------------------------------------------
1             1             2               Comment One      2015-05-15 00:00:00
2             1             3               Comment Two      2015-05-16 00:00:00
3             1             1               Comment Three    2015-05-17 00:00:00
4             2             2               Comment Four     2015-05-18 00:00:00
5             3             3               Comment Five     2015-05-19 00:00:00
6             3             2               Comment Six      2015-05-20 00:00:00

表名称:成员

_____________________
idmember     username
---------------------
1            apple
2            orange
3            banana

如何通过一个查询连接所有表、计数并获取最后评论+用户名。可能的结果是这样的:

_____________________________________________________________________________________________________________________
idarticle idwriter title       username_writer totalcomments lastcomment_id lastcomment_username lastcomment_datetime
---------------------------------------------------------------------------------------------------------------------
3         2        Title Three orange          2             2              orange               2015-05-20 00:00:00
2         3        Title Two   banana          1             2              orange               2015-05-18 00:00:00
1         1        Title One   apple           3             1              apple                2015-05-17 00:00:00

希望有人能解决我的问题。我使用 PHP 5.4、MySQL 5.5 和 MeekroDB 库，因此查询必须支持这些库。对不起我的英语不好。谢谢

Answer 1

http://sqlfiddle.com/#!9/132336/1

SELECT a.idarticle, a.idwriter , a.title, 
       m.username, 
       c.idcomment, cm.username, c.datetime
FROM article a
LEFT JOIN member m
ON a.idwriter = m.idmember
LEFT JOIN comment c
ON a.idarticle = c.idarticle
LEFT JOIN comment c1
ON a.idarticle = c1.idarticle
  AND c.datetime<c1.datetime
LEFT JOIN member cm
ON c.idcommented = cm.idmember
WHERE c1.datetime IS NULL