sql - 获取选择查询中表的最后一条记录

这是我对getting-the-last-record-inserted-into-a-select-query遇到的另一个问题的跟进

我正在尝试编辑一个查询，该查询对Andrea足够好，可以在昨天为我提供帮助，该查询在一页上效果很好，但是我正在尝试创建一个类似的查询，但运气不佳。

我需要为每个面板显示面板名称，链接到该面板的主题和消息数以及用户，最后一条消息的主题和日期（有效）

我需要获取板名，主题和消息数

这是我的表结构

CREATE TABLE `boards` (
  `boardid` int(2) NOT NULL auto_increment,
  `boardname` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`boardid`)
);

CREATE TABLE `messages` (
  `messageid` int(6) NOT NULL auto_increment,
  `topicid` int(4) NOT NULL default '0',
  `message` text NOT NULL,
  `author` varchar(255) NOT NULL default '',
  `date` datetime(14) NOT NULL,
  PRIMARY KEY  (`messageid`)
);

CREATE TABLE `topics` (
  `topicid` int(4) NOT NULL auto_increment,
  `boardid` int(2) NOT NULL default '0',
  `topicname` varchar(255) NOT NULL default '',
  `author` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`topicid`)
);

以及我根据Andrea为我所做的查询提出的查询。该查询在板名中输出的内容，主题和消息的数量（即使有5个，也显示1），主题作者和消息数（不需要），最后发布的作者和日期（需要） ），但不需要主题名称

SELECT b.boardname, count( DISTINCT t.topicname ) AS topics, count( lm.message ) AS message, t.author as tauthor,

      (select count(message) from messages m where m.topicid = t.topicid) AS messagecount,
       lm.author as lauthor, lm.date
  FROM topics t
  INNER JOIN messages lm
    ON lm.topicid = t.topicid AND lm.date = (SELECT max(m2.date) from messages m2)
  INNER JOIN boards b
    ON b.boardid = t.boardid
  GROUP BY t.topicname

这是我的原始查询，可完成我想要的，但得到第一篇文章，而不是最后一篇文章

SELECT b.boardid, b.boardname, count( DISTINCT t.topicname ) AS topics, count( m.message ) AS message, m.author AS author, m.date AS date, t.topicname AS topic
FROM boards b
INNER JOIN topics t ON t.boardid = b.boardid
INNER JOIN messages m ON t.topicid = m.topicid
INNER JOIN (

SELECT topicid, MAX( date ) AS maxdate
FROM messages
GROUP BY topicid
) test ON test.topicid = t.topicid
GROUP BY boardname
ORDER BY boardname

任何帮助与赞赏

最佳答案

SELECT  b.*, m.*, t,*
        (
        SELECT  COUNT(*)
        FROM    topics ti
        WHERE   ti.boardid = b.boardid
        ) AS topiccount,
        (
        SELECT  COUNT(*)
        FROM    topics ti, messages mi
        WHERE   ti.boardid = b.boardid
                AND mi.topicid = ti.topicid
        ) AS messagecount
FROM    boards b
LEFT JOIN
        messages m
ON      m.messageid = (
        SELECT  mii.messageid
        FROM    topics tii, messages mii
        WHERE   tii.boardid = b.boardid
                AND mii.topicid = tii.topicid
        ORDER BY
                mii.date DESC
        LIMIT 1
        )
LEFT JOIN
        topics t
ON      t.topicid = m.topicid