复杂sql记录
灵感来了也记不住，在这里记录一下一些sql查询，方便以后复制粘贴
往角色为st_admin的用户的user_msg表中插入一条记录。 难点：角色为st_admin的用户个数不定
<insert id="sendMsgToRole">
insert into user_msg
(user_id,title,content,sender)
select tmp.*,
#{title},
#{content},
#{sender} from
(select user_id from user_role ur
left join role r
on ur.role_id=r.id
where r.role_name=#{role}
) tmp
</insert>往ids这一群用户的user_msg表中插入一条记录，并更新他们的消息提示状态； 如果ids为null，则往全体用户发送该消息。 难点：如果发送目标为全体，则消息提示状态需要更新全体，如果发送目标限制于ids，则消息提示状态仅更新该类用户。
foreach遍历的collection直接填写传递过来的集合名称即可，不用el表达式
<insert id="sendMsg">
insert into user_msg (user_id,title,content,sender)
select
u.id,
#{title},
#{content},
#{sender}
FROM user u
<choose>
<when test="ids !=null and ids.size>0">
where u.id
<foreach collection="ids" item="id" open="in(" close=")" separator=",">
#{id}
</foreach>
;
update user set msg_status=1
where user.id
<foreach collection="ids" item="id" open="in(" close=")" separator=",">
#{id}
</foreach>
</when>
<otherwise>
;
update user set msg_status=1;
</otherwise>
</choose>
</insert><!-- 按日查询 -->
SELECT DATE_FORMAT(created_date,'%Y-%m-%d') as time,sum(money) money FROM o_finance_detail where org_id = 1000 GROUP BY time
<!-- 按月查询 -->
SELECT DATE_FORMAT(created_date,'%Y-%m') as time,sum(money) money FROM o_finance_detail where org_id = 1000 GROUP BY time
<!-- 按年查询 -->
SELECT DATE_FORMAT(created_date,'%Y') as time,sum(money) money FROM o_finance_detail where org_id = 1000 GROUP BY time
<!-- 按周查询 -->
SELECT DATE_FORMAT(created_date,'%Y-%u') as time,sum(money) money FROM o_finance_detail where org_id = 1000 GROUP BY time 查询是否包含某个字符
SELECT DATE_FORMAT(create_time, '%Y-%m-%d') as time, count(*) num
FROM zjmj_appback.back_refund
where create_time is not null
and instr(livestock_ids,',') = 0
GROUP BY timeinstr(livestock_ids,’,’) 等于0时，表示不包含 instr(livestock_ids,’,’) 大于0时，表示包含
查询某个字符出现的次数
SELECT id, length(field) - length(replace(field,',',''))+1 as count FROM `table` where 1昨天一天内 where TO_DAYS(NOW()) - TO_DAYS(create_time) <![CDATA[ <= ]]> 1;
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2020/03/17 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
腾讯云开发者
