解决sqlite中union的子句不能使用order by和limit问题

有一种场景，我们需要在同一张表中找出几个分类的文章，并且按照时间排序，通常我们会这样写（假设表名称是post）:

SELECT * FROM post WHERE category_id=1 ORDER BY create_time DESC LIMIT 10

如果有多个分类，我们会考虑将几个语句使用union all连接

SELECT * FROM post WHERE category_id=1 ORDER BY create_time DESC LIMIT 10
UNION ALL
SELECT * FROM post WHERE category_id=2 ORDER BY create_time DESC LIMIT 10

但是这个语句在sqlite是行不通的，sqllite的order和limit不能在union子句执行，这个时候可以使用子查询实现，比如:

SELECT * FROM (SELECT * FROM post WHERE category_id=1 ORDER BY create_time DESC LIMIT 10)
UNION ALL
SELECT * FROM (SELECT * FROM post WHERE category_id=1 ORDER BY create_time DESC LIMIT 10)

赞赏

微信赞赏支付宝赞赏