php - 请协助我建立这个查询

Question

我想按 rls_id 提取组行，但使用最新/最近的 date

SELECT * 
FROM `tbl_revisions` 
WHERE `date` IN (SELECT MAX(`date`) 
                 FROM `tbl_revisions` 
                 GROUP BY `rls_id`) 
GROUP BY `rls_id`

上面的查询运行良好，但我不想使用子查询。我需要其他方法。

CREATE TABLE IF NOT EXISTS `tbl_revisions` 
(
  `id` int(21) NOT NULL AUTO_INCREMENT,
  `rls_id` int(21) NOT NULL,
  `date` datetime NOT NULL,
  `user` int(21) NOT NULL,
  `data` blob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=66 ;

编辑:需要更快的方式

好的。感谢@Bill Karwin 和@OMG Ponies，我得到了 2 个工作查询。

我在这里粘贴了两个查询的解释，这样其他人会学得更好

Bill Karwin :

SELECT r1.*
FROM `tbl_revisions` r1
LEFT OUTER JOIN `tbl_revisions` r2
  ON (r1.`rls_id` = r2.`rls_id` AND r1.`date` < r2.`date`)
WHERE r2.`rls_id` IS NULL;

---

OMG Ponies:

SELECT t.* 
  FROM TBL_REVISIONS t
  JOIN (SELECT rls_id,
               MAX(date) AS max_date
          FROM TBL_REVISIONS
      GROUP BY rls_id) x ON x.rls_id = t.rls_id
                        AND x.max_date = t.date

Answer 1

不使用子查询？确定:

SELECT t.* 
  FROM TBL_REVISIONS t
  JOIN (SELECT rls_id,
               MAX(date) AS max_date
          FROM TBL_REVISIONS
      GROUP BY rls_id) x ON x.rls_id = t.rls_id
                        AND x.max_date = t.date

有些人可能将其称为子选择，但 x 更准确地称为派生表或内联 View 。子选择通常是 SELECT 子句本身内的 SELECT 语句，例如:

SELECT ...,
       (SELECT COUNT(*)...)

无论如何，检查标签“greatest-n-per-group”以获取其他各种示例。