常用SQL

1.先查重 再插入

INSERT INTO web_user(user_name, password) SELECT 'user_name2', 'password2' FROM DUAL WHERE NOT EXISTS(SELECT user_name FROM web_user WHERE user_name = 'xxx');

2.多情况

UPDATE CASHDETAIL
        SET
        CASH_BALANCE = CASE when (TIMESTAMPDIFF(MINUTE,#{2},NOW())>=60) THEN
        (CASE when (TIMESTAMPDIFF(MINUTE,#{2},NOW())*60/(SELECT TIME_TO_SEC( TIMEDIFF(END_TIME,START_TIME)) FROM
        SOSTAR_ORDER WHERE ORDER_ID =#{0})>'1')
        then '1'
        ELSE (TIMESTAMPDIFF(MINUTE,#{2},NOW())*60/(SELECT TIME_TO_SEC(TIMEDIFF(END_TIME,START_TIME)) FROM
        SOSTAR_ORDER WHERE ORDER_ID =#{0}))
        end
        )
        ELSE '0.2' END
        WHERE ORDER_ID =#{0} AND STAFF_USERID=#{1}
        AND DATE=CURDATE()

3.时间

UPDATE CASHDETAIL
        SET
        CASH_BALANCE = (TIMESTAMPDIFF(MINUTE,#{2},NOW())*60/(SELECT TIME_TO_SEC(TIMEDIFF(END_TIME,START_TIME)) FROM
        SOSTAR_ORDER WHERE ORDER_ID =#{0}))
        WHERE ORDER_ID =#{0} AND STAFF_USERID=#{1}

4.根据数字结果输出文字

SELECT
        (CASE
         WHEN
         TO_USER =#{0}  THEN
        sum(CASH_TOTAL-COMMISSION) ELSE sum(CASH_TOTAL) END )AS cashTotal,
        ORDER_ID AS orderId,
        DATE AS date,
        (CASE
         WHEN
         TO_USER =#{0}  THEN
           (CASE WHEN TYPE = '1' THEN '1' WHEN TYPE='3' then '5' WHEN TYPE = '4' THEN '1' ELSE '4'
           END)
         ELSE
          (CASE WHEN TYPE = '2' THEN '3' WHEN TYPE='3' then '6' ELSE '2'
           END)
            END) AS type,
        (CASE
         WHEN
         TO_USER =#{0}  THEN
            TO_CASH_AMOUNT

         ELSE
           min(FROM_CASH_AMOUNT)

            END) AS cashAmount,
            (CASE WHEN (TO_USER =#{0} AND TYPE = '1') THEN CONCAT('订单',ORDER_ID,' 工资收入')
                  WHEN (TO_USER =#{0} AND TYPE = '2') THEN CONCAT('账户提现')
                  WHEN (TO_USER =#{0} AND TYPE = '3') THEN CONCAT('订单',ORDER_ID,' 小费收入')
                  WHEN (TO_USER =#{0} AND TYPE = '4') THEN CONCAT('订单',ORDER_ID,' 加班收入')
                  WHEN (FROM_USER =#{0} AND TYPE = '1') THEN CONCAT('订单',ORDER_ID,' 工资支付')
                  WHEN (FROM_USER =#{0} AND TYPE = '2') THEN CONCAT('账户充值')
                  WHEN (FROM_USER =#{0} AND TYPE = '3') THEN CONCAT('订单',ORDER_ID,' 小费支出')
                  WHEN (FROM_USER =#{0} AND TYPE = '4') THEN CONCAT('订单',ORDER_ID,' 加班支出')
                  ELSE '' end) AS descri
        FROM
        CASH_FLOW
        WHERE
        TO_USER = #{0} OR   FROM_USER=#{0} GROUP BY DATE,ORDER_ID,TYPE
        ORDER BY DATE  DESC,FLOW_ID DESC

5.计算
coalesce:返回第一个非null记录

SELECT
        COALESCE(SUM(CASH_TOTAL-COMMISSION),0)
        FROM
        CASH_FLOW
        WHERE
        TO_USER =#{0}
        AND
        TYPE IN ('1','4','3')

6.左连接

SELECT
        cf.FLOW_ID AS flowId,
        cf.CASH_TOTAL as cashTotal,
        cf.TO_USER as userId,
        cf.DATE as date,
        cf.TO_CASH_AMOUNT as cashAmount,
        cf.WEB_FLG,
        us.USER_TYPE AS type,
        cf.payee_account,
        cf.payee_real_name,
        (CASE WHEN  us.USER_TYPE='0' then s.NAME  ELSE c.COMPANY_NAME  end)AS userName
        FROM
        CASH_FLOW cf,USER us
        LEFT  JOIN staff s
        on s.USER_ID= us.USER_ID
        LEFT JOIN  company c
        ON c.USER_ID= us.USER_ID
        WHERE
        WEB_FLG ='0'
        AND us.USER_ID=cf.TO_USER
        <if test="userName != null and userName !=''">
            AND( c.COMPANY_NAME like CONCAT('%',#{userName},'%') or s.Name like CONCAT('%',#{userName},'%'))
        </if>

7.foreach（传入参数为list）

SELECT
        a.USER_ID AS userId,
        a.NAME AS name,
        a.PHONE AS phone,
        a.SEX AS sex,
        a.AGE AS age,
        a.CLOSE_RATE AS closeRate,
        a.PIC_PATH AS picPath,
        a.AUTHENTICATION AS authentication,
        a.INTRODUCTION AS introduction,
        a.NICK_NAME AS nickName,
        a.FINSHED_ORDERS AS finishedOrders,
        b.STATUS AS staffStatus,
        b.EVALUATE_FLG AS evaluateFlg
        from
        STAFF a,ORDER_RELATION b
        where
        b.ORDER_ID=#{orderId}
        and b.STAFF_USER_ID=a.USER_ID
        and b.STATUS in
        <foreach item="item" index="index" collection="relationStatus" open="(" separator="," close=")">
            #{item}
        </foreach>
        order by b.EVALUATE_FLG ASC,b.STATUS DESC

8 分页（50条后面的50条）

select * from fav where 1=1 limit 50,50;

9.查询重复数据

select * from [表A] where id in (select id from [表A] group by id having count(id) >1 )

10.in按传入数组顺序排序

SELECT * FROM lft_group_member WHERE gchat_id = '1388617207' and mem_id IN(15359676994870010007,15359675405590010006,15361374767230010006,15361367552260010004,15364902450500014563)
order by instr(',15359676994870010007,15359675405590010006,15361374767230010006,15361367552260010004,15364902450500014563,',concat(',',mem_id,','))