摘要：分析并修正了一个包含 UNION ALL 和 ORDER BY 的 SQLite 查询，解决子查询中 ORDER BY 和 LIMIT 的作用范围问题。

SQLite UNION ALL 和 ORDER BY 的正确使用

      在 SQLite 中，使用 UNION ALL 时，ORDER BY 和 LIMIT 的作用范围可能引发问题。以下是修正一个具体问题的案例。

原始问题

SELECT '2025-02-23 00:00:00.000' as ID, MARK as lastPackage, MARK as currentPackage 
FROM shijian 
WHERE LEI='APP使用记录' AND ID LIKE '2025-02-23%' 
ORDER BY ID ASC LIMIT 1
UNION ALL
SELECT '2025-02-23 23:59:59.999' as ID, TITLE as lastPackage, TITLE as currentPackage 
FROM shijian 
WHERE LEI='APP使用记录' AND ID LIKE '2025-02-23%' 
ORDER BY ID DESC LIMIT 1
UNION ALL
SELECT ID, MARK as lastPackage, TITLE as currentPackage 
FROM shijian 
WHERE LEI='APP使用记录' AND ID LIKE '2025-02-23%'
ORDER BY ID;

      该语句的问题在于 ORDER BY 和 LIMIT 无法直接应用于子查询中的 UNION ALL。

解决方案

(
    SELECT '2025-02-23 00:00:00.000' AS ID, MARK AS lastPackage, MARK AS currentPackage 
    FROM shijian 
    WHERE LEI = 'APP使用记录' AND ID LIKE '2025-02-23%' 
    ORDER BY ID ASC 
    LIMIT 1
)
UNION ALL
(
    SELECT '2025-02-23 23:59:59.999' AS ID, TITLE AS lastPackage, TITLE AS currentPackage 
    FROM shijian 
    WHERE LEI = 'APP使用记录' AND ID LIKE '2025-02-23%' 
    ORDER BY ID DESC 
    LIMIT 1
)
UNION ALL
(
    SELECT ID, MARK AS lastPackage, TITLE AS currentPackage 
    FROM shijian 
    WHERE LEI = 'APP使用记录' AND ID LIKE '2025-02-23%'
)
ORDER BY ID;

      通过将每个子查询用括号包裹，确保 ORDER BY 和 LIMIT 只作用于各自的子查询，最后在整体结果集上添加 ORDER BY ID。

注意事项

• 确保表 shijian 的列名和数据类型与查询一致。
• 如果 ID 是日期时间类型，需转换为字符串格式。