选择多个行共有的最大日期

Question

给定如下表，使用UNIQUE CONSTRAINT(user, date)，如何选择列表(1, 2)中所有用户通用的max(date)？

ID | date     | user | value
-----------------------------
1  | 2016-5-1 | 1    | a
2  | 2016-5-1 | 2    | b
3  | 2016-5-3 | 2    | c
4  | 2015-5-4 | 1    | d

因此，对于WHERE user IN (1, 2)，查询应该返回2016-5-1。

到目前为止，我有两个工作版本，但看起来都不是很干净。有没有更好/更通用/更灵活的方法来实现我想要的东西？注意，我只需要在PostgreSQL中使用它。

a)依赖于sensor列表的长度：

SELECT a.date FROM 
    (SELECT date, COUNT(date) 
        FROM mytable WHERE sensor_id IN (8, 9)
        GROUP BY date ORDER BY date DESC) a
    WHERE a.count = 2 LIMIT 1;

b)使用单独的子查询：

SELECT i.date
    FROM (SELECT date FROM mytable WHERE user_id=1) i
    JOIN (SELECT date FROM mytable WHERE user_id=2) j
ON i.date = j.date ORDER BY date DESC LIMIT 1;

Answer 1

select date
from mytable
where user_id in (1,2)
group by date
having count(date) = 2
order by date desc
limit 1;

having中的数字必须等于where中list的基数。

Answer 2

这两种方法都很好，但每种方法都可以调整为更好。

第一个不需要子查询：

SELECT date
FROM mytable
WHERE sensor_id IN (8, 9)
GROUP BY date
HAVING COUNT(*) = 2
ORDER BY date DESC
LIMIT 1;

MySQL具体化了子查询，这增加了开销。此外，子查询中的ORDER BY只是对查询中字符的浪费--它要么执行不必要的排序，要么被MySQL忽略。

第二个版本应该这样写：

SELECT i.date
FROM mytable i JOIN
     mytable j
     ON i.date = j.date AND i.sensor_id = 8 AND j.sensor_id = 9
ORDER BY i.date DESC
LIMIT 1;

子查询再一次阻碍了性能。在这种情况下，它们可能会阻止对JOIN使用索引，并导致物化开销。

这两个版本都是表达逻辑的好方法。我发现第一种方法更具一般性。如果性能是个问题，那么您应该在您的数据和系统上同时尝试这两个版本，看看哪一个效果更好。