通过城市编号分组,查询统计数据,如果该城市没有数据,则会过滤掉此城市组。
需求:查询所有城市的数据,没有数据的显示为0.
首先通过DISTINCT查询所有的城市作为左连接中的主表,右表为查询出所有的有数据的城市分组,通过城市编码关联,取主表的城市和右表的数据,如果主表城市对应的数据没有,则用nvl(aaa,0)取0补全。
SELECT DISTINCT
bg.TM_SALE_CITY,
A.DTL_CH_NAME,
A.DTL_EN_NAME,
NVL (aa.FINA_GROUP_MONEY, 0) AS FINA_GROUP_MONEY,
NVL (aa.FINA_GROUP_AMOUNT, 0) AS FINA_GROUP_AMOUNT,
NVL (aa.PAY_AMOUNT, 0) AS PAY_AMOUNT,
NVL (aa.FINA_SERVICE_FEE, 0) AS FINA_SERVICE_FEE,
NVL (aa.FINA_RESERVE_AMOUNT, 0) AS FINA_RESERVE_AMOUNT,
NVL (aa.FINA_FEE_AMOUNT, 0) AS FINA_FEE_AMOUNT,
NVL (aa.CONSUME_TOTAL_EUR, 0) AS CONSUME_TOTAL_EUR,
( SELECT NVL(SUM(NVL(c.CONSUME_TOTAL_EUR, 0)), 0) FROM TM_COST_EXPENDITURE c WHERE bg.TM_SALE_CITY = c.CITY_CODE
and c.EXPENDITURE_STATUS = 'H'
<!-- 出团日期 -->
<if test="group_end_month_start !=null and group_end_month_start != '' ">
and to_char(to_date(c.EXPENDITURE_DATE,'yyyy-MM-dd'),'yyyy-MM') <![CDATA[>=]]> #{group_end_month_start}
</if>
<if test="group_end_month_end !=null and group_end_month_end != '' ">
and to_char(to_date(c.EXPENDITURE_DATE,'yyyy-MM-dd')