sql里的常用方法

sql常用

删除多余的type[1,2,3]数组

DELETE
FROM
park_config
WHERE
park_id =#{id}
and
type NOT IN
<foreach item="item" index="index" collection="type" open="(" close=")" separator=",">
#{item}
</foreach>

 一对多查询

        SELECT
        a.id,
        a.park_name,
        GROUP_CONCAT(b.type SEPARATOR ',')
            as type,
        a.create_by,
        a.begin_time,
        a.end_time,
        a.create_time,
        a.status,
        GROUP_CONCAT(b.id SEPARATOR ',') as pcId
        FROM
        park_base a
        LEFT JOIN park_config b ON a.id = b.park_id
        <where>
        a.status=1
        <if test="vo.parkName!=null and vo.parkName!= ''">
            and a.park_name like CONCAT('%', #{vo.parkName},'%')
        </if>
        </where>
        GROUP BY a.park_name
        order by a.create_time desc

 返回字符

select
suqian_user.id_card_ocr_status,
            CASE
                WHEN suqian_user.id_card_ocr_status = 0 THEN
                    '未开始'
                WHEN suqian_user.id_card_ocr_status = 1 THEN
                    '识别成功'
                WHEN suqian_user.id_card_ocr_status = 2 THEN
                    '未上传'
                WHEN suqian_user.id_card_ocr_status = 3 THEN
                    '识别错误'
                ELSE
                    ''
                END idcard_ocr_status_name
form 表

 

SELECT
b.id as value,
b.park_corp_name as label
FROM
park_base a
LEFT JOIN park_corp b ON a.id = b.park_id
WHERE
b.id IS NOT NULL
AND FIND_IN_SET(
a.id,
(
SELECT
GROUP_CONCAT( d.park_ids SEPARATOR ',' ) AS park_ids
FROM
`corp_base` c
LEFT JOIN corp_sign d ON c.id = d.corp_id
WHERE
d.status_no = 1
AND c.id = 110341
GROUP BY
c.id
)
);

 

<if test="startTime!=null and startTime != ''">
    and ca.apply_time <![CDATA[>=]]> #{startTime}
</if>
<if test="endTime!=null and endTime != ''">
    and ca.apply_time <![CDATA[<=]]> DATE_ADD(#{endTime},INTERVAL 1 DAY)

 

SELECT
  count(1) AS orderCount,
  b.corp_name as xCoordName
FROM
  order_base a
  LEFT JOIN corp_base b ON a.corp_id = b.id
WHERE
  DateDiff( DATE_FORMAT( a.create_time, '%Y-%m-%d' ), CURDATE( ) ) >=- 5
AND a.order_status = 1
  GROUP BY
    corp_name
　ORDER BY
    orderCount DESC
    LIMIT 5;

 

SELECT DATE_FORMAT(tb2.dateStr,'%m/%d') xCoordName,IFNULL(tb1.totalCount,0) orderCount FROM

 

 

    <insert id="batchInsert">
        INSERT INTO order_base (
            id,
            order_code,
            corp_id,
            project_id,
            user_id,
            user_name,
            id_card_value,
            mobile,
            payer_bank_name,
            payer_bank_account,
            payee_bank_name,
            payee_bank_account,
            order_money,
            real_money,
            order_status,
            pay_status,
            pay_type,
            result_desc,
            result_files,
            create_by,
            create_time,
            update_by,
            update_time,
            pay_time,
            in_order_flow_id,
            failure_reasons
        )
        VALUES
            <foreach collection="orderList" item="order" separator=",">
              (
                #{order.id},
                #{order.orderCode},
                #{order.corpId},
                #{order.projectId},
                #{order.userId},
                #{order.userName},
                #{order.idCardValue},
                #{order.mobile},
                #{order.payerBankName},
                #{order.payerBankAccount},
                #{order.payeeBankName},
                #{order.payeeBankAccount},
                #{order.orderMoney},
                #{order.realMoney},
                #{order.orderStatus},
                #{order.payStatus},
                #{order.payType},
                #{order.resultDesc},
                #{order.resultFiles},
                #{order.createBy},
                #{order.createTime},
                #{order.updateBy},
                #{order.updateTime},
                #{order.payTime},
                #{order.inOrderFlowId},
                #{order.failureReasons}
              )
            </foreach>
    </insert>

 

INSERT INTO storehouse_system.upload_log ( SELECT * FROM storehouse_platform.`upload_log` )

 

        SELECT
            *
        FROM
            eladmin.`user`
        WHERE
            (username LIKE 'GC%'
           OR username LIKE 'KH%')
          and length (username)=8

 

    <select id="getRequisitionPdf" resultType="java.util.Map">
        select
            a.apply_department applyDept,
            date_format(a.create_time,'%Y年%c月%d日') applyTime,
            (select corp_name from corp_base where id  = a.corp_id) corpName,
            a.requisition_content requisitionContent,
            a.payment_amount paymentAmount,
            (select park_corp_name from park_corp where id = a.park_corp_id) parkCorpName,
            a.confirm_amount confirmAmount,
            (select real_name from admin_user where id = a.apply_by) applyBy
        from requisition_base a
        where id = #{requisitionId}
    </select>

 

    /**
     * 导出请款单pdf
     * @param requisitionId
     * @return
     */
    @Override
    public String exportRequisitionPdf(Long requisitionId) {
        //生成pdf
        Map<String,Object> params = requisitionBaseMapper.getRequisitionPdf(requisitionId);
        params.put("capitalPaymentAmount", NumberChineseFormater.format(Double.parseDouble(params.get("paymentAmount").toString()),true,true));
        String tempUrl = PdfUtils.getPDF(params,"请款单模板.pdf");
        //上传oss
        UploadLogDto uploadLogDto = uploadFeignService.uploadFileByPath(tempUrl,"上传请款单",1L);
        if(uploadLogDto != null){
            FileUtil.deleteTempFile(tempUrl);
            return uploadLogDto.getFileUrl();
        }
        return null;
    }

 

    <update id="batchUpdateOrder" >
        UPDATE order_base
        SET order_status = #{status}
        WHERE
        id IN
        <foreach item = "id" collection = "orderIdList" separator = ","  open="(" close=")">
        (
         #{id}
        )
        </foreach>
    </update>

 

SELECT
*
FROM
servers a
WHERE
 FIND_IN_SET(
a.id,

'1,2222222222222263,2222222222222264'

)!=0;