WITH base_fee AS (
SELECT
f.id AS fee_id,
f.comm_id,
IFNULL(org.Name, ‘’) AS project_name,
f.customer_id,
f.resource_id,
f.corp_cost_id,
f.cost_id,
f.fee_date,
YEAR(f.fee_date) AS fee_year,
IFNULL(f.due_amount, 0) AS due_amount,
IFNULL(res.resource_code, ‘’) AS resource_code,
IFNULL(cus.name, ‘’) AS customer_name,
IFNULL(cost.cost_name, IFNULL(corp_cost.cost_name, ‘’)) AS cost_name,
IFNULL(u.Name, ‘无管家’) AS house_keeper_name
FROM tidb_sync_combine.tb_charge_fee f
LEFT JOIN erp_base.tb_base_masterdata_resource res
ON LOWER(f.resource_id) = LOWER(res.id)
LEFT JOIN erp_base.tb_base_masterdata_customer_comm cus
ON LOWER(f.customer_id) = LOWER(cus.id)
LEFT JOIN tidb_sync_combine.tb_charge_cost cost
ON LOWER(f.cost_id) = LOWER(cost.id)
LEFT JOIN erp_base.tb_base_charge_cost corp_cost
ON LOWER(f.corp_cost_id) = LOWER(corp_cost.id)
LEFT JOIN erp_base.rf_user u
ON LOWER(res.house_keeper) = LOWER(u.Id)
LEFT JOIN erp_base.tb_base_cheng_ji org
ON LOWER(f.comm_id) = LOWER(org.Id)
WHERE f.is_delete = 0
AND f.fee_date < ‘2025-01-01 00:00:00’
AND f.comm_id IN (‘58f7d868-ba17-40e7-86d5-fd564540c933’,‘f4829a28-e816-41a8-8252-bf8e243f8f0d’,‘7a4ccf5a-7cf4-447e-af35-8ccb3efcce91’,‘3c42f884-00e4-4152-92ba-f50d801c0dff’,‘0ff93f1a-4c02-4588-9204-de8dda5add72’,‘c291720d-f9ff-4ab4-9fb3-ab77095da8a3’,‘5ff7cb61-eab5-4db1-b5de-afb169c047a0’,‘32d658ea-3ff5-40ce-8347-32aa63d86687’,‘e0f7c1d7-2004-4bed-9fa4-b5b146b191fd’,‘9df4e31d-491e-4655-baeb-bc9d47ddc103’,‘e6387dff-6ef7-4249-ad6f-cd72a0592537’,‘b3cf7d47-d48e-47c1-91b3-25e4723eedb0’,‘b69732b7-235e-4a33-81e5-7131d9d0ae32’,‘1a32624a-7453-40e1-b6bf-8207d942ebfe’)
),
processed_before AS (
SELECT
d.fee_id,
SUM(
CASE
WHEN d.deal_type IN (‘实收’,‘代扣’,‘托收确认’,‘预存冲抵’,‘减免’) THEN ABS(IFNULL(d.deal_amount, 0))
WHEN d.deal_type IN (‘实收红冲’,‘预存冲抵红冲’,‘减免红冲’) THEN -ABS(IFNULL(d.deal_amount, 0))
ELSE 0
END
) AS before_processed_amount
FROM tidb_sync_combine.tb_charge_receipts_detail d
INNER JOIN base_fee bf
ON LOWER(d.fee_id) = LOWER(bf.fee_id)
WHERE d.is_delete = 0
AND d.deal_date < ‘2025-01-01 00:00:00’
AND d.deal_type IN (‘实收’,‘代扣’,‘托收确认’,‘预存冲抵’,‘减免’,‘实收红冲’,‘预存冲抵红冲’,‘减免红冲’)
GROUP BY d.fee_id
),
fee_red_before AS (
SELECT
h.id AS fee_id,
SUM(ABS(IFNULL(h.due_amount, 0))) AS red_amount
FROM tidb_sync_combine.tb_charge_fee_his h
INNER JOIN base_fee bf
ON LOWER(h.id) = LOWER(bf.fee_id)
WHERE h.is_delete = 0
AND h.deal_date < ‘2025-01-01 00:00:00’
AND (h.fee_deal_type IN (‘应收红冲’,‘费用红冲’) OR h.due_amount < 0)
GROUP BY h.id
),
period_detail AS (
SELECT
d.fee_id,
SUM(
CASE
WHEN d.deal_type IN (‘实收’,‘代扣’,‘托收确认’,‘预存冲抵’) THEN ABS(IFNULL(d.deal_amount, 0))
WHEN d.deal_type IN (‘实收红冲’,‘预存冲抵红冲’) THEN -ABS(IFNULL(d.deal_amount, 0))
ELSE 0
END
) AS recovery_amount,
SUM(
CASE
WHEN d.deal_type = ‘减免’ THEN ABS(IFNULL(d.deal_amount, 0))
WHEN d.deal_type = ‘减免红冲’ THEN -ABS(IFNULL(d.deal_amount, 0))
ELSE 0
END
) AS arrears_waiver_amount
FROM tidb_sync_combine.tb_charge_receipts_detail d
INNER JOIN base_fee bf
ON LOWER(d.fee_id) = LOWER(bf.fee_id)
WHERE d.is_delete = 0
AND d.deal_date >= ‘2025-01-01 00:00:00’
AND d.deal_date <= ‘2025-12-31 23:59:59’
AND d.deal_type IN (‘实收’,‘代扣’,‘托收确认’,‘预存冲抵’,‘减免’,‘实收红冲’,‘预存冲抵红冲’,‘减免红冲’)
GROUP BY d.fee_id
),
all_period_waiver AS (
SELECT
d.comm_id,
d.customer_id,
d.resource_id,
d.corp_cost_id,
SUM(
CASE
WHEN d.deal_type = ‘减免’ THEN ABS(IFNULL(d.deal_amount, 0))
WHEN d.deal_type = ‘减免红冲’ THEN -ABS(IFNULL(d.deal_amount, 0))
ELSE 0
END
) AS period_waiver_amount
FROM tidb_sync_combine.tb_charge_receipts_detail d
LEFT JOIN erp_base.tb_base_masterdata_resource res2
ON LOWER(d.resource_id) = LOWER(res2.id)
LEFT JOIN erp_base.tb_base_masterdata_customer_comm cus2
ON LOWER(d.customer_id) = LOWER(cus2.id)
LEFT JOIN erp_base.rf_user u2
ON LOWER(res2.house_keeper) = LOWER(u2.Id)
WHERE d.is_delete = 0
AND d.deal_date >= ‘2025-01-01 00:00:00’
AND d.deal_date <= ‘2025-12-31 23:59:59’
AND d.deal_type IN (‘减免’,‘减免红冲’)
AND d.comm_id IN (‘58f7d868-ba17-40e7-86d5-fd564540c933’,‘f4829a28-e816-41a8-8252-bf8e243f8f0d’,‘7a4ccf5a-7cf4-447e-af35-8ccb3efcce91’,‘3c42f884-00e4-4152-92ba-f50d801c0dff’,‘0ff93f1a-4c02-4588-9204-de8dda5add72’,‘c291720d-f9ff-4ab4-9fb3-ab77095da8a3’,‘5ff7cb61-eab5-4db1-b5de-afb169c047a0’,‘32d658ea-3ff5-40ce-8347-32aa63d86687’,‘e0f7c1d7-2004-4bed-9fa4-b5b146b191fd’,‘9df4e31d-491e-4655-baeb-bc9d47ddc103’,‘e6387dff-6ef7-4249-ad6f-cd72a0592537’,‘b3cf7d47-d48e-47c1-91b3-25e4723eedb0’,‘b69732b7-235e-4a33-81e5-7131d9d0ae32’,‘1a32624a-7453-40e1-b6bf-8207d942ebfe’)
GROUP BY d.comm_id, d.customer_id, d.resource_id, d.corp_cost_id
),
fee_stage AS (
SELECT
bf.comm_id,
bf.project_name,
bf.customer_id,
bf.resource_id,
bf.corp_cost_id,
bf.resource_code,
bf.customer_name,
bf.cost_name,
bf.house_keeper_name,
CASE
WHEN bf.fee_year <= 2020 THEN ‘age5’
WHEN bf.fee_year = 2021 THEN ‘age4’
WHEN bf.fee_year = 2022 THEN ‘age3’
WHEN bf.fee_year = 2023 THEN ‘age2’
WHEN bf.fee_year = 2024 THEN ‘age1’
ELSE ‘other’
END AS age_bucket,
GREATEST(
IFNULL(bf.due_amount, 0)
- IFNULL(pb.before_processed_amount, 0)
- IFNULL(fr.red_amount, 0),
0
) AS initial_arrears_amount,
IFNULL(pd.recovery_amount, 0) AS recovery_amount,
IFNULL(pd.arrears_waiver_amount, 0) AS arrears_waiver_amount
FROM base_fee bf
LEFT JOIN processed_before pb
ON LOWER(bf.fee_id) = LOWER(pb.fee_id)
LEFT JOIN fee_red_before fr
ON LOWER(bf.fee_id) = LOWER(fr.fee_id)
LEFT JOIN period_detail pd
ON LOWER(bf.fee_id) = LOWER(pd.fee_id)
),
agg AS (
SELECT
comm_id,
project_name,
customer_id,
resource_id,
corp_cost_id,
house_keeper_name,
resource_code,
customer_name,
cost_name,
SUM(CASE WHEN age_bucket = ‘age5’ THEN initial_arrears_amount ELSE 0 END) AS init_age5,
SUM(CASE WHEN age_bucket = ‘age4’ THEN initial_arrears_amount ELSE 0 END) AS init_age4,
SUM(CASE WHEN age_bucket = ‘age3’ THEN initial_arrears_amount ELSE 0 END) AS init_age3,
SUM(CASE WHEN age_bucket = ‘age2’ THEN initial_arrears_amount ELSE 0 END) AS init_age2,
SUM(CASE WHEN age_bucket = ‘age1’ THEN initial_arrears_amount ELSE 0 END) AS init_age1,
SUM(CASE WHEN age_bucket = ‘age5’ THEN recovery_amount ELSE 0 END) AS recover_age5,
SUM(CASE WHEN age_bucket = ‘age4’ THEN recovery_amount ELSE 0 END) AS recover_age4,
SUM(CASE WHEN age_bucket = ‘age3’ THEN recovery_amount ELSE 0 END) AS recover_age3,
SUM(CASE WHEN age_bucket = ‘age2’ THEN recovery_amount ELSE 0 END) AS recover_age2,
SUM(CASE WHEN age_bucket = ‘age1’ THEN recovery_amount ELSE 0 END) AS recover_age1,
SUM(CASE WHEN age_bucket = ‘age5’ THEN arrears_waiver_amount ELSE 0 END) AS waiver_age5,
SUM(CASE WHEN age_bucket = ‘age4’ THEN arrears_waiver_amount ELSE 0 END) AS waiver_age4,
SUM(CASE WHEN age_bucket = ‘age3’ THEN arrears_waiver_amount ELSE 0 END) AS waiver_age3,
SUM(CASE WHEN age_bucket = ‘age2’ THEN arrears_waiver_amount ELSE 0 END) AS waiver_age2,
SUM(CASE WHEN age_bucket = ‘age1’ THEN arrears_waiver_amount ELSE 0 END) AS waiver_age1,
SUM(arrears_waiver_amount) AS arrears_waiver_amount
FROM fee_stage
WHERE initial_arrears_amount <> 0
OR recovery_amount <> 0
OR arrears_waiver_amount <> 0
GROUP BY
comm_id,
project_name,
customer_id,
resource_id,
corp_cost_id,
house_keeper_name,
resource_code,
customer_name,
cost_name
)
SELECT /*+ SET_VAR(exec_mem_limit = 42949672960, query_timeout = 1800) */
a.project_name AS project_name,
‘2025-01-01 至 2025-12-31’ AS period_text,
a.house_keeper_name AS 管家名称,
a.resource_code AS 资源编号,
a.customer_name AS 客户名称,
a.cost_name AS 收费科目,
ROUND(a.init_age5, 2) AS 欠费5年及以上金额,
ROUND(a.init_age4, 2) AS 欠费4年金额,
ROUND(a.init_age3, 2) AS 欠费3年金额,
ROUND(a.init_age2, 2) AS 欠费2年金额,
ROUND(a.init_age1, 2) AS 欠费1年金额,
ROUND(a.init_age5 + a.init_age4 + a.init_age3 + a.init_age2 + a.init_age1, 2) AS 欠费金额合计,
ROUND(a.recover_age5, 2) AS 欠费5年及以上欠款清回金额,
ROUND(a.recover_age4, 2) AS 欠费4年欠款清回金额,
ROUND(a.recover_age3, 2) AS 欠费3年欠款清回金额,
ROUND(a.recover_age2, 2) AS 欠费2年欠款清回金额,
ROUND(a.recover_age1, 2) AS 欠费1年欠款清回金额,
ROUND(a.arrears_waiver_amount, 2) AS 本期清欠减免金额,
ROUND(IFNULL(w.period_waiver_amount, 0), 2) AS 本期减免金额,
ROUND(
a.recover_age5 + a.recover_age4 + a.recover_age3 + a.recover_age2 + a.recover_age1
+ a.arrears_waiver_amount,
2
) AS 清欠合计,
ROUND(GREATEST(a.init_age5 - a.recover_age5 - a.waiver_age5, 0), 2) AS 欠费5年及以上剩余未清回金额,
ROUND(GREATEST(a.init_age4 - a.recover_age4 - a.waiver_age4, 0), 2) AS 欠费4年剩余未清回金额,
ROUND(GREATEST(a.init_age3 - a.recover_age3 - a.waiver_age3, 0), 2) AS 欠费3年剩余未清回金额,
ROUND(GREATEST(a.init_age2 - a.recover_age2 - a.waiver_age2, 0), 2) AS 欠费2年剩余未清回金额,
ROUND(GREATEST(a.init_age1 - a.recover_age1 - a.waiver_age1, 0), 2) AS 欠费1年剩余未清回金额,
ROUND(
GREATEST(a.init_age5 - a.recover_age5 - a.waiver_age5, 0)
+ GREATEST(a.init_age4 - a.recover_age4 - a.waiver_age4, 0)
+ GREATEST(a.init_age3 - a.recover_age3 - a.waiver_age3, 0)
+ GREATEST(a.init_age2 - a.recover_age2 - a.waiver_age2, 0)
+ GREATEST(a.init_age1 - a.recover_age1 - a.waiver_age1, 0),
2
) AS 截止期末剩余未清回金额合计
FROM agg a
LEFT JOIN all_period_waiver w
ON LOWER(a.comm_id) = LOWER(w.comm_id)
AND LOWER(a.customer_id) = LOWER(w.customer_id)
AND LOWER(a.resource_id) = LOWER(w.resource_id)
AND LOWER(a.corp_cost_id) = LOWER(w.corp_cost_id)
ORDER BY
a.project_name,
a.house_keeper_name,
a.resource_code,
a.customer_name,
a.cost_name
;
当这个comm_id in()过多时，就会报：ERROR 1064: Unknown error