复杂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 time

instr(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;