SQLite含GROUP BY与CASE子句的查询异常解决求助

你的问题核心是错误地把归档和未归档的分组逻辑混在同一个GROUP BY里处理，导致未归档条目被意外过滤掉了。咱们一步步理清楚：

先说说原查询的问题 #

你写的GROUP BY CASE表达式：

CASE
  WHEN items.archived = 1  
    THEN items.id
  ELSE IFNULL(storeId, id)
END

逻辑上看似合理：归档条目用id单独分组，未归档用storeId分组。但SQLite的GROUP BY有个特点——当你SELECT *的时候，它只会返回每组里的某一条随机数据，而且你把所有条目放在同一个分组逻辑里，会让未归档的分组和归档条目完全割裂，甚至被覆盖。比如storeId a-1的未归档条目id1，它的分组值是a-1，但这个分组里只有它自己，理论上应该出现在结果里，但因为SQLite的非标准GROUP BY行为，加上没有明确的排序，可能它被排在后面没被返回？更关键的是，你的需求根本不是要把所有条目混在一起分组，而是要分开处理两部分数据：保留所有归档条目，同时对未归档条目按storeId分组汇总。

正确的实现方法：用UNION ALL合并两部分数据 #

我们可以把查询拆成两个独立的部分，再用UNION ALL合并结果：

1. 第一部分：直接查询所有归档条目（archived=1），这部分不需要分组，全部保留。
2. 第二部分：对未归档条目（archived=0）按storeId分组，每个storeId保留一条数据（这里我用MIN(id)取每个storeId下最早的未归档条目，你也可以换成MAX(id)或者其他逻辑）。

完整的SQL语句如下：

-- 第一部分：获取所有归档条目
SELECT id, storeId, archived
FROM items
WHERE archived = 1

UNION ALL

-- 第二部分：未归档条目按storeId分组，每个storeId取一条
SELECT MIN(id) AS id, storeId, archived
FROM items
WHERE archived = 0
GROUP BY storeId, archived;

如果你的需求是统计每个storeId的未归档数量，而不是取具体条目，可以把第二部分改成聚合查询：

SELECT NULL AS id, storeId, 0 AS archived, COUNT(*) AS unarchived_total
FROM items
WHERE archived = 0
GROUP BY storeId;

再总结下原查询的误区 #

你想通过一个GROUP BY同时实现“保留所有归档条目”和“未归档按storeId分组”，但这两种需求的分组逻辑完全不兼容：归档条目需要每条单独成组，未归档需要同storeId成一组。把它们放在同一个GROUP BY里，只会让SQLite的分组逻辑混乱，返回不符合预期的结果。分开处理再合并，才是正确的思路。

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