SQL实用总结
获取数量 ，比对时间
<select id="getExamUserCount" resultType="_int">
select
count(*)
from
exam_record er
left join exam e on e.id = er.exam_id
where
er.candidate_id = #{userId} and
er.deleted = 0 and
er.score is not null
and er.begin_date between #{beginDate} and #{endDate};
</select>
获取考试最高分
<select id="getHighScore" resultType="com.shsajt.datacenter.common.dto.report.ExamRank">
select
e.name,
er.exam_id as examId,
er.score,
er.begin_date as date
from
exam_record er
left join exam e on e.id = er.exam_id
where
er.candidate_id = #{userId} and
er.deleted = 0 and
er.score is not null
order by er.score desc limit 1;
</select>
获取考试排名位次 （比对JSON参数）
select
count(distinct er.id)
from
exam_record er
where
er.candidate_id = #{userId} and
er.deleted = 0 and
er.exam_id = #{examId} and
er.score is not null and
JSON_UNQUOTE(JSON_EXTRACT(er.parameter, '$.companyId')) = #{companyId} and
er.score <![CDATA[ >= ]]> #{score}
获取最喜欢的游戏（GROUP BY）
<select id="getMostLikeGame" resultType="com.shsajt.datacenter.common.dto.report.Game">
SELECT sc.game_id as gameId, count(sc.game_id) as count , g.name as likeGame FROM score sc
left join game g on sc.game_id = g.id
where
sc.user_id = #{userId} and
sc.deleted = 0
and sc.gmt_create between #{beginDate} and #{endDate}
group by sc.game_id order by count desc limit 1
</select>
获取某游戏 游玩分数排名
<select id="getUserLikeGameInfo" resultType="com.shsajt.datacenter.common.dto.report.GameDTO">
select (@i:= @i+1) as `rank` , score , user_id from
(SELECT max(sc.score) as score , sc.user_id FROM score sc
where
sc.deleted = 0 and
sc.game_id = #{gameId} and
sc.company_id = #{companyId}
and sc.gmt_create between #{beginDate} and #{endDate}
group by sc.user_id order by score desc) temp ,
(SELECT @i:= 0) b
</select>
某一天玩的最多
<select id="getMixGameDate" resultType="com.shsajt.datacenter.common.dto.report.Game">
select date_format(gmt_create , '%Y-%m-%d') as maxPlayDate,
count(sc.id) as maxPlayNum
from score sc
where
sc.deleted = 0 and
sc.user_id = #{userId} and
sc.gmt_create between #{beginDate} and #{endDate}
group by date_format(gmt_create , '%Y-%m-%d')
order by maxPlayNum desc
limit 1
</select>
按类型统计数量
<select id="getTotal" resultType="com.shsajt.common.dto.WorkTypeCountDTO">
select
count(case when (temp.work_type_id =8 and temp.looped = 1 )then temp.id end) as checkNum,
count(case when temp.work_type_id =9 then temp.id end) as trainNum,
count(case when temp.work_type_id =10 then temp.id end) as guideNum,
count(case when temp.work_type_id =11 then temp.id end) as serveNum,
count(case when temp.work_type_id =12 then temp.id end) as meetNum,
count(case when temp.work_type_id =13 then temp.id end) as reportNum,
count(case when temp.work_type_id =14 then temp.id end) as otherNum
from
(
select distinct cw.*, cl.* from contract_work cw
left join closed_loop cl on cw.id = cl.work_id
left join work_and_item wai on cw.id = wai.work_id
left join contract_item ci on wai.item_id = ci.id
<where>
cw.deleted = 0 and ci.deleted = 0
<if test="beginDate != null">
and cw.gmt_create <![CDATA[ >= ]]> #{beginDate}
</if>
<if test="endDate != null">
and cw.gmt_create <![CDATA[ <= ]]> #{endDate}
</if>
<if test="contractId != null">
and ci.contract_id = #{contractId}
</if>
<if test="officeId != null">
and cw.office_id = #{officeId}
</if>
<if test ="states != null and states.size() >0">
and cw.work_state_id in
<foreach collection='states' item='item' index='index' open='(' separator=',' close=')'>
#{item}
</foreach>
</if>
</where>
) temp
更新操作
<update id="updateWork" >
update
contract_work cw
<trim prefix="set" suffixOverrides="," suffix="where cw.id = #{work.id}">
cw.value = #{work.value},
cw.begin_date = #{work.beginDate},
cw.end_date = #{work.beginDate},
cw.meet_topic = #{work.meetTopic},
cw.meet_place = #{work.meetPlace},
cw.serve_object = #{work.serveObject},
cw.check_serial = #{work.checkSerial},
cw.name = #{work.name},
cw.modifier_id = #{work.modifierId},
cw.gmt_modified = #{work.gmtModified},
cw.comment = #{work.comment}
</trim>
<update id="updateUserRecord">
update user_record ur set ur.visit_num = #{visitNum}
<if test = "firstVisitTime != null">
, ur.first_visit_time = #{firstVisitTime}
</if>
, ur.last_visit_time = #{lastVisitTime}
where ur.user_id = #{userId}
and ur.company_id = #{companyId}
</update>
合同执行样例
<if test="vo.looped != null and vo.looped == 0 and vo.creatorId != null and vo.permission == true">
and (
<!-- 本身就有发现隐患数但是还未闭环的检查 -->
((cl.looped = 0 or cl.looped is null) and cw.work_type_id = 8 and cw.value is not null and cw.value != 0)
or
<!-- 不是检查但是 可以看到自己的 1，4，5状态和所有人的待审核状态 2 -->
( cw.work_type_id != 8 and
(
(
cw.creator_id = #{vo.creatorId} and cw.work_state_id in (1,4,5)
)
or
(cw.work_state_id = 2)
)
)
<!-- 是检查但是本事就是没有发现隐患数的，查询条件等同于非检查类型的合同执行 -->
or
(
cw.work_type_id = 8
and
(cw.value is null or cw.value = 0)
and
(
(cw.creator_id = #{vo.creatorId} and cw.work_state_id in (1,4,5) )
or
(cw.work_state_id = 2)
)
)
)
</if>
用查询出来的列当成新表 FROM
<select id="getUserIdsByUserId" resultType="Integer">
select user_id
from user_company
<where>
deleted = 0
and company_id =(
select company_id
from user_company
<where>
deleted = 0
<if test="userId != null">
and user_id = #{userId}
</if>
</where>
)
</where>
</select>
获取用户能看到的公文以及需要让用户看到的信息
<select id ="getInformUserPage" resultType ="com.shsajt.common.dto.InformPageDTO">
select
i.inform_id ,
name ,
i.state_id ,
a.creator_id as handleUserId, //批办人
a.gmt_create as handleTime, //批办时间
b.creator_id as finishUserId, //办结人
b.gmt_create as finishTime, //办结时间
ifnull(sp.sign_num,0) as signNum , //签阅数
ifnull(sp.member_num,0) as memberNum, //总人数
i.creator_id ,
i.gmt_create,
sg.sign_time //如果用户签阅了，有签阅时间
from inform i
left join sign_progress sp on i.inform_id = sp.inform_id
left join sign sg on i.inform_id = sg.inform_id
left join (
select * from content c
where state_id =2
)a on i.inform_id = a.inform_id
left join (
select * from content c
where state_id =3
)b on i.inform_id = b.inform_id
where i.deleted =0 and sg.deleted = 0
<if test="vo.officeId != null">
and office_id = #{vo.officeId}
</if>
<if test="vo.stateId != null">
and i.state_id = #{vo.stateId}
</if>
<if test="userId != null">
and sg.member_id = #{userId}
</if>
<if test="vo.beginDate != null and vo.endDate != null">
and i.gmt_create between #{vo.beginDate} and #{vo.endDate}
</if>
group by i.inform_id
order by i.gmt_create desc
</select>
判断字段是不是 is null
<select id="getHandleUserNum" resultType="_int">
select count(distinct ifm.inform_id) from inform ifm
left join sign sg on ifm.inform_id = sg.inform_id
<where>
ifm.deleted = 0 and sg.deleted = 0 and sg.sign_time is null
<if test="officeId != null">
and ifm.office_id = #{officeId}
</if>
<if test="userId != null">
and sg.member_id = #{userId}
</if>
</where>
</select>
获取公文已办结和待接收的数量
<select id="getHandleOfficeNum" resultType="com.shsajt.common.dto.HandleNumDTO">
select
count(case when (temp.state_id = 2 and temp.sign_num = temp.member_num)then temp.inform_id end) as finishNum,
count(case when temp.state_id = 1 then temp.inform_id end) as acceptNum
from
(
select distinct ifm.inform_id, ifm.state_id,
ifnull(sp.sign_num, 0) as sign_num, ifnull(sp.member_num,0)as member_num from inform ifm
left join sign_progress sp on ifm.inform_id = sp.inform_id
<where>
ifm.deleted = 0
<if test="officeId != null">
and ifm. office_id = #{officeId}
</if>
</where>
) temp
