|
业务需求中，连表查询根据表的某个字段，这个字段可能为空 如果为空，根据这个表的另外一个字段查询，用union连接对结果去重即可
SELECT DISTINCT b.SAMPLE_NO,
b.ID,
b.SPECIMEN_ID,
b.RECEIVED_SPECIMEN_TIME,
b.RECEIVED_SPECIMEN_PLACE,
b.RECEIVER,
b.SENDER,
b.REMARK,
b.RECORD_STATUS,
b.CREATE_TIME,
b.MODIFY_TIME,
r.Name,
t.ASSAY_NO
FROM BIZ_SPECIMEN_LAB_RECEPTION b
LEFT JOIN BIZ_ASSAY_ORDER_SAMPLE o on o.SAMPLE_NO=b.SAMPLE_NO
LEFT JOIN RES_LABORATORY r on r.ID=o.SAMP_PREP_DEPT_CODE
LEFT JOIN BIZ_ASSAY_TASK_INFO t ON t.SAMPLE_NO=b.SAMPLE_NO
LEFT JOIN BIZ_ASSAY_ITEM_DATA d on d.SPECIMEN_ID=b.SPECIMEN_ID
WHERE 1=1
AND d.ASSAY_STATUS=0
AND r.ID=#{laboratoryId}
AND b.SAMPLE_NO=#{sampleNo}
and .RECEIVED_SPECIMEN_TIME>=to_date(#{fromDate}, 'yyyy-mm-dd') ]]>
and .RECEIVED_SPECIMEN_TIME
UNION
SELECT DISTINCT b.SAMPLE_NO,
b.ID,
b.SPECIMEN_ID,
b.RECEIVED_SPECIMEN_TIME,
b.RECEIVED_SPECIMEN_PLACE,
b.RECEIVER,
b.SENDER,
b.REMARK,
b.RECORD_STATUS,
b.CREATE_TIME,
b.MODIFY_TIME,
r.Name,
t.ASSAY_NO
FROM BIZ_SPECIMEN_LAB_RECEPTION b
LEFT JOIN BIZ_ASSAY_ORDER_SAMPLE o on o.SAMPLE_NO=b.SAMPLE_NO
LEFT JOIN RES_LABORATORY r on r.ID=o.SAMP_PREP_DEPT_CODE
LEFT JOIN BIZ_ASSAY_TASK_INFO t ON t.SAMPLE_NO=b.SAMPLE_NO
LEFT JOIN BIZ_ASSAY_ITEM_DATA d on d.ASSAY_TASK_INFO_ID=t.ID
WHERE 1=1
AND d.ASSAY_STATUS=0
AND r.ID=#{laboratoryId}
AND b.SAMPLE_NO=#{sampleNo}
and .RECEIVED_SPECIMEN_TIME>=to_date(#{fromDate}, 'yyyy-mm-dd') ]]>
and .RECEIVED_SPECIMEN_TIME
union，默认为union distinct，已经对结果去重了，所以不用加distinct union，返回去重后的数据 union all，返回所有的数据
|