求助：MySQL单查询获取各状态各5条发票数据的实现方案

可以通过 UNION ALL 将四个独立查询合并为一条语句，一次性获取四种状态下各5条发票数据，具体SQL如下：

SELECT * FROM Invoice WHERE due_state = 'due' LIMIT 5
UNION ALL
SELECT * FROM Invoice WHERE due_state = 'overdue' LIMIT 5
UNION ALL
SELECT * FROM Invoice WHERE disputed_state = 'disputed' LIMIT 5
UNION ALL
SELECT * FROM Invoice WHERE paid_state = 'partially_paid' LIMIT 5;

补充说明 #

• 选用 UNION ALL 而非 UNION：前者不会对结果集去重，能确保每种状态都返回完整的5条数据，同时执行效率更高。
• 若需要直观区分每条数据对应的状态，可在每个子查询中新增状态标识字段，示例：

SELECT *, 'due' AS status_type FROM Invoice WHERE due_state = 'due' LIMIT 5
UNION ALL
SELECT *, 'overdue' AS status_type FROM Invoice WHERE due_state = 'overdue' LIMIT 5
UNION ALL
SELECT *, 'disputed' AS status_type FROM Invoice WHERE disputed_state = 'disputed' LIMIT 5
UNION ALL
SELECT *, 'partially_paid' AS status_type FROM Invoice WHERE paid_state = 'partially_paid' LIMIT 5;

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