SQLite数据库中如何筛选特定用户与各联系人的最新单条消息？

我来帮你解决这个问题——你需要的是给特定用户（比如gondolator）保留和每个联系人的最新一条消息（无论发送还是接收），而不是当前查询返回的收发双向条目。当前的GROUP BY只是简单按收发双方的组合分组，既会重复显示同一联系人的收发记录，也没保证取到的是最新消息。下面是针对SQLite的解决方案：

最优方案（使用CTE，可读性高） #

这个方案用公共表表达式（CTE）先定位每个联系人的最新消息时间，再关联回原表获取完整消息内容，逻辑清晰且高效：

WITH target_user AS (
    -- 先获取目标用户的ID，后续用ID关联比字符串匹配更高效
    SELECT ID FROM Accounts WHERE Benutzername = 'gondolator'
),
contact_latest AS (
    SELECT
        -- 统一每个消息对应的联系人：如果是目标用户发的，联系人是接收者；否则是发送者
        CASE
            WHEN n.Sender = tu.ID THEN a_emp.Benutzername
            ELSE a_send.Benutzername
        END AS contact,
        MAX(n.Zeit) AS latest_time
    FROM Nachrichten n
    CROSS JOIN target_user tu
    LEFT JOIN Accounts a_send ON n.Sender = a_send.ID
    LEFT JOIN Accounts a_emp ON n.Empfaenger = a_emp.ID
    -- 筛选所有和目标用户相关的消息
    WHERE n.Sender = tu.ID OR n.Empfaenger = tu.ID
    GROUP BY contact
)
-- 关联回原表，获取最新消息的完整详情
SELECT
    a_send.Benutzername AS Sender,
    a_emp.Benutzername AS Empfaenger,
    n.Inhalt,
    n.Zeit
FROM Nachrichten n
JOIN target_user tu ON (n.Sender = tu.ID OR n.Empfaenger = tu.ID)
JOIN contact_latest cl ON (
    -- 匹配联系人与对应的最新时间
    (n.Sender = tu.ID AND cl.contact = (SELECT Benutzername FROM Accounts WHERE ID = n.Empfaenger))
    OR
    (n.Empfaenger = tu.ID AND cl.contact = (SELECT Benutzername FROM Accounts WHERE ID = n.Sender))
)
AND n.Zeit = cl.latest_time
LEFT JOIN Accounts a_send ON n.Sender = a_send.ID
LEFT JOIN Accounts a_emp ON n.Empfaenger = a_emp.ID;

方案逻辑说明 #

1. target_user CTE：提前获取目标用户的ID，避免后续多次查询用户名，提升关联效率。
2. contact_latest CTE：
   • 遍历所有与目标用户相关的消息，用CASE语句统一识别每条消息对应的联系人（比如gondolator发消息给Killergondel，联系人是Killergondel；Killergondel发给gondolator，联系人同样是Killergondel）。
   • 按联系人分组，取每组的最大时间latest_time，也就是目标用户与该联系人的最新消息时间。
3. 主查询：关联原消息表和contact_latest，精准定位每个联系人对应的最新时间的那条消息，同时关联Accounts表获取收发双方的用户名。

备选方案（无CTE，兼容旧版SQLite） #

如果你的SQLite版本低于3.8.3（不支持CTE），可以用子查询实现相同逻辑：

SELECT
    a_send.Benutzername AS Sender,
    a_emp.Benutzername AS Empfaenger,
    n.Inhalt,
    n.Zeit
FROM Nachrichten n
LEFT JOIN Accounts a_send ON n.Sender = a_send.ID
LEFT JOIN Accounts a_emp ON n.Empfaenger = a_emp.ID
WHERE
    (a_send.Benutzername = 'gondolator' OR a_emp.Benutzername = 'gondolator')
    AND n.Zeit = (
        SELECT MAX(n2.Zeit)
        FROM Nachrichten n2
        LEFT JOIN Accounts a2_send ON n2.Sender = a2_send.ID
        LEFT JOIN Accounts a2_emp ON n2.Empfaenger = a2_emp.ID
        WHERE
            -- 匹配同一个联系人的收发记录
            (
                (a2_send.Benutzername = 'gondolator' AND a2_emp.Benutzername = CASE WHEN a_send.Benutzername = 'gondolator' THEN a_emp.Benutzername ELSE a_send.Benutzername END)
                OR
                (a2_emp.Benutzername = 'gondolator' AND a2_send.Benutzername = CASE WHEN a_send.Benutzername = 'gondolator' THEN a_emp.Benutzername ELSE a_send.Benutzername END)
            )
    )
GROUP BY
    -- 按联系人分组，确保每个联系人只显示一条
    CASE WHEN a_send.Benutzername = 'gondolator' THEN a_emp.Benutzername ELSE a_send.Benutzername END;

测试验证 #

用你提供的测试数据执行上述SQL，会得到gondolator与Killergondel、Jan、TestUser各一条最新消息（由于测试数据的时间都是datetime('now')，如果实际场景中时间有先后，会自动取最晚的那条）。

内容的提问来源于stack exchange，提问作者gondolator