子查询

select b.id,b.dwmc,f.id,f.ORG_NAME,f.FILE_TYPE from tb_casq b LEFT JOIN tb_bid_file f on f.OWN_CODE= b.id where b.id='25a4d7fe020f628e93953881d56f03bf'

申请表的id作为文件表的own_code  把两个表合并在一起

把查出的四个不同类型的文件写在一条记录上

SELECT b.id,b.dwmc,
(SELECT f.id FROM tb_bid_file f WHERE f.OWN_CODE=b.id AND f.FILE_TYPE='22001' ) f1id,
(SELECT f.ORG_NAME FROM tb_bid_file f WHERE f.OWN_CODE=b.id AND f.FILE_TYPE='22001' ) f1name,
(SELECT f.id FROM tb_bid_file f WHERE f.OWN_CODE=b.id AND f.FILE_TYPE='22002' ) f2id,
(SELECT f.ORG_NAME FROM tb_bid_file f WHERE f.OWN_CODE=b.id AND f.FILE_TYPE='22002' ) f2name,
(SELECT f.id FROM tb_bid_file f WHERE f.OWN_CODE=b.id AND f.FILE_TYPE='22003' ) f3id,
(SELECT f.ORG_NAME FROM tb_bid_file f WHERE f.OWN_CODE=b.id AND f.FILE_TYPE='22003' ) f3name,
(SELECT f.id FROM tb_bid_file f WHERE f.OWN_CODE=b.id AND f.FILE_TYPE='22004' ) f4id,
(SELECT f.ORG_NAME FROM tb_bid_file f WHERE f.OWN_CODE=b.id AND f.FILE_TYPE='22004' ) f4name
FROM tb_casq b  WHERE b.id='25a4d7fe020f628e93953881d56f03bf'

//查询多个

SELECT b.id,b.dwmc,
(SELECT f.id FROM tb_bid_file f WHERE f.OWN_

CODE=b.id AND f.FILE_TYPE='22001' ) f1id,
(SELECT f.ORG_NAME FROM tb_bid_file f WHERE f.OWN_CODE=b.id AND f.FILE_TYPE='22001' ) f1name,
(SELECT f.id FROM tb_bid_file f WHERE f.OWN_CODE=b.id AND f.FILE_TYPE='22002' ) f2id,
(SELECT f.ORG_NAME FROM tb_bid_file f WHERE f.OWN_CODE=b.id AND f.FILE_TYPE='22002' ) f2name,
(SELECT f.id FROM tb_bid_file f WHERE f.OWN_CODE=b.id AND f.FILE_TYPE='22003' ) f3id,
(SELECT f.ORG_NAME FROM tb_bid_file f WHERE f.OWN_CODE=b.id AND f.FILE_TYPE='22003' ) f3name,
(SELECT f.id FROM tb_bid_file f WHERE f.OWN_CODE=b.id AND f.FILE_TYPE='22004' ) f4id,
(SELECT f.ORG_NAME FROM tb_bid_file f WHERE f.OWN_CODE=b.id AND f.FILE_TYPE='22004' ) f4name
FROM tb_casq b
left join tb_bid_file f on b.id = f.OWN_CODE

 

还有一个例子

select a.*,b.is_win from 
(SELECT b.id,b.projectname,b.qualifications,b.partya,b.winbidders,b.promanager,b.wintime,b.winmoney,b.protime,
b.supervision_cotract,b.winbidnotice,b.customer,b.logother,b.infodate,b.infopeo,b.boolend,b.isin,b.kbtime,b.platform,b.original,b.Authorizer,
(SELECT GROUP_CONCAT(companys) FROM TB_OA_ENTRY WHERE WINBIDID=B.ID ) tbdw,
(SELECT SUM(QUALIFICATIONS_FEES) FROM TB_OA_ENTRY WHERE WINBIDID=B.ID ) tolzzfee,
(SELECT SUM(WINBIDFEE) FROM TB_OA_ENTRY WHERE WINBIDID=B.ID ) tolwbfee, 
(SELECT SUM(BOOKFEES) FROM TB_OA_ENTRY WHERE WINBIDID=B.ID ) tolbsfee, 
(SELECT SUM(AUDITFEE) FROM TB_OA_ENTRY WHERE WINBIDID=B.ID ) tolsjfee,
(SELECT SUM(PEOFEE) FROM TB_OA_ENTRY WHERE WINBIDID=B.ID ) tolryfee, 
(SELECT ID FROM TB_BID_FILE WHERE OWN_CODE=B.ID AND FILE_TYPE ='0400001') tzsId,
(SELECT ID FROM TB_BID_FILE WHERE OWN_CODE=B.ID AND FILE_TYPE ='0400002') htId 
FROM TB_WINBID B WHERE 1=1 
and boolend = '0100100' 
AND BOOLEND <> '0100102' ORDER BY INFODATE DESC) a
left join TB_OA_ENTRY b on b.winbidid = a.id where b.is_win = '0100100'