有一种场景,我们需要在同一张表中找出几个分类的文章,并且按照时间排序,通常我们会这样写(假设表名称是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)赞赏
微信赞赏
支付宝赞赏