logo标签列表

MYSQL连接两个表并通过日期限制第二个表的结果

mysqlsqlselectjoin
6
我正在尝试使用MYSQL查询从两个表中检索数据。我想要将它们连接起来，其中categories.cat_id = topics.topic_cat。多个条目可能具有相同的topic_cat，因此我只想选择最近的一个，这等于MAX(topic_date)。
以下查询显示了正确的topics信息，每个topic_cat仅有一个结果，并且该结果具有最新日期。
SELECT topic_subject, topic_cat, topic_date
FROM topics
GROUP BY topic_cat DESC

多行可能有相同的topic_cat值，但我只想检索和连接最近的MAX(topic_date)，然后连接到一个查询中，该查询显示来自categories表的以下信息。

SELECT categories.cat_id, categories.cat_name, categories.cat_description, topics.topic_subject, topics.topic_cat, topics.topic_date, topics.topic_by
FROM categories
LEFT JOIN topics
ON categories.cat_id=topics.topic_cat
GROUP BY cat_id;

这个查询显示了正确的信息，除了一件事。它显示了topic_cat中最旧的条目或者MIN(topic_date)。我尝试过以下方法来获取topic_cat中最新的条目或者MAX(topic_date)，但是没有成功。

SELECT categories.cat_id, categories.cat_name, categories.cat_description
FROM categories
LEFT JOIN (SELECT topic_subject, topic_cat, topic_date, topic_by
FROM topics
GROUP BY topic_cat DESC) AS topics
ON categories.cat_id=topics.topic_cat

希望能得到任何帮助或建议，将不胜感激。
好的，这里提供示例数据和相关期望结果。
表1 = 类别。
_______________________________________________________
| cat_id | cat_name      | cat_description             |
-------------------------------------------------------
| 1      |  james        | Some information about james|
-------------------------------------------------------
| 2      |  myo          | Some information about myo  |
-------------------------------------------------------
| 3      |  brandon      | Some information about brandon  |
-------------------------------------------------------

表格2 = 主题

__________________________________________________
| topic_subject |  topic_cat  |  topic_date  |   topic_by  |
----------------------------------------------------------
| marcos        |  2          | 2013-9-28  |  User 1       |
---------------------------------------------------------
| ferdinand     |  2          | 2013-9-29  |  User 2       |
---------------------------------------------------------
|  maria luisa  |  2          | 2013-9-30  |  User 1       |
---------------------------------------------------------
|  Isabella     |  1          | 2013-8-24   |  User 3      |
--------------------------------------------------------
| Carlos        |  3          |  2012-6-21   |  User 2     |
--------------------------------------------------------
|  Enrique      |  3          |  2011-4-2   |  User 3      |
---------------------------------------------------------

我希望查询基于上述表格返回以下与数据相关的内容：
_________________________________________________________________________________________________
| cat_id | cat_name      | cat_description                |  topic_subject |  topic_cat  |  topic_date  |   topic_by        |
----------------------------------------------------------------------------------------------------------------
|  1     |  james        | Some information about james   |   Isabella     |  1          | 2013-8-24    |  User 3          |
----------------------------------------------------------------------------------------------------------------
|  2     |  myo          | Some information about myo     |  maria luisa   |  2          | 2013-9-30    |  User 1           | 
----------------------------------------------------------------------------------------------------------------
|  3     |  brandon      | Some information about brandon | Carlos         |  3          |  2012-6-21   |  User 2         |
----------------------------------------------------------------------------------------------------------------

我希望你能理解这些内容。
1
请提供样本数据和相关的期望结果。 - PM 77-1
尝试使用 LIMIT 子句。 - Mark
我可以限制根据最近的日期吗？ - Jimmy Long
2个回答
5

试试这个：

###
SELECT * FROM categories c
LEFT JOIN topics t ON c.cat_id = t.topic_cat
WHERE c.cat_id IN (SELECT t1.cat_id FROM (
    SELECT c.cat_id, c.cat_name, MAX(t.topic_date) AS maxdate FROM categories c
    LEFT JOIN topics t ON c.cat_id = t.topic_cat
    GROUP BY c.cat_name
) as t1 WHERE t1.maxdate = t.topic_date OR t.topic_date IS NULL );

### without nulls
SELECT * FROM categories c
    LEFT JOIN topics t ON c.cat_id = t.topic_cat
    WHERE c.cat_id IN (SELECT t1.cat_id FROM (
    SELECT c.cat_id, c.cat_name, MAX(t.topic_date) AS maxdate FROM categories c
    LEFT JOIN topics t ON c.cat_id = t.topic_cat
    GROUP BY c.cat_name
) as t1 WHERE t1.maxdate = t.topic_date);
2

尝试更改

LEFT JOIN (SELECT topic_subject, topic_cat, topic_date, topic_by
FROM topics
GROUP BY topic_cat DESC) AS topics

to:

LEFT JOIN (SELECT topic_subject, topic_cat, topic_date, topic_by
FROM topics
GROUP BY topic_cat
ORDER BY topic_date DESC
LIMIT 0,1) AS topics
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接