關(guān)聯(lián)查詢1對(duì)多返回

遇見(jiàn)的問(wèn)題

審批主表，和審批明細(xì)表，一張審批對(duì)應(yīng)多張明細(xì)數(shù)據(jù)，每條明細(xì)數(shù)據(jù)的狀態(tài)是不一樣的，現(xiàn)在需要根據(jù)明細(xì)的狀態(tài)獲取到主單子的狀態(tài)，狀態(tài)返回矩陣如下

觀察報(bào)錯(cuò)

無(wú)

問(wèn)題解決

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;

這里用到了兩層查詢，一遍是查詢?cè)撋暾?qǐng)單子的所有明細(xì)的數(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

然后外層是查詢?cè)搯巫酉略侔凑諣顟B(tài)進(jìn)行分組求和

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

然后拿到的申請(qǐng)單id，再group by這個(gè)單子id，因?yàn)橐粋€(gè)單子只會(huì)返回一個(gè)狀態(tài)，就達(dá)到效果了。

結(jié)語(yǔ)

如果哪位大佬發(fā)現(xiàn)了解釋的不對(duì)的，還望不吝賜教。十分感謝