— 需求: 应出未出水电查询

方案：

select z.id,
z.MALL_CODE,
z.MALL_NAME,
z.COMPANY_ID,
z.BELONG_NAME,
z.COMPANY_ID,
z.company_no,
z.COMPANY_NAME,
SUBSTRING_INDEX(GROUP_CONCAT( z.cont_no ORDER BY z.CONT_END_DATE DESC),',','1') AS cont_no ,
min(z.CONT_BEGIN_DATE) CONT_BEGIN_DATE,
max(z.CONT_END_DATE) CONT_END_DATE,
max(z.WATER_ACCOUNT_END) WATER_ACCOUNT_END ,
max(z.ELECTRICITY_ACCOUNT_END) ELECTRICITY_ACCOUNT_END ,
(case when INSTR(GROUP_CONCAT(z.pay_cycle), '2') > 0 then '2' when INSTR(GROUP_CONCAT(z.pay_cycle), '3') > 0 then '3' else '1' end)  pay_cycle,
z.E3,
z.E4,
z.B2,
(case when INSTR(GROUP_CONCAT(DISTINCT z.CONT_TYPE), ',') <= 0 then z.CONT_TYPE else null end) CONT_TYPE,
z.B3,
z.is_charge_water,
z.is_charge_electricity,
z.is_charge,
max(z.B4) B4,
max(z.E1) E1,
max(z.formatB4) formatB4,
max(z.formatE1) formatE1 from (
SELECT y.*,
date_format(y.B4,'%Y-%m') as formatB4,
date_format(y.E1,'%Y-%m') as formatE1
from (
select x.*,
(case 
when x.B3 is not null and x.pay_cycle ='1' then DATE_ADD(x.B3 ,INTERVAL 2 MONTH) 
when x.B3 is not null and x.pay_cycle ='2' then MAKEDATE(EXTRACT(YEAR FROM x.B3),1) + interval QUARTER(x.B3)*3+1 month
when x.B3 is not null and x.pay_cycle ='3' then DATE_ADD(x.B3 ,INTERVAL 4 MONTH) 
 else null end ) as B4,
(case when x.is_charge_water = 'N' and is_charge_electricity = 'Y' then x.E4 
when x.is_charge_water = 'Y' and is_charge_electricity = 'N' then x.E3 
when x.is_charge_water = 'Y' and is_charge_electricity = 'Y' and x.E3 is not null and x.E4 is not null then IF(x.E3>=x.E4,x.E4,x.E3) else null end) as E1
from (
select t.*,
(case when t.B2 between t.CONT_BEGIN_DATE and t.CONT_END_DATE then t.B2 when t.B2 > t.CONT_END_DATE then t.CONT_END_DATE  else null end) as B3,
IFNULL(i.is_charge_water,'Y') as is_charge_water,
IFNULL(i.is_charge_electricity,'Y') as is_charge_electricity,
CONCAT_WS(',',i.is_charge_water,i.is_charge_electricity) as is_charge
from (
SELECT 
a.ID,
a.MALL_ID,
bm.MALL_CODE,
bm.MALL_NAME,
bm.BELONG_NAME,
a.COMPANY_ID,
icw.company_no,
a.COMPANY_NAME,
a.CONT_NO,
a.CONT_BEGIN_DATE,
a.CONT_END_DATE,
c.WATER_ACCOUNT_END,
c.ELECTRICITY_ACCOUNT_END,
a.pay_cycle,
c.E3,
c.E4,
(case when a.pay_cycle ='1' then DATE_SUB(CONCAT('${compareDate}','-01') ,INTERVAL 2 MONTH) 
when a.pay_cycle ='2' then MAKEDATE(EXTRACT(YEAR FROM DATE_SUB(CONCAT('${compareDate}','-01') ,INTERVAL 1 MONTH)),1) + interval QUARTER(DATE_SUB(CONCAT('${compareDate}','-01') ,INTERVAL 1 MONTH))*3-4 month
when a.pay_cycle ='3' then DATE_SUB(CONCAT('${compareDate}','-01') ,INTERVAL 4 MONTH) 
 else null end) as B2,
a.CONT_TYPE
FROM bs_cont a
left join bs_mall bm on a.MALL_ID = bm.ID 
left join ib_company_wl icw on a.COMPANY_ID = icw.id and icw.is_del = '0'
left join (
select b.cont_id, max(if(br.FEE_TYPE='507',br.ACCOUNT_END, null)) as WATER_ACCOUNT_END, 
max(if(br.FEE_TYPE='507',br.RECE_DATE, null)) as E3, 
max(if(br.FEE_TYPE='506',br.ACCOUNT_END, null)) as ELECTRICITY_ACCOUNT_END,
max(if(br.FEE_TYPE='506',br.RECE_DATE, null)) as E4
from 
bill b 
left join bill_rece br on br.BILL_ID=b.id and br.IS_DEL='0' 
where b.is_del='0' and b.status = '2' and br.FEE_TYPE in ('506','507')
group by b.cont_id
) c on c.cont_id = a.ID
where a.IS_DEL = '0' and a.CONT_TYPE in ('1','2') 
AND bm.MALL_CODE in(SELECT mall_code from ibp_platform.icp_user_mall where is_del = 0 and icp_user_id = ${userId})

${if(len(mallName) == 0,"","and bm.MALL_NAME in('"+mallName+"')")}
${if(len(companyName) == 0,"","and a.COMPANY_NAME like('%"+companyName+"%')")}

) t 
left join ib_company_hydropower_config i on t.MALL_CODE = i.mall_code and (t.company_no = i.company_code or i.company_code= 'ALL') and i.is_del = 0
GROUP BY t.ID
) x where 1=1
-- x.B3 is not null 
and x.is_charge <> 'N,N'
) y where 1=1 
 ) z group by z.MALL_CODE,z.COMPANY_ID
HAVING 1=1 and (z.E1 is null or z.E1<z.B4)
${if(len(contBeginDate) == 0,"","and CONT_END_DATE >= ('"+contBeginDate+"')")}
${if(len(contEndDate) == 0,"","and CONT_BEGIN_DATE <= ('"+contEndDate+"')")}

ps: ${xxx} 帆软报表写法，就是入参