SQL：从其他表中分组并反转结果 [英] SQL: group by from other table and invert result
问题描述
我的SQL查询有一些问题
我有表消息和表收件人
消息是
ID作者日期
--------------------
0 1 2013-07-08 05:38:47
1 1 2013-07-13 05:38:47
2 1 2013-07-15 05:38:47
3 1 2013-07-15 05:38:47
4 2 2013 -07-17 05:38:47
5 1 2013-07-28 05:38:47
收件人是
ID m_id收件人
--------------------
0 0 2
1 1 2
2 2 3
3 3 2
4 4 1
5 5 2
我需要返回行从表消息与 收件人列从讯息表格
我会试试这个
SELECT m。*
FROM message as m
INNER JOIN收件人为r ON（m.ID = r.m_id）
WHERE m.author = 1
GROUP BY r.recipient
ORDER BY m.ID DESC
返回是
ID作者日期
------------ --------
2 1 2013-07-15 05:38:47
0 1 2013-07-08 05:38:47
但我需要
ID作者date
--------------------
5 1 2013-07-28 05:38:47
2 1 2013-07-15 05:38:47
请帮忙
我使用MySQL Server 5.1
我找到了解决我的问题的方案
SELECT m。*
FROM（
SELECT * FROM收件人
WHERE 1 = 1
ORDER BY recipient.ID DESC
）AS r
INNER JOIN消息AS m ON（r.m_id = m.ID）
WHERE m.author = 1
GROUP BY r.recipient
只是反转表收件人
<使用 DISTINCT ON 的 PostgreSQL 非常简单快捷 - 非标准SQL，因此在每个RDBMS中都不可用。
这个问题没有提到它，但是从代码示例派生出来，它实际上是为每个给定的<$ c $寻找每个收件人 的last date C>作者 的。
SELECT DISTINCT ON（r.recipient）m。*
FROM message m
JOIN收件人r ON r.m_id = m.id
WHERE m.author = 1
ORDER BY r.recipient，m.date DESC，r.m_id - 打破关系
详细信息以及多个SQL标准备选方案在这里：
在每个GROUP BY组中选择第一行？
另一个解决方案使用基本的标准SQL。适用于每个主要的RDBMS，包括 MySQL （自从添加了该标签以来）：
SELECT m 。*
FROM消息m
JOIN收件人r ON r.m_id = m.id
WHERE m.author = 1
AND NOT EXISTS（
SELECT 1
FROM消息m1
JOIN收件人r1 ON r1.m_id = m1.id
WHERE r1.recipient = r.recipient
AND m1.author = 1
AND m1.date > m.date
）
只有具有最新日期的行通过不存在反半连接。
I have some problem with my SQL query
I have table message and table recipient
message is
ID author date
--------------------
0 1 2013-07-08 05:38:47
1 1 2013-07-13 05:38:47
2 1 2013-07-15 05:38:47
3 1 2013-07-15 05:38:47
4 2 2013-07-17 05:38:47
5 1 2013-07-28 05:38:47
recipient is
ID m_id recipient
--------------------
0 0 2
1 1 2
2 2 3
3 3 2
4 4 1
5 5 2
I need return rows from table message with group by recipient column from table recipient with last date in message table
I'll try this
SELECT m.*
FROM message as m
INNER JOIN recipient as r ON (m.ID = r.m_id)
WHERE m.author = 1
GROUP BY r.recipient
ORDER BY m.ID DESC
return is
ID author date
--------------------
2 1 2013-07-15 05:38:47
0 1 2013-07-08 05:38:47
but i need
ID author date
--------------------
5 1 2013-07-28 05:38:47
2 1 2013-07-15 05:38:47
please help
I USE MySQL Server 5.1
I found a solution to my problem
SELECT m.*
FROM (
SELECT * FROM recipient
WHERE 1=1
ORDER BY recipient.ID DESC
) AS r
INNER JOIN message AS m ON (r.m_id = m.ID)
WHERE m.author = 1
GROUP BY r.recipient
just reverse table recipient
Very simple and fast in PostgreSQL with DISTINCT ON - not standard SQL so not available in every RDBMS.
The question doesn't mention it, but deriving from the code examples it is actually looking for the row with the "last date" for each recipient for a given author.
SELECT DISTINCT ON (r.recipient) m.*
FROM message m
JOIN recipient r ON r.m_id = m.id
WHERE m.author = 1
ORDER BY r.recipient, m.date DESC, r.m_id -- to break ties
Details as well as multiple SQL standard alternatives here:
Select first row in each GROUP BY group?
Another solution with basic, standard SQL. Works with every major RDBMS, including MySQL (since that tag has been added):
SELECT m.*
FROM message m
JOIN recipient r ON r.m_id = m.id
WHERE m.author = 1
AND NOT EXISTS (
SELECT 1
FROM message m1
JOIN recipient r1 ON r1.m_id = m1.id
WHERE r1.recipient = r.recipient
AND m1.author = 1
AND m1.date > m.date
)
Only the row with the latest date passes the NOT EXISTS anti-semi-join.
这篇关于SQL：从其他表中分组并反转结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
