审批主表,和审批明细表,一张审批对应多张明细数据,每条明细数据的状态是不一样的,现在需要根据明细的状态获取到主单子的状态,状态返回矩阵如下
明细状态 | 返回总状态 |
---|---|
都是已完成 | 已完成 |
都是已终止 | 已终止 |
有进行中 | 进行中 |
有草稿 | 草稿 |
只有已完成和已终止 | 已完成 |
无
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 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_status
FROM (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) ba
LEFT JOIN (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) ba1 ON ba1.approval_id = ba.approval_id
GROUP BY ba.approval_id) t2 ON t2.approval_id = vba.object_id
WHERE vba.is_deleted = 0
AND 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 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,因为一个单子只会返回一个状态,就达到效果了。
如果哪位大佬发现了解释的不对的,还望不吝赐教。十分感谢