【Mysql】关联查询1对多处理

关联查询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_abstract
FROM view_bus_approval vbaLEFT JOIN view_bus_payment vbp ON vba.object_id = vbp.approval_idLEFT JOIN (SELECT ba.approval_id,CASEWHEN 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) baLEFT 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_id
WHERE vba.is_deleted = 0AND vba.business_table = 'bus_official_seal_use'
ORDER BY (CASEvba.enum_statusWHEN '05' THEN1WHEN '30' THEN2WHEN '10' THEN3WHEN '40' THEN4WHEN '20' THEN5ELSE 6END) ASC,vba.create_time DESC;

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

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

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

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

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

结语

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