PHP精简SQL查询：无嵌套查询获取各Category最新Article

Got it, let's tackle this problem. You want to fetch the most recent article for each category without using nested queries, while keeping SQL query count to a minimum. Here's a straightforward, efficient approach using only joins:

SQL Query #

SELECT 
    c.Id AS CategoryId,
    c.Name AS CategoryName,
    c.Thumbnail,
    a.Id AS ArticleId,
    a.Title,
    a.Content,
    a.CreatedOn
FROM CATEGORY c
JOIN ARTICLE a ON c.Id = a.CategoryId
LEFT JOIN ARTICLE a2 
    ON a.CategoryId = a2.CategoryId 
    AND a.CreatedOn < a2.CreatedOn
WHERE a2.Id IS NULL;

How This Works #

• Join Categories and Articles: The initial JOIN links each category to all its associated articles, giving us a full list of category-article pairs.
• Left Join to Find Newer Articles: By joining the ARTICLE table again (aliased as a2), we check for any article in the same category that was created later than the current article (a).
• Filter for Latest Articles: The WHERE a2.Id IS NULL condition keeps only those articles where no newer article exists in the same category—these are exactly the latest articles per category.

Key Notes #

• Handling Ties: If multiple articles in a category share the same latest CreatedOn timestamp, this query will return all of them. To pick just one (e.g., the one with the highest article ID), modify the left join condition to:

  LEFT JOIN ARTICLE a2 
      ON a.CategoryId = a2.CategoryId 
      AND (a.CreatedOn < a2.CreatedOn OR (a.CreatedOn = a2.CreatedOn AND a.Id < a2.Id))
  

• Performance Boost: Add indexes on ARTICLE.CategoryId and ARTICLE.CreatedOn to speed up the join and filtering operations—this is crucial for large datasets.

内容的提问来源于stack exchange，提问作者geeknoob