left join 关联查询 一对多 重复数据处理
SELECT DISTINCT
a.QYMC,
a.XYDM,
a.DZ,
a.FW,
a.XKZBH,
a.JGJG,
a.FZJG,
a.QFR,
a.FZRQ_DATE,
a.YXQZ_DATE,
a.TYPE,
c.facilityName,
c.facilityLocation
FROM
COMPANY_BASE a
LEFT JOIN company_drug_product b ON a.UUID = b.COMPANYBASEUUID
left join employee c on a.UUID = c.COMPANYBASEUUID and c.isdel=0 and c.mark=9
WHERE
a.QYLX = 3
AND a.ISDEL = 0
AND (
ifnull(a.QYMC, '') = ''
OR ifnull(a.XYDM, '') = ''
OR ifnull(a.DZ, '') = ''
OR ifnull(a.FW, '') = ''
OR ifnull(a.XKZBH, '') = ''
OR ifnull(a.JGJG, '') = ''
OR ifnull(a.FZJG, '') = ''
OR ifnull(a.QFR, '') = ''
OR ifnull(a.FZRQ_DATE, '') = ''
OR ifnull(a.YXQZ_DATE, '') = ''
OR ifnull(a.TYPE, '') = ''
OR ifnull(b.FR, '') = ''
OR ifnull(b.FZR, '') = ''
OR ifnull(c.facilityName, '') = ''
)
ORDER BY
a.YXQ_DATE DESC,
a.id DESC
SELECT DISTINCT
a.QYMC,
a.XYDM,
a.DZ,
a.FW,
a.XKZBH,
a.JGJG,
a.FZJG,
a.QFR,
a.FZRQ_DATE,
a.YXQZ_DATE,
a.TYPE,
c.facilityName,
c.facilityLocation
FROM
COMPANY_BASE a
LEFT JOIN company_drug_product b ON a.UUID = b.COMPANYBASEUUID
LEFT JOIN (select COMPANYBASEUUID,fullName from employee where isdel = 0 and mark = 9 GROUP BY COMPANYBASEUUID) as c on c.COMPANYBASEUUID = a.UUID
WHERE
a.QYLX = 3
AND a.ISDEL = 0
AND (
ifnull(a.QYMC, '') = ''
OR ifnull(a.XYDM, '') = ''
OR ifnull(a.DZ, '') = ''
OR ifnull(a.FW, '') = ''
OR ifnull(a.XKZBH, '') = ''
OR ifnull(a.JGJG, '') = ''
OR ifnull(a.FZJG, '') = ''
OR ifnull(a.QFR, '') = ''
OR ifnull(a.FZRQ_DATE, '') = ''
OR ifnull(a.YXQZ_DATE, '') = ''
OR ifnull(a.TYPE, '') = ''
OR ifnull(b.FR, '') = ''
OR ifnull(b.FZR, '') = ''
OR ifnull(c.facilityName, '') = ''
)
ORDER BY
a.YXQ_DATE DESC,
a.id DESC