<select id="countOrderData" resultType="java.util.HashMap" parameterType="com.ai.core.base.BaseSearchModel">
select c.REGION_ID,c.REGION_NAME,c.TYPE,
MAX(CASE c.ORDER_STATUS WHEN '1' THEN C.ORDER_COUNT ELSE 0 END) AS WAIT_DEAL,
MAX(CASE c.ORDER_STATUS WHEN '2' THEN c.ORDER_COUNT ELSE 0 END) AS OPEN_ACCOUNT,
MAX(CASE c.ORDER_STATUS WHEN '3' THEN c.ORDER_COUNT ELSE 0 END) AS ACTIVE_ORDER,
MAX(CASE c.ORDER_STATUS WHEN '4' THEN c.ORDER_COUNT ELSE 0 END) AS CANCEL_ORDER,
MAX(CASE c.ORDER_STATUS WHEN '5' THEN c.ORDER_COUNT ELSE 0 END) AS BACK_ORDER
from (
select b.REGION_ID, b.REGION_NAME, TYPE, ORDER_STATUS, ORDER_COUNT from (
select REGION_ID,
ORDER_STATUS, count(1) as ORDER_COUNT,TYPE from univ_referee_info
where pay_flag not in ('1','2','4') AND TYPE IS NOT NULL
AND COUNTY_ID not in ('382012','382013','382007','382011','382047','382046','382048','382041')
<!-- 福清 382012长乐 382013连江 382007平潭 382011晋江382047石狮382046南安382048惠安382041 -->
<if test="startPayTime != null and startPayTime != ''" >
and <![CDATA[ PAY_TIME > str_to_date(#{startPayTime},'%Y-%m-%d')]]>
</if>
<if test="endPayTime != null and endPayTime != ''" >
and <![CDATA[ PAY_TIME < str_to_date(#{endPayTime},'%Y-%m-%d')+1]]>
</if>
group by REGION_ID,TYPE,ORDER_STATUS
) a
join ai_sys_region b
on a.REGION_ID = b.REGION_ID) c
Group by REGION_ID,TYPE
UNION
select c.REGION_ID, c.REGION_NAME, c.TYPE,
MAX(CASE c.ORDER_STATUS WHEN '1' THEN C.ORDER_COUNT ELSE 0 END) AS WAIT_DEAL,
MAX(CASE c.ORDER_STATUS WHEN '2' THEN c.ORDER_COUNT ELSE 0 END) AS OPEN_ACCOUNT,
MAX(CASE c.ORDER_STATUS WHEN '3' THEN c.ORDER_COUNT ELSE 0 END) AS ACTIVE_ORDER,
MAX(CASE c.ORDER_STATUS WHEN '4' THEN c.ORDER_COUNT ELSE 0 END) AS CANCEL_ORDER,
MAX(CASE c.ORDER_STATUS WHEN '5' THEN c.ORDER_COUNT ELSE 0 END) AS BACK_ORDER
from (
select b.REGION_ID, b.REGION_NAME, TYPE, ORDER_STATUS, ORDER_COUNT from (
select COUNTY_ID,
ORDER_STATUS, count(1) as ORDER_COUNT,TYPE from univ_referee_info
where pay_flag not in ('1','2','4') AND TYPE IS NOT NULL
AND COUNTY_ID in ('382012','382013','382007','382011','382047','382046','382048','382041')
<if test="startPayTime != null and startPayTime != ''" >
and <![CDATA[ PAY_TIME > str_to_date(#{startPayTime},'%Y-%m-%d')]]>
</if>
<if test="endPayTime != null and endPayTime != ''" >
and <![CDATA[ PAY_TIME < str_to_date(#{endPayTime},'%Y-%m-%d')+1]]>
</if>
group by COUNTY_ID,TYPE,ORDER_STATUS
) a
join ai_sys_region b
on a.COUNTY_ID = b.REGION_ID) c
Group by REGION_ID,TYPE
</select>
MySQL列转行语句实例
最新推荐文章于 2024-09-06 08:27:13 发布