一些MySQL实例

业务需要同时实现：

update hydygx set STATE ="01" where USER_ID=1 and FRIEND_ID in (2,3,4);

update hydygx set STATE ="01" where USER_ID in (2,3,4) and FRIEND_ID =1;

我们都知道既然是两句update语句，肯定是两句话先后执行的，因为执行update之前，会对表加锁，另一个update语句就没法访问表了，我们可以改变方式去实现，如下：

update hydygx set STATE ="01" where (USER_ID=1 and FRIEND_ID in (2,3,4)) or (USER_ID in (2,3,4) and FRIEND_ID =1);

 

UNION ALL：将多个结果合并在一起显示。

select sum(SUM_ALL) SUM_ALL,sum(SUM_WEEK) SUM_WEEK,sum(SUM_MONTH) SUM_MONTH,sum(SUM_YEAR) SUM_YEAR from (
select sum(t.SETTLEMENT_TOTAL) SUM_ALL,0 SUM_WEEK,0 SUM_MONTH,0 SUM_YEAR from jsxx t,fjxx f
where t.ROOM_ID=f.ROOM_ID
and t.USER_ID=#{USER_ID}
and t.STATE ='01'
union ALL
select 0 SUM_ALL,sum(t.SETTLEMENT_TOTAL) SUM_WEEK,0 SUM_MONTH,0 SUM_YEAR from jsxx t,fjxx f
where t.ROOM_ID=f.ROOM_ID
and t.USER_ID=#{USER_ID}
and t.STATE ='01'
and f.SETTLEMENT_TIME >= DATE_SUB(NOW(), INTERVAL 1 WEEK)
union ALL
select 0 SUM_ALL,0 SUM_WEEK,sum(t.SETTLEMENT_TOTAL) SUM_MONTH,0 SUM_YEAR from jsxx t,fjxx f
where t.ROOM_ID=f.ROOM_ID
and t.USER_ID=#{USER_ID}
and t.STATE ='01'
and f.SETTLEMENT_TIME >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
union ALL
select 0 SUM_ALL,0 SUM_WEEK,0 SUM_MONTH,sum(t.SETTLEMENT_TOTAL) SUM_YEAR from jsxx t,fjxx f
where t.ROOM_ID=f.ROOM_ID
and t.USER_ID=#{USER_ID}
and t.STATE ='01'
and f.SETTLEMENT_TIME >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
) aa

 

update hydygx set STATE ="02" where (USER_ID=#{USER_ID} and FRIEND_ID in
<foreach collection="deleteFriendList" item="FRIEND_ID" index="index"
open="(" close=")" separator=",">
#{FRIEND_ID}
</foreach> ) or (USER_ID in <foreach collection="deleteFriendList" item="FRIEND_ID" index="index"
open="(" close=")" separator=",">
#{FRIEND_ID}
</foreach> and FRIEND_ID =#{USER_ID}
)

 

select DATE_FORMAT(f.SETTLEMENT_TIME,'%Y-%m-%d') dft,f.USER_ID,f.SETTLEMENT_RATE,p.SHORT_NAME,t.SETTLEMENT_TOTAL,f.ROOM_ID
from jsxx t,fjxx f,p_user p
where t.ROOM_ID=f.ROOM_ID
and p.USER_ID=f.USER_ID
and t.USER_ID=#{USER_ID}
and t.state ='01'
order by f.SETTLEMENT_TIME desc
limit #{pageBegin},#{pageSize}

转载于:https://www.cnblogs.com/huxiamei/p/6050254.html