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,','))
