mysql - 在mysql中添加内部查询列值

Question

我有很多表，我从中获取数据，我获取的数据是正确的，但仅限于收集的金额。

但现在我想明智地获得总剩余金额和总未偿金额。

1. 明确未清总金额在恢复表中
2. 收集的金额在投诉行动表中。

我的要求是，我必须在不同天的板块中显示个人收集的金额，并在区域明智地显示收集的总金额、剩余的总金额和未偿还的总金额：

这是我想如何显示结果的图像：

查询获取波纹管数据

select sum(y.salab1) salab1,sum(y.salab2) salab2,sum(y.salab3) 
salab3,sum(y.salab4) salab4,sum(y.salab5) salab5,sum(y.salab6) 
salab6,y.regionName,y.zonName from (
select
(CASE
 WHEN (CEIL((DATEDIFF(CURRENT_TIMESTAMP(), rcv.`AGING_DATE`) /15)) = 1 ) THEN  
sum( IFNULL(cpa.`COLLECTED_AMOUNT`, 0) )
ELSE 0
  END) as salab1,
 (CASE
  WHEN (CEIL((DATEDIFF(CURRENT_TIMESTAMP(), rcv.`AGING_DATE`) /15)) = 2 ) 
THEN  sum(IFNULL(cpa.`COLLECTED_AMOUNT`, 0) )
  ELSE 0 
 END) as salab2,
 (CASE
 WHEN (CEIL((DATEDIFF(CURRENT_TIMESTAMP(), rcv.`AGING_DATE`) /15)) = 3 ) THEN  
sum(IFNULL(cpa.`COLLECTED_AMOUNT`, 0) )
  ELSE 0 
 END) as salab3,
 (CASE
  WHEN (CEIL((DATEDIFF(CURRENT_TIMESTAMP(), rcv.`AGING_DATE`) /15)) = 4 ) 
 THEN  sum(IFNULL(cpa.`COLLECTED_AMOUNT`, 0) )
  ELSE 0
 END) as salab4,
 (CASE
 WHEN (CEIL((DATEDIFF(CURRENT_TIMESTAMP(), rcv.`AGING_DATE`) /15)) = 5 ) THEN  
sum(IFNULL(cpa.`COLLECTED_AMOUNT`, 0) )
  ELSE 0
END) as salab5,
  (CASE
  WHEN (CEIL((DATEDIFF(CURRENT_TIMESTAMP(), rcv.`AGING_DATE`) /15)) >5  ) 
THEN  sum( IFNULL(cpa.`COLLECTED_AMOUNT`, 0) )
  ELSE 0
 END) as salab6,

CEIL((DATEDIFF(CURRENT_TIMESTAMP(), rcv.`AGING_DATE`) /15)) as slab
,   rgn.`NAME` as regionName, zn.`NAME` as zonName,rcv.AMOUNT
 from `recovery` rcv
 join complain cmp on cmp.`CODE` = rcv.`COMPLAINT_ID`
 join customerprofile cp on cp.`REG_CODE` = cmp.`CUST_REG_NO`
 join marketofficer mo on mo.`MO_ID` = rcv.`RO_ID`
 LEFT OUTER join complain_actions cpa on cpa.`COMPLAIN_CODE` = cmp.`CODE`
 join market mrkt on mrkt.`ID` = cp.`MARKET_ID`
 join area ar on ar.`ID` = mrkt.`AREA_ID`
 join city cty on cty.`ID` = ar.`CITY_ID`
 join region rgn on rgn.`ID` = cty.`REGION_ID`
 join `zone` zn on zn.`ID` = rgn.`ZONE_ID`
 group by zonName,regionName,slab) y group by y.zonName,y.regionName

此查询正在获取收集的数量区域和区域，但我想要所有

1. 以天为单位收取的金额
2. 总收款
3. 总剩余金额
4. 未偿还总额

谢谢。

标签： mysqlsql