找人做網(wǎng)站需要交接什么免費網(wǎng)絡(luò)營銷軟件
關(guān)聯(lián)查詢1對多返回
遇見的問題
審批主表,和審批明細表,一張審批對應(yīng)多張明細數(shù)據(jù),每條明細數(shù)據(jù)的狀態(tài)是不一樣的,現(xiàn)在需要根據(jù)明細的狀態(tài)獲取到主單子的狀態(tài),狀態(tài)返回矩陣如下
明細狀態(tài) | 返回總狀態(tài) |
---|---|
都是已完成 | 已完成 |
都是已終止 | 已終止 |
有進行中 | 進行中 |
有草稿 | 草稿 |
只有已完成和已終止 | 已完成 |
觀察報錯
無
問題解決
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;
這里用到了兩層查詢,一遍是查詢該申請單子的所有明細的數(shù)量
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
然后外層是查詢該單子下再按照狀態(tài)進行分組求和
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,因為一個單子只會返回一個狀態(tài),就達到效果了。
結(jié)語
如果哪位大佬發(fā)現(xiàn)了解釋的不對的,還望不吝賜教。十分感謝