从类别中选择最新主题（SQL和PHP）(Select latest topic from a category (SQL and PHP))

我希望能够通过SQL查询选择发布到类别的最新主题。

我有两张桌子，我将要使用。

类别 主题

主题行知道它属于哪个类别，因为topics.topic_cat应该与categories.cat_id匹配。

我想遍历所有类别并显示发布给他们的最新主题。 它看起来像这样。

while($row = $result->fetch_assoc()) { echo $row['cat_name'] . ' Latest Post is ' . $row['topic_name']; }

这将是它执行时的样子。

category1 Latest Post is "latest post" category2 Latest Post is "latest post" category3 Latest Post is "latest post"

等等...

我可以用什么SQL查询来选择它？

这是我的桌子结构。

类别

CAT_ID cat_name cat_description

主题

topic_id TOPIC_NAME topic_date topic_cat topic_by

I want to be able to select the latest topic that was posted to a category by selecting it through an SQL query.

I have two tables that I'm going to use.

categories topics

A topic row knows what category it belongs to because topics.topic_cat should match with categories.cat_id.

I want to loop through all the categories and show the latest topic posted to them. It looks something like this.

while($row = $result->fetch_assoc()) { echo $row['cat_name'] . ' Latest Post is ' . $row['topic_name']; }

This would be an example of what it would look like if it was executed.

category1 Latest Post is "latest post" category2 Latest Post is "latest post" category3 Latest Post is "latest post"

and so on...

What SQL query could I use to select this?

Here are my table structures.

categories

cat_id cat_name cat_description

topics

topic_id topic_name topic_date topic_cat topic_by

最满意答案

更新

这是一个更有效的查询，可以获得相同的结果。 它将表现更好，因为子查询与外部查询不相关（不使用任何数据）

SELECT cat_name, topic_name, latest as topic_date FROM categories c LEFT JOIN ( SELECT topic_name, topic_cat, MAX(topic_date) latest FROM topics GROUP BY topic_cat ) as t ON c.cat_id = t.topic_cat

现场演示

你问过LEFT JOIN和JOIN ：第一个意味着如果任何类别没有主题，你仍然希望它包含在结果中。 主题名称和日期将为null 。 JOIN表示您希望结果仅包含具有匹配主题的类别。 因此，您可以根据自己的喜好更改我的任何一个查询。

原始答案

这就是你需要的：

SELECT c.cat_name, t.topic_name, t.topic_date FROM categories c LEFT JOIN topics t ON c.cat_id=t.topic_cat WHERE topic_date = ( SELECT MAX(t2.topic_date) FROM topics t2 WHERE t2.topic_cat = t.topic_cat )

此查询将仅返回每个类别的最新主题。 每个结果都有字段：

cat_name | topic_name | topic_date

一旦执行了查询，就可以按照自己的意愿运行循环：

while($row = $result->fetch_assoc()) { echo $row['cat_name'] . ' Latest Post is ' . $row['topic_name']; }

现场演示

Update

This is a more efficient query that gets you the same result. It will perform better because the subquery is uncorrelated (doesn't use any data) from the outer query

SELECT cat_name, topic_name, latest as topic_date FROM categories c LEFT JOIN ( SELECT topic_name, topic_cat, MAX(topic_date) latest FROM topics GROUP BY topic_cat ) as t ON c.cat_id = t.topic_cat

Live demo

You asked about LEFT JOIN vs JOIN: The first means that if any category has no topic, you still want it included in results. The topic name and date will just be null. JOIN means that you want results to include only categories with a matching topic. So you can alter either of my queries to your liking.

Original answer

This is what you need:

SELECT c.cat_name, t.topic_name, t.topic_date FROM categories c LEFT JOIN topics t ON c.cat_id=t.topic_cat WHERE topic_date = ( SELECT MAX(t2.topic_date) FROM topics t2 WHERE t2.topic_cat = t.topic_cat )

This query will return only the latest topic from each category. Each result has fields:

cat_name | topic_name | topic_date

Once you've executed your query, you can run your loop as you wish:

while($row = $result->fetch_assoc()) { echo $row['cat_name'] . ' Latest Post is ' . $row['topic_name']; }

Live demo