SQLite视图分组获取最新消息及统计未读消息数的实现

我需要从SQLite的一个多来源消息整合视图中获取数据：同一用户有多条消息时仅保留最新的一条，该视图已按Date字段降序排序。

我原本用查询语句：

SELECT *, COUNT(IsRead = false) FROM Messages GROUP BY User

能得到每个用户的最新消息和总消息数，但我需要统计的是未读消息（IsRead = false）的数量而非总消息数。

给定数据表：

+-------+--------+------------+
| User  | IsRead |    Date    |
+-------+--------+------------+
| User1 | false  | 2020-01-05 |
| User2 | false  | 2020-01-04 |
| User1 | false  | 2020-01-03 |
| User3 | true   | 2020-01-02 |
| User2 | true   | 2020-01-01 |
| User3 | true   | 2020-01-01 |
+-------+--------+------------+

期望结果：

+-------+--------+------------+---------+
| User  | IsRead |    Date    | notRead |
+-------+--------+------------+---------+
| User1 | false  | 2020-01-05 |       2 |
| User2 | false  | 2020-01-04 |       1 |
| User3 | true   | 2020-01-02 |       0 |
+-------+--------+------------+---------+

请问如何实现该需求？另外，若视图已按Date降序排序，分组后是否总能获取到用户的最新消息？测试中符合预期，但需确认是否可靠。

1. 统计未读消息数量 #

原来的COUNT(IsRead = false)会把所有行都计数（因为IsRead = false返回0或1，COUNT会统计非NULL值），所以得到的是总消息数。要统计未读数量，应该用SUM函数：

方案一：CASE表达式 #

SELECT 
    User,
    IsRead,
    Date,
    SUM(CASE WHEN IsRead = false THEN 1 ELSE 0 END) AS notRead
FROM Messages
GROUP BY User

方案二：利用SQLite布尔值特性简化 #

SQLite中布尔值等价于整数（false=0，true=1），可以直接用NOT IsRead转换未读状态为1，求和得到未读数量：

SELECT 
    User,
    IsRead,
    Date,
    SUM(NOT IsRead) AS notRead
FROM Messages
GROUP BY User

2. 分组后取最新消息的可靠性 #

结论：仅依赖视图排序后分组取最新消息不可靠

SQLite官方文档明确说明：当使用GROUP BY时，未被包含在分组条件或聚合函数中的列（比如IsRead、Date），会返回分组内任意一行的值，这个行为是不确定的。

测试中符合预期只是巧合——SQLite可能会临时按照视图的排序顺序取行，但这并非官方承诺的稳定行为。当数据量变化、SQLite版本更新或执行计划调整时，结果可能随机选取分组内的其他行，无法保证始终获取最新消息。

可靠获取最新消息的方案 #

方式一：子查询关联取最新日期

先通过子查询获取每个用户的最新消息日期，再关联原表拿到对应消息，同时统计未读数量：

WITH LatestDates AS (
    SELECT User, MAX(Date) AS LatestDate
    FROM Messages
    GROUP BY User
)
SELECT 
    m.User,
    m.IsRead,
    m.Date,
    SUM(NOT m2.IsRead) AS notRead
FROM Messages m
JOIN LatestDates ld ON m.User = ld.User AND m.Date = ld.LatestDate
JOIN Messages m2 ON m.User = m2.User
GROUP BY m.User, m.IsRead, m.Date

方式二：窗口函数（推荐，SQLite 3.25+支持）

用ROW_NUMBER()窗口函数给每个用户的消息按日期降序编号，取编号为1的行（最新消息），同时通过窗口函数直接统计该用户的未读总数：

WITH RankedMessages AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY User ORDER BY Date DESC) AS rn,
        SUM(NOT IsRead) OVER (PARTITION BY User) AS notRead
    FROM Messages
)
SELECT User, IsRead, Date, notRead
FROM RankedMessages
WHERE rn = 1

这种方式逻辑清晰，结果完全可靠，是优先选择的方案（需确保你的SQLite版本≥3.25）。

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