根据时间选择前1个以获取不同的价值 [英] Select top 1 for distinct value according to time
问题描述
我得到了以下表格:
tblMyFriends
ID FriendID GroupID Public
1 F1 YES
2 F2 YES
3 F3 NO
3 G1 YES
4 G2 YES
6 F4 NO
7 F5 YES
8 G3 NO
tblMessages
ID FriendID GroupID MyMessage MyTime
1 F1 A1 2
2 F4 A2 3
3 F1 A3 1
3 G2 Y1 1
4 G2 Y2 3
6 F3 A4 3
7 F3 A5 4
8 G3 Y3 5
9 F4 A6 5
10 F4 A7 6
我需要获取最新消息(仅前1个消息),即降低时间顺序.此外,然后是我列表中其余的朋友或群组列表
I need to fetch the latest message(only Top 1) i.e. decreasing order of time. Also, followed by the remaining list of friend or group in my list
因此,我将需要查询,该查询将返回以下输出:
Hence, i will need query which will return following output:
tblOutput
ID FriendID GroupID MyMessage MyTime
1 F4 A7 6
2 G3 Y3 5
3 F3 A5 4
4 G2 Y2 3
5 F1 A1 2
6 F2 0
7 F5 0
8 G2 0
我正在使用SQLite
i'm using SQLite
推荐答案
To get records for friends/groups that do not have messages, use an outer join.
要获取单个密钥,使连接变得更容易,请使用 coalesce函数.(这要求空字段为NULL.)
To get a single key, which makes joining easier, use the coalesce function. (This requires that the empty fields are NULL.)
要为每组记录获取一个结果记录,请使用GROUP BY:
To get one result record for each group of records use GROUP BY:
SELECT tblMyFriends.FriendID,
tblMyFriends.GroupID,
tblMessages.MyMessage,
MAX(tblMessages.MyTime)
FROM tblMyFriends
LEFT JOIN tblMessages
ON COALESCE(tblMyFriends.FriendID, tblMyFriends.GroupID) =
COALESCE(tblMessages.FriendID, tblMessages.GroupID)
GROUP BY tblMyFriends.FriendID,
tblMyFriends.GroupID
这需要SQLite 3.7.11或更高版本;否则,MyMessage值将不会来自与MAX匹配的同一记录.
This requires SQLite 3.7.11 or later; otherwise, the MyMessage values will not be from the same record that matches the MAX.
这篇关于根据时间选择前1个以获取不同的价值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!