業務需求中，連表查詢根據表的某個字段，這個字段可能為空
如果為空，根據這個表的另外一個字段查詢，用union連接對結果去重即可

<select id="selectByNull" resultMap="BaseResultMap">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_NOFROM BIZ_SPECIMEN_LAB_RECEPTION bLEFT JOIN BIZ_ASSAY_ORDER_SAMPLE o on o.SAMPLE_NO=b.SAMPLE_NOLEFT JOIN RES_LABORATORY r on r.ID=o.SAMP_PREP_DEPT_CODELEFT JOIN BIZ_ASSAY_TASK_INFO t ON t.SAMPLE_NO=b.SAMPLE_NOLEFT JOIN BIZ_ASSAY_ITEM_DATA d on d.SPECIMEN_ID=b.SPECIMEN_IDWHERE 1=1AND d.ASSAY_STATUS=0<if test="laboratoryId!=null and laboratoryId!=''">AND r.ID=#{laboratoryId}</if><if test="sampleNo!=null and sampleNo!=''">AND b.SAMPLE_NO=#{sampleNo}</if><if test="fromDate!=null and fromDate!=''">and <![CDATA[b.RECEIVED_SPECIMEN_TIME>=to_date(#{fromDate}, 'yyyy-mm-dd') ]]></if><if test="toDate!=null and toDate!=''">and <![CDATA[b.RECEIVED_SPECIMEN_TIME<=to_date(#{toDate}, 'yyyy-mm-dd') ]]></if>UNIONSELECT 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_NOFROM BIZ_SPECIMEN_LAB_RECEPTION bLEFT JOIN BIZ_ASSAY_ORDER_SAMPLE o on o.SAMPLE_NO=b.SAMPLE_NOLEFT JOIN RES_LABORATORY r on r.ID=o.SAMP_PREP_DEPT_CODELEFT JOIN BIZ_ASSAY_TASK_INFO t ON t.SAMPLE_NO=b.SAMPLE_NOLEFT JOIN BIZ_ASSAY_ITEM_DATA d on d.ASSAY_TASK_INFO_ID=t.IDWHERE 1=1AND d.ASSAY_STATUS=0<if test="laboratoryId!=null and laboratoryId!=''">AND r.ID=#{laboratoryId}</if><if test="sampleNo!=null and sampleNo!=''">AND b.SAMPLE_NO=#{sampleNo}</if><if test="fromDate!=null and fromDate!=''">and <![CDATA[b.RECEIVED_SPECIMEN_TIME>=to_date(#{fromDate}, 'yyyy-mm-dd') ]]></if><if test="toDate!=null and toDate!=''">and <![CDATA[b.RECEIVED_SPECIMEN_TIME<=to_date(#{toDate}, 'yyyy-mm-dd') ]]></if> </select>

union，默認為union distinct，已經對結果去重了，所以不用加distinct
union，返回去重后的數據
union all，返回所有的數據

總結

以上是生活随笔為你收集整理的mybatis的union查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。