sql - SQL LEFT JOIN问题

我正在尝试选择最新的updated（field）pages（table），不包括隐藏的页面（visible=“n”）。但是，我想按slug对页面进行分组，所以每个slug只能得到一个结果。
下面的查询有点工作，但它只为每个slug选择最新更新的页面—然后，如果该页面碰巧被隐藏，它会从结果中删除它：（在本例中，我只想获取带有可见slug的最新更新页面。

SELECT `p1`.`id` AS `pID`, `p1`.`slug` AS `url`, `p1`.`revision`, `p1`.`title`, `p1`.`published`, `p1`.`updated`, (SELECT COUNT(*) FROM `pages` WHERE `slug` = `url` AND `visible` = "y") AS `revisionCount`, (SELECT COUNT(*) FROM `tests` WHERE `pageID` = `pID`) AS `testCount`
FROM `pages` `p1`
LEFT JOIN `pages` `p2` ON `p1`.`slug` = `p2`.`slug` AND `p1`.`updated` < `p2`.`updated`
WHERE `p2`.`updated` IS NULL AND `p1`.`visible` = "y"
ORDER BY `p1`.`updated` DESC

最佳答案

这应该有效：

SELECT id AS pID, slug AS url, revision, title, published, updated, (SELECT COUNT(*) FROM pages WHERE slug = url AND visible = "y") AS revisionCount, (SELECT COUNT(*) FROM tests WHERE pageID = pID) AS testCount
FROM pages
WHERE updated IN (SELECT MAX(updated) FROM pages WHERE visible = "y" GROUP BY slug) AND visible = "y"
ORDER BY updated DESC

P.S.所有的筷子是什么？