关联查询1对多返回

遇见的问题

审批主表，和审批明细表，一张审批对应多张明细数据，每条明细数据的状态是不一样的，现在需要根据明细的状态获取到主单子的状态，状态返回矩阵如下

明细状态返回总状态
都是已完成已完成
都是已终止已终止
有进行中进行中
有草稿草稿
只有已完成和已终止已完成

观察报错

无

问题解决

SELECT vba.object_id, vba.create_time, vba.company_id, vba.approval_no, vba.finish_time, vba.business_table, vba.business_id, t2.enum_status, vba.create_user_name, vba.apply_abstractFROM view_bus_approval vba LEFT JOIN view_bus_payment vbp ON vba.object_id = vbp.approval_id LEFT JOIN (SELECT ba.approval_id, CASE WHEN ba.enum_status = '05' AND ba.cnt > 0 THEN '05' WHEN ba.enum_status = '10' AND ba.cnt > 0 THEN '10' WHEN ba.enum_status = '20' AND ba.cnt = ba1.cnt THEN '20' WHEN ba.enum_status = '30' AND ba.cnt = ba1.cnt THEN '30' WHEN ba.enum_status = '40' AND ba.cnt = ba1.cnt THEN '40' ELSE '20' END AS enum_statusFROM (SELECT t2.approval_id, t2.enum_status, sum(cnt) AS cntFROM view_approval_detail_status_cnt t2WHERE t2.approval_user_id = 1GROUP BY t2.approval_id, t2.enum_status) ba LEFT JOIN (SELECT t2.approval_id, sum(cnt) AS cntFROM view_approval_detail_status_cnt t2WHERE t2.approval_user_id = 1GROUP BY t2.approval_id) ba1 ON ba1.approval_id = ba.approval_idGROUP BY ba.approval_id) t2 ON t2.approval_id = vba.object_idWHERE vba.is_deleted = 0AND vba.business_table = 'bus_official_seal_use'ORDER BY ( CASE vba.enum_status WHEN '05' THEN 1 WHEN '30' THEN 2 WHEN '10' THEN 3 WHEN '40' THEN 4 WHEN '20' THEN 5 ELSE 6 END ) ASC, vba.create_time DESC;

这里用到了两层查询，一遍是查询该申请单子的所有明细的数量

SELECT t2.approval_id, sum(cnt) AS cntFROM view_approval_detail_status_cnt t2WHERE t2.approval_user_id = 1GROUP BY t2.approval_id

然后外层是查询该单子下再按照状态进行分组求和

SELECT t2.approval_id, t2.enum_status, sum(cnt) AS cntFROM view_approval_detail_status_cnt t2WHERE t2.approval_user_id = 1GROUP BY t2.approval_id, t2.enum_status

然后拿到的申请单id，再group by这个单子id，因为一个单子只会返回一个状态，就达到效果了。

结语

如果哪位大佬发现了解释的不对的，还望不吝赐教。十分感谢