根据时间选择前1个以获取不同的价值

问题描述

我得到了以下表格:

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个以获取不同的价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！