利用代数表达式解决复杂SQL

---

https://www.cnblogs.com/ukzq/p/12184884.html

<select id="listKucunWithRiZhi" resultMap="BaseResultMap">
select DISTINCT rizhi2.mingchengguige as mingchengguige,
ifnull(rizhi1.qichukucun1,0) qichukucun,
ifnull(rizhi1.shourushuliang1,0) shourushuliang,
ifnull(rizhi1.fachushuliang1,0) fachushuliang,
ifnull(rizhi1.tuihuishuliang1,0) tuihuishuliang,
ifnull(rizhi1.jieyushuliang1,0) jieyushuliang
from (
select
DISTINCT (kucunrizhi.mingchengguige) as mingchengguige,
substring_index(group_concat(kucunrizhi.id order by kucunrizhi.createtime desc),',',1) as id,
substring_index(group_concat(kucunrizhi.xiangmuweihu order by kucunrizhi.createtime desc),',',1)as
xiangmuweihu,
substring_index(group_concat(kucunrizhi.rukukufang order by kucunrizhi.createtime desc),',',1)as rukukufang,
substring_index(group_concat(kucunrizhi.dalei order by kucunrizhi.createtime desc),',',1)as dalei,
substring_index(group_concat(kucunrizhi.qichukucun order by kucunrizhi.createtime),',',1)qichukucun1,
sum(kucunrizhi.shourushuliang)as shourushuliang1,
sum(kucunrizhi.fachushuliang)as fachushuliang1,
sum(kucunrizhi.tuihuishuliang)as tuihuishuliang1,
substring_index(group_concat(kucunrizhi.jieyushuliang order by kucunrizhi.createtime desc),',',1)jieyushuliang1,
substring_index(group_concat(kucunrizhi.jiagongyigong order by kucunrizhi.createtime desc),',',1)as
jiagongyigong,
substring_index(group_concat(kucunrizhi.createtime order by kucunrizhi.createtime desc),',',1)as createtime
from wuliaokucunrizhi as kucunrizhi
<where>
<if test="mingchengguige != null and mingchengguige != '' ">
<bind name="mingchengguigePattern" value="'%'+mingchengguige+'%'"/>
and kucunrizhi.mingchengguige like #{mingchengguigePattern}
</if>
<if test="jiagongyigong != null and jiagongyigong != '' ">
and kucunrizhi.jiagongyigong = #{jiagongyigong}
</if>
<if test="rukukufang != null and rukukufang != '' ">
and kucunrizhi.rukukufang = #{rukukufang}
</if>
<if test="xiangmuweihu != null and xiangmuweihu != ''">
and kucunrizhi.xiangmuweihu = #{xiangmuweihu}
</if>
<if test="dalei != null and dalei != '' ">
and kucunrizhi.dalei = #{dalei}
</if>
<if test="s_btime != null">
and kucunrizhi.createtime &gt;= #{s_btime}
</if>
<if test="s_etime != null">
and kucunrizhi.createtime &lt;= date_add(#{s_etime}, interval 1 day)
</if>
<if test="createtime != null">
and kucunrizhi.createtime = #{createtime}
</if>
<if test="updatetime != null">
and kucunrizhi.updatetime = #{updatetime}
</if>
</where>
GROUP BY kucunrizhi.mingchengguige
<if test="sort == null">
ORDER BY createtime DESC
</if>
<if test="sort != null and sort != ''">
order by sort{order}
</if>)rizhi1 right join wuliaokucunrizhi as rizhi2 on
rizhi1.mingchengguige = rizhi2.mingchengguige
</select>