一些MySQL執行個體

標籤：方式   tle   mat   order by   desc   short   ges   mit   sub   

業務需要同時實現：

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}

一些MySQL執行個體