php - DISTINCT 不按照我想要的方式与 ORDER BY 合作

Question

我有这种电子邮件表

id | url | id1 | id2 | mail_date | message

我的行示例

id | url | id1 | id2 | mail_date | message

1  | aaa | 2   | 8   | JAN 2016  | blah blah
2  | bbb | 4   | 8   | FEB 2016  | blah blah
3  | aaa | 8   | 2   | MAR 2016  | blah blah
4  | bbb | 8   | 4   | APR 2016  | blah blah
5  | bbb | 4   | 8   | MAY 2016  | blah blah
6  | aaa | 2   | 8   | JUN 2016  | blah blah
7  | bbb | 8   | 4   | JUL 2016  | blah blah
8  | aaa | 8   | 2   | AUG 2016  | blah blah

我有这样的疑问

SELECT DISTINCT url, id1, id2 FROM email WHERE id1 = 8 OR id2 = 8 ORDER BY mail_date DESC 

我的问题是，当我在 phpMyAdmin 或 php 代码 中执行此操作时，我得到如下结果:

url   | id1 | id2
bbb   | 8   | 4
aaa   | 8   | 2
bbb   | 4   | 8
aaa   | 2   | 8

我想要的是获得谁有用户发送或接收的最新电子邮件的结果，如下所示:

url   | id1 | id2
aaa   | 8   | 2
bbb   | 8   | 4
aaa   | 2   | 8
bbb   | 4   | 8

我想要的是DISTINCT将选择唯一和最新(基于mail_date)行在email 表中，就像我的want 示例一样。

url   | id1 | id2
aaa   | 8   | 2 //AUG 2016
bbb   | 8   | 4 //JUL 2016
aaa   | 2   | 8 //JUN 2016
bbb   | 4   | 8 //MAY 2016

但是! DISTINCT 将选择查询的第一个唯一行，并且它将获得较旧的 mail_date，就像我的 问题示例一样。

url   | id1 | id2
bbb   | 8   | 4 //APR 2016
aaa   | 8   | 2 //MAR 2016
bbb   | 4   | 8 //FEB 2016
aaa   | 2   | 8 //JAN 2016

有没有办法DISTINCT按照我想要的方式与ORDER BY mail_date合作？

更新

抱歉，我在url方面犯了一个错误，我现在更新了。

Answer 1

Distinct只会消除在所有选定列中具有完全相同数据的记录。由于每条记录的 URL 都不同，distinct 不会消除任何记录。

如果您要查找最近出现的发送者/接收者及其 URL，则可以通过首先获取包含每个发送者/接收者 (ID1/ID2) 组合的最大日期的一组数据来实现此目的。然后将其作为限制加入到基本集。

SELECT E1.URL, E1.ID1, E1.ID2
FROM email E1
INNER JOIN (SELECT ID1, ID2, max(mail_date) max_mail_date
            FROM email 
            GROUP BY ID1, ID2) E2
  on E1.ID1 = E2.ID1
 and E1.ID2 = E2.ID2
 and E1.mail_date = E2.max_mail_date
WHERE E1.ID1 = 8 or E1.ID2 = 8
ORDER BY mail_date desc

连接的另一种可能会更快的替代方法是使用存在而不是内联 View 。由于我们想要最大日期，因此我们还需要使用 HAVING，因为由于执行顺序，聚合值在 where 子句中不可用。

SELECT E1.URL, E1.ID1, E1.ID2
FROM email E1
WHERE (E1.ID1 = 8 or E1.ID2 = 8)
 AND  EXISTS (SELECT max(E2.mail_date) maxDate
            FROM email E2
            WHERE E1.ID1 = E2.ID1
              and E1.ID2 = E2.ID2
            GROUP BY ID1, ID2
            HAVING E1.mail_date = maxDate)
ORDER BY E1.mail_date desc