SQL：从其他表中分组并反转结果

问题描述

我的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标准备选方案在这里：

另一个解决方案使用基本的标准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 queryI 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 tableI'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：从其他表中分组并反转结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！