MySQL 中根据关键字查询多个字段

佚名 6年前 (2019-04-23) 随笔 1603人围观抢沙发百度已收录

<select id="selectByItem" resultType="com.shinho.dc3.order.dto.dnOrder.DnOrderItemDto" parameterType="com.shinho.dc3.order.dto.dnOrder.DnOrderItemSearchDto" >
  SELECT aa.dn_vbeln,
          aa.customername,
          aa.PERSON_NAME,
          aa.CARNUM,
          aa.DRIVER,
          aa.SHDAT,
          aa.MATNR,
          aa.MAKTX,
          aa.T_COUNT,
          aa.I_COUNT,
          aa.SY_COUNT,
          aa.I_UNIT_NAME,
          aa.DN_AMT,
          aa.ST_AMT,
          aa.SY_AMT,
          aa.remark
  FROM (SELECT A.VBELN as dn_vbeln,
            A.NAME as customername,
            A.PERSON_NAME,
            D.car_name carnum,
            D.DRIVER,
            D.SHDAT,
            B.MATNR,
            B.MAKTX,
            B.T_COUNT,
            B.I_COUNT,
            B.T_COUNT - I_COUNT as SY_COUNT,
            B.I_UNIT_NAME,
            B.T_COUNT * B.ZH_PRICE as DN_AMT,
            B.I_COUNT * B.ZH_PRICE as ST_AMT,
            (B.T_COUNT - I_COUNT) * B.ZH_PRICE as SY_AMT,
            '部分签收' remark
        FROM dn_order  A
        JOIN dn_order_item  B
        ON A.VBELN = B.VBELN
        AND A.DID = B.DID
        AND B.T_COUNT != B.I_COUNT
        LEFT JOIN pc_orders  C
        ON A.VBELN = C.DN_VBELN AND A.DID = C.DID
        LEFT JOIN pc_order D ON C.VBELN = D.VBELN AND C.DID = D.DID
        WHERE     1 = 1
        AND B.T_COUNT &gt; 0
        <if test="dateStart != null and dateStart.trim() != ''" >
          AND D.SHDAT &gt;=#{dateStart}
        </if>
        <if test="dateEnd != null and dateEnd.trim() != ''" >
          AND D.SHDAT &lt;=#{dateEnd}
        </if>
        AND A.did = #{did}
  <if test="key != null and key != ''">
    <bind name="key" value="'%' + key + '%'"/>
    and (UPPER (A.VBELN) like #{key} or
           UPPER (A.KUNNR) like #{key} or
           UPPER (A.NAME) like #{key} or
           UPPER (A.PERSON_NAME) like #{key} or
           UPPER (D.CARNUM) like #{key} or
           UPPER (B.MATNR) like #{key} or
           UPPER (B.MAKTX) like #{key}
    )
  </if>
  UNION ALL
        SELECT A.VBELN dn_vbeln,
            A.NAME as customername,
            A.PERSON_NAME,
            D.car_name carnum,
            D.DRIVER,
            D.SHDAT,
            B.MATNR,
            B.MAKTX,
            B.T_COUNT,
            0  I_COUNT,
            I_COUNT SY_COUNT,
            B.I_UNIT_NAME,
            B.T_COUNT * B.ZH_PRICE DN_AMT,
            0  ST_AMT,
            I_COUNT * B.ZH_PRICE SY_AMT,
            '全部未收' REMARK
        FROM dn_order A
        JOIN dn_order_item B
        ON     A.VBELN = B.VBELN
        AND A.DID = B.DID
        AND B.T_COUNT = B.I_COUNT
        AND A.STATUS = '20'
        LEFT JOIN pc_orders C
        ON A.VBELN = C.DN_VBELN AND A.DID = C.DID
        LEFT JOIN pc_order D ON C.VBELN = D.VBELN AND C.DID = D.DID
        WHERE     1 = 1
        AND B.T_COUNT &gt; 0
        <if test="dateStart != null and dateStart.trim() != ''" >
          AND D.SHDAT &gt;=#{dateStart}
        </if>
        <if test="dateEnd != null and dateEnd.trim() != ''" >
          AND D.SHDAT &lt;=#{dateEnd}
        </if>
        AND A.did = #{did}
  <if test="key != null and key != ''">
    <bind name="key" value="'%' + key + '%'"/>
    and (UPPER (A.VBELN) like #{key} or
    UPPER (A.KUNNR) like #{key} or
    UPPER (A.NAME) like #{key} or
    UPPER (A.PERSON_NAME) like #{key} or
    UPPER (D.CARNUM) like #{key} or
    UPPER (B.MATNR) like #{key} or
    UPPER (B.MAKTX) like #{key}
    )
  </if>
        ) aa
  ORDER BY aa.REMARK, aa.SHDAT DESC
</select>