我有一个有几十万个条目的表,我试图使用查询来获取特定receiver_id的结果集,并通过sender_id对它们进行分组。我目前的SQL查询工作,但我想知道是否有可能在语句中使用两个MAX调用有任何潜在的问题。它看起来像这样:
SELECT MAX(id) as id, sender_id, receiver_id, MAX(date) as date
FROM messages
WHERE receiver_id=5 and belong_to=5
GROUP BY sender_id
表日期如下所示:
id sender_id receiver_id content date belong_to
-- --------- ----------- ------- ------------------- ---------
1 5 7 test 2013-03-11 10:33:54 7
2 5 7 test 2013-03-11 10:33:54 5
3 13 7 test 2 2013-03-13 12:01:36 7
4 13 7 test 2 2013-03-13 12:01:36 13
5 5 7 test 3 2013-03-14 09:15:37 7
6 5 7 test 3 2013-03-14 09:15:37 5
7 25 5 data1 2013-03-15 11:01:36 5
8 25 5 data1 2013-03-15 11:01:36 25
9 16 5 data2 2013-03-17 09:17:17 5
10 16 5 data2 2013-03-17 09:17:17 16
11 25 5 data3 2013-04-05 09:17:17 5
12 25 5 data3 2013-04-05 09:17:17 16
我的查询的输出是这样的:
id sender_id receiver_id date
-- --------- ----------- -------------------
9 16 5 2013-03-17 09:17:17
11 25 5 2013-04-05 09:17:17
使用MAX调用这个查询有任何问题吗?如果是这样,还有什么替代方案?
我不太理解您的结构(因此,例如,本例假设可以对sender_id, receiver_id, date, belong_to
施加一个UNIQUE键),但我怀疑您想要这样的东西。根据需要按用户筛选…
SELECT x.*
FROM messages x
JOIN
( SELECT sender_id
, receiver_id
, MAX(date) max_date
FROM messages
GROUP
BY receiver_id
, sender_id
) y
ON y.sender_id = x.sender_id
AND y.receiver_id = x.receiver_id
AND y.max_date = x.date
WHERE x.belong_to = x.receiver_id;
根据注释,您需要的是:
' unique [list of send_ids for a particular receiver_id and last entry (date) for each[receiver]'
如果您指的是所有具有该接收者最新条目日期的发送者,则:
Select * From Messages m
Where date = (Select Max(date) From messages
Where receiver_id = m.receiver_id)
And receiver_id = 5 -- add this if you only want results for one receiver_id
你的意思是'特定receiver_id的唯一send_ids列表,包含每个[receiver-sender组合]的最新条目(日期)',然后执行
Select * From Messages m
Where date = (Select Max(date) From messages
Where Sender_id = m.Sender_id
And receiver_id = m.receiver_id)
And receiver_id = 5 -- add this if you only want results for one receiver_id