SQLite如何编写按ID返回姓名、首条记录及总条数的SQL查询

• 写在SELECT子句里的子查询属于标量子查询，SQLite对这类子查询的要求是最多返回1行1列的单个值，原语句里的子查询同时返回stuID、lessDate两个列，不符合语法规则，因此无法运行。
• 原语句直接混用普通字段stuName和聚合函数COUNT(*)但没有写GROUP BY，在SQLite严格模式下也会触发语法错误。

根据需要返回的字段粒度，选对应写法即可：

写法1：窗口函数（推荐，SQLite 3.25及以上版本支持，代码最简洁） #

直接用窗口函数一次性计算首条记录、总条数，不需要多层子查询嵌套或者额外分组：

SELECT 
    stuName,
    FIRST_VALUE(stuID) OVER (ORDER BY lessDate) AS firstLessonStuID,
    FIRST_VALUE(lessDate) OVER (ORDER BY lessDate) AS firstLessonDate,
    COUNT(*) OVER () AS totalNum
FROM Lessons
WHERE stuID = 66
LIMIT 1;

写法2：CTE关联（兼容所有SQLite版本） #

先通过CTE查出对应学生的首条课程记录，再关联原表做聚合统计：

WITH first_lesson AS (
    SELECT stuID, lessDate
    FROM Lessons
    WHERE stuID = 66
    ORDER BY lessDate
    LIMIT 1
)
SELECT 
    l.stuName,
    fl.stuID AS firstLessonStuID,
    fl.lessDate AS firstLessonDate,
    COUNT(l.*) AS totalNum
FROM Lessons l, first_lesson fl
WHERE l.stuID = 66
GROUP BY l.stuName, fl.stuID, fl.lessDate;

写法3：拆分标量子查询（仅需返回首课日期时用） #

如果不需要返回首条记录的stuID，只需要课程日期，可以把多列子查询拆成单列标量子查询，加上分组即可：

SELECT 
    stuName,
    (SELECT lessDate FROM Lessons WHERE stuID = 66 ORDER BY lessDate LIMIT 1) AS firstLessonDate,
    COUNT(*) AS totalNum
FROM Lessons
WHERE stuID = 66
GROUP BY stuName;

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