给定两个表，请从一个表​​中选择所有数据，仅从另一个表中选择最新数据 [英] Given two tables, select all data from one table and only the most recent from the other

问题描述

我正在尝试建立一个使用PHP和MySQL数据库存储其类别和主题内容的论坛网站.在主页上，我想要一个表格，其中显示所有类别的列表以及每个类别中最新发布的主题.

I'm trying to build a forum website that uses PHP and a MySQL database to store its category and topic content. On the homepage, I want to have a table that shows a list of all the categories and the most recent posted topic from each category.

我想编写一个查询，该查询返回类别表中的所有类别，并且仅返回主题表中每个类别的最新主题.

I want to write a query that returns all of the categories from the category table and only the most recent topics for each category from the topics table.

我的表格如下:

类别

+--------+-------------+--------------------------+
| cat_id |  cat_name   |     cat_description      |
+--------+-------------+--------------------------+
|      1 | Automobiles | *Tim Taylor manly grunt* |
|      2 | English     | How to English           |
|      3 | Gardening   | Lawn and Order           |
+--------+-------------+--------------------------+

主题

+----------+-----------------------+------------------------+-----------+----------+
| topic_id |      topic_name       |     topic_content      | topic_cat | topic_by |
+----------+-----------------------+------------------------+-----------+----------+
|        1 | '67 Chevy Question    | Weird knocking noise?  |         1 |        1 |
|        2 | You're vs Your        | What's the difference? |         2 |        3 |
|        3 | Jumper cables?        | The proper hookup      |         1 |        2 |
|        4 | '03 Pathfinder        | Next newest model?     |         1 |        1 |
|        5 | There, Their, They're | Know the difference    |         2 |        4 |
+----------+-----------------------+------------------------+-----------+----------+

我在技巧3下的 https://stackoverflow.com/a/12586263/7249891 上找到了一个相关的答案，但经过几个小时的摆弄，无法将其归结为对我有用的查询.

I found a relevant answer on https://stackoverflow.com/a/12586263/7249891 under Trick #3, but after a couple of hours of fiddling, am unable to boil it down to a query that works for me.

我的问题是，如何调整原始查询

My question is, how do I adjust my original query

SELECT c.cat_name AS Category, t.topic_cat AS Recent Topic 
FROM categories c
JOIN topics t
WHERE c.cat_id = t.topic_cat

因此它返回数据库中的所有类别，但仅返回每个类别中的最新主题，结果与此类似.

so it returns all the categories in the database, but only the most recent topic from each category in a result similar to this

+-------------+-----------------------+
|  Category   |     Recent Topic      |
+-------------+-----------------------+
| Automobiles | '03 Pathfinder        |
| English     | There, Their, They're |
| Gardening   | NULL                  |
+-------------+-----------------------+

说明: 在此论坛中，管理员创建了几个类别，任何用户都可以在其中发布主题.

Clarifications: In this forum, there are several categories created by admins that any user can post a topic in.

在主题中，主题主题是用户提出的问题，主题内容是有关该问题的其他信息.

In a topic, the topic subject is a question asked by a user and the topic content is additional information about that question.

Cat_id和topic_id都是自动递增的主键.

Cat_id and topic_id are both auto incrementing primary keys.

Topic_subject是引用cat_id的外键.

Topic_subject is a foreign key that references cat_id.

假定由于主键行为，主题表中的最新主题是topic_id编号最高的主题.该表中还有一个日期字段(我意识到最后一刻我忘了在此处添加日期).

Assume that the most recent topic in the topics table is the one with the highest topic_id number because of the primary key behavior. There is also a date field in this table (which I realized last minute I forgot to include here).

我在这里未列出的其他两个表是:用户和回复表. Topic_by是引用用户表的外键.

There are two other tables I didn't list here: a users and replies table. Topic_by is a foreign key that references the users table.

如果类别(在我的上例中为园艺类别)中没有主题，我们将假定程序的PHP部分将使列表的该部分显示为(无)".

If there are no topics in a category (the gardening category in my above example), we'll assume the PHP portion of the program will make that part of the list say "(none)".

推荐答案

首先找到每个类别中的最新帖子:

First find the latest post in each category:

select topic_cat, max(topic_id) as latest_topic
from topics group by topic_cat

然后将其添加到您的加入条件中:

Then add that to your join conditions:

SELECT  c.cat_name AS Category, t.topic_name AS Recent_Topic 
FROM categories c
left JOIN topics t on c.cat_id = t.topic_cat 
left join (select topic_cat, max(topic_id) as latest_topic
        from topics group by topic_cat) as latest_topics 
        on latest_topics.topic_cat = c.cat_id
        and latest_topics.latest_topic = t.topic_id 
where latest_topics.topic_cat is not null or t.topic_cat is null;

这篇关于给定两个表，请从一个表​​中选择所有数据，仅从另一个表中选择最新数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！