表单查询sql

比眉伴天荒 2022-04-13 12:57 248阅读 0赞

SELECT
            a.order_no AS 'orderId',
            a.third_merchant_id AS "supplierId",
            si.seller_name AS "suppliers",
            a.`member_name` AS 'memberId',
            CASE
            WHEN a.ship_mobile IS NOT NULL AND a.ship_mobile != '' THEN a.ship_mobile
            ELSE a.ship_tel
            END AS 'shipTel',
            a.order_createtime AS 'createtime',
            a.payment AS 'payment',
            a.payable_amount AS 'payableAmount',
            a.payed AS 'payed',
            a.discount AS 'discount',
            a.pmt_order as pmtOrder,
            a.pmt_goods as pmtGoods,
            CASE  a.production_status
            WHEN '1'
            THEN '待审核'
            WHEN '2'
            THEN '待分配'
            WHEN '3'
            THEN '生产中'
            WHEN '4'
            THEN '生产完成'
            WHEN '5'
            THEN '已发货'
            WHEN '6'
            THEN '已完成'
            WHEN '7'
            THEN '新开单(门店)'
            WHEN '11'
            THEN '已作废'
            END AS 'productionStatus',
            a.payment_time AS 'paymentTime',
            a.project_name AS "projectName",
            a.memo AS "memo",
            a.production_request as 'productionRequest',
            (SELECT GROUP_CONCAT(`addon`) FROM `erp_order_item` WHERE order_no=a.order_no) AS addon
            FROM
            erp_order a
            LEFT JOIN seller_info si ON a.third_merchant_id = si.seller_id
            WHERE a.third_merchant_id=#{map.sellerId} and a.production_status !=11
            <if test="map.orderId != null and map.orderId != ''">
                and a.order_no LIKE concat('%', #{map.orderId}, '%')
            </if>
            <if test="map.name != null and map.name != ''">
                and a.`member_name` LIKE concat('%', #{map.name}, '%')
            </if>
            <if test="map.payment != null and map.payment != ''">
                and a.payment=#{map.payment}
            </if>
            <if test="map.payStatus != null and map.payStatus != ''">
                and a.pay_status=#{map.payStatus}
            </if>
            <if test="map.productionStatus != null and map.productionStatus != ''">
                and a.production_status=#{map.productionStatus}
            </if>
            <if test="map.beginCreatetime != null and map.beginCreatetime != ''">
                and DATE_FORMAT(a.order_createtime, '%Y-%m-%d %H:%i:%S') >= DATE_FORMAT(#{map.beginCreatetime}, '%Y-%m-%d %H:%i:%S')
            </if>
            <if test="map.endCreatetime != null and map.endCreatetime != ''">
                and DATE_FORMAT(a.order_createtime, '%Y-%m-%d %H:%i:%S') <= DATE_FORMAT(#{map.endCreatetime}, '%Y-%m-%d %H:%i:%S')
            </if>
            <if test="map.beginPaytime != null and map.beginPaytime != ''">
                and DATE_FORMAT(a.payment_time, '%Y-%m-%d %H:%i:%S') >= DATE_FORMAT(#{map.beginPaytime}, '%Y-%m-%d %H:%i:%S')
            </if>
            <if test="map.endPaytime != null and map.endPaytime != ''">
                and DATE_FORMAT(a.payment_time, '%Y-%m-%d %H:%i:%S') <= DATE_FORMAT(#{map.endPaytime}, '%Y-%m-%d %H:%i:%S')
            </if>
            ORDER BY a.order_createtime DESC