WITH result_list AS (SELECT a.id,
a.user_id,
c.arch_no,
c.user_name,
c.user_address,
c.user_state,
c.main_arch_id,
c.user_type,
c.costtype as cost_type,
b.area_id,
a.tab_id,
b.tab_type,
a.this_month_read,
a.last_month_read,
a.agreement_water,
COALESCE(a.really_use, 0) really_use,
COALESCE(a.read_meter_month, null) read_meter_month,
a.read_meter_time,
a.read_meter_count,
a.tenant_id,
COALESCE(b.step_quota_water, 0) step_quota_water,
wm.year_excess_use as year_excess_use,
COALESCE(c.population_sum, 0) population_sum,
COALESCE(
(SELECT SUM(CASE WHEN f.acct_item_type_code = '0050001' THEN f.final_num ELSE 0 END) as basic_year_bill_use
FROM ueccp.rs_acct_item f
where f.tenant_id = 'T001'
and f.acct_item_type_code = '0050001'
and a.tab_id = f.tab_id
and f.read_meter_month >= 202501
and f.read_meter_month <= 202512
and e.water_properties_id = f.water_properties_id), 0) basic_year_bill_use,
COALESCE(
(SELECT SUM(CASE WHEN acct_item_type_code = '0060001' THEN final_num ELSE 0 END) sewage_year_bill_use
FROM ueccp.rs_acct_item f
where f.tenant_id = 'T001'
and a.tab_id = f.tab_id
and f.acct_item_type_code = '0060001'
and f.read_meter_month >= 202501
and f.read_meter_month <= 202512
and e.water_properties_id = f.water_properties_id), 0) sewage_year_bill_use,
COALESCE(
(SELECT SUM(CASE WHEN acct_item_type_code = '0050001' THEN final_num ELSE 0 END) basic_month_bill_use
FROM ueccp.rs_acct_item i
where tab_id IS NOT NULL
AND final_num > 0
AND tenant_id = 'T001'
and i.acct_item_type_code = '0050001'
AND a.tab_id = i.tab_id
and i.read_meter_month = 202509
and e.water_properties_id = i.water_properties_id), 0) basic_month_bill_use,
COALESCE(
(SELECT SUM(CASE WHEN acct_item_type_code = '0060001' THEN final_num ELSE 0 END) sewage_month_bill_use
FROM ueccp.rs_acct_item i
where tab_id IS NOT NULL
AND final_num > 0
AND tenant_id = 'T001'
AND a.tab_id = i.tab_id
and i.acct_item_type_code = '0060001'
and i.read_meter_month = 202509
and e.water_properties_id = i.water_properties_id), 0) sewage_month_bill_use,
g.pay_type,
h.node_code pay_type_node_code,
e.water_properties_id,
COALESCE(e.use_rate, 0) as use_rate,
COALESCE(polluters_rate, 0) as polluters_rate,
coalesce(e.mixed_method, 0) as mixed_method,
e.min_amount,
e.max_amount,
coalesce(e.seq, 1) as seq,
j.other_fee_price,
j.is_cycle,
j.is_collect,
j.calculate_month,
j.other_fee_mana_id,
k.trash_type,
k.billing_type,
k.is_collect_trash,
k.start_month,
k.delay_month,
k.true_start_month,
k.receive_state,
k.receive_money,
coalesce((select sum(amount)
from ueccp.rs_acct_item ai
where ai.tab_id = A.tab_id
and acct_item_type_code = '0030001'
and ai.read_meter_month = 202509), 0) as garbage_fee
FROM ueccp.rs_meter_read a
INNER JOIN ueccp.rs_tab_arch b on a.tab_id = b.id
INNER JOIN ueccp.rs_user_arch c on a.user_id = c.id
LEFT JOIN ueccp.rs_account g on c.id = g.user_id
LEFT JOIN ueccp.rs_dictionary h on g.pay_type = h.node
INNER JOIN ueccp.rs_feearea d on b.area_id = d.area_id
LEFT JOIN ueccp.rs_user_wa_proper e on c.id = e.user_id and b.id = e.tab_id
LEFT JOIN (select of.other_fee_price,
of.is_cycle,
of.is_collect,
of.calculate_month,
uof.user_id,
uof.other_fee_mana_id
from ueccp.rs_user_other_fee uof,
ueccp.rs_other_fee of
where uof.other_fee_mana_id = of.id
and of.tenant_id = 'T001') j on j.user_id = c.id
LEFT JOIN (select ut.user_id,
ut.trash_type,
ut.billing_type,
ut.is_collect_trash,
ut.start_month,
ut.delay_month,
ut.true_start_month,
ut.receive_state,
ut.receive_money
from ueccp.rs_user_trash ut,
ueccp.rs_water_garbage wg
where ut.trash_type = wg.id
and wg.tenant_id = 'T001') k on k.user_id = c.id
left join (SELECT coalesce(sum(wl.excess_use), 0) as year_excess_use,
wm.user_id,
wm.tab_id,
wm.data_year
FROM ueccp.rs_super_plan_water_mana wm
INNER JOIN ueccp.rs_super_plan_water_list wl
ON wm.ID = wl.super_plan_water_mana_id
INNER JOIN ueccp.rs_dictionary d
ON d.node = wm.date_state AND d.node_code = 'SYS_SUPER_PLAN_STATE_PASS'
group by wm.user_id, wm.tab_id, wm.data_year) as wm
on wm.user_id = a.user_id and wm.tab_id = a.tab_id and
wm.data_year = LEFT(a.read_meter_month, 4)
WHERE b.IS_CHECK_TAB = 0
AND a.really_use IS NOT NULL
AND a.read_meter_month IS NOT NULL
and e.water_properties_id is not null
AND a.tenant_id = 'T001'
AND a.read_meter_state =
(SELECT NODE FROM ueccp.rs_DICTIONARY WHERE NODE_CODE = 'SYS_READ_STATE_HASREAD')
AND (a.agreement_verify_state = 1 OR
((a.agreement_verify_state = 0 OR a.agreement_verify_state IS NULL) AND
(a.agreement_water IS NULL OR (a.agreement_water < to_number((SELECT param_final_value
FROM ueccp.rs_system_param
WHERE PARAM_CODE = 'NEED_VERIFY_AGREE_WATER_PLUS_VALUE'),
'9999999999999999999') AND
a.agreement_water > to_number((SELECT param_final_value
FROM ueccp.rs_system_param
WHERE PARAM_CODE = 'NEED_VERIFY_AGREE_WATER_NEGATIVE_VALUE'),
'9999999999999999999')))))
AND (a.check_state = 1 OR
(a.check_state = 0 AND (a.ESTIMATE_READ = 0 OR a.ESTIMATE_READ IS NULL) AND
a.IS_EXCEPTION IN (SELECT NODE
FROM ueccp.rs_DICTIONARY
WHERE NODE_CODE IN ('SYS_METERREAD_TABSTATE_26',
'SYS_METERREAD_EXCEPTION_NORMAL')) AND
a.READ_METER_SITUATION = (SELECT NODE
FROM ueccp.rs_DICTIONARY
WHERE NODE_CODE = 'SYS_METERREAD_TABSTATE_NORMAL') AND
a.meter_turn_type =
(SELECT NODE FROM ueccp.rs_DICTIONARY WHERE NODE_CODE = 'SYS_METERREAD_TURNTYPE_NORMAL')))
AND a.read_meter_month = REPLACE('2025-09', '-', '')
AND a.area_id IN
(1214009, 1214026, 1214027, 1214221, 1214222, 1214223, 1214224, 1214225, 1214226, 1214227,
1214028, 1214228, 1214229, 1214230, 1214231, 1214232, 1214233, 1214234, 1214235, 1214236,
1214237, 1214238, 1214239, 1214240, 1214241, 1214242, 1214243, 1214244, 1214245, 1214246,
1214247, 1214248, 1214249, 1214250, 1214251, 1214252, 1214253, 1214254, 1214255, 1214256,
1214257, 1214258, 1214259, 1214260, 1214261, 1214262, 1214263, 1214264, 1214265, 1214266,
1214267, 1214268, 1214269, 1214270, 1214271, 1214272, 1214273, 1214029, 1214274, 1214275,
1214276, 1214277, 1214030, 1214278, 1214011, 1214050, 1214051, 1214052, 1214053, 1214054,
1214055, 1214056, 1214057, 1214058, 1214059, 1214060, 1214061, 1214062, 1214063, 1214064,
1214065, 1214066, 1214067, 1214068, 1214069, 1214070, 1214071, 1214072, 1214073, 1214279,
1214280, 1214281, 1214031, 1214282, 1214283, 1214284, 1214285, 1214286, 1214287, 1214032,
1214288, 1214033, 1214289, 1214034, 1214290, 1214291, 1214013, 1214086, 1214087, 1214088,
1214089, 1214090, 1214091, 1214092, 1214093, 1214094, 1214095, 1214096, 1214097, 1214098,
1214099, 1214292, 1214293, 1214294, 1214015, 1214295, 1214296, 1214297, 1214298, 1214299,
1214300, 1214301, 1214302, 1214303, 1214304, 1214305, 1214306, 1214307, 1214308, 1214309,
1214310, 1214311, 1214035, 1214312, 1214364, 1214374, 1214384, 1215354, 1215364)
AND NOT EXISTS (select 1
from ueccp.rs_feearea rf
where rf.area_id = a.area_id
and (rf.parent_area_id in
(select DISTINCT regexp_split_to_table('1212690,1212673', ',')::int8) or
rf.area_id in
(select DISTINCT regexp_split_to_table('1212690,1212673', ',')::int8))))
SELECT ss.*, s.plan_use, s.excess_use, s.plan_list_id
FROM result_list ss
left join (select w.water_id
from ueccp.rs_water_management w
INNER JOIN ueccp.rs_water_management_item wi ON w.water_id = wi.water_properties_id
INNER JOIN ueccp.rs_water_step_name s ON wi.rule_id = s.ID
INNER JOIN ueccp.rs_dictionary d ON s.quota_step_type = d.node :: VARCHAR
WHERE d.node_code = 'SYS_LADDER_TYPE_QUOTA'
group by w.water_id) w on ss.water_properties_id = w.water_id
inner join (SELECT wm.user_id,
wm.tab_id,
wm.ID as plan_mana_id,
wm.data_year || lpad(wl.use_month, 2, '0') as read_meter_month,
wl.ID as plan_list_id,
coalesce(wl.excess_use, 0) as excess_use,
wl.plan_use
FROM ueccp.rs_super_plan_water_mana wm
INNER JOIN ueccp.rs_super_plan_water_list wl ON wm.ID = wl.super_plan_water_mana_id
INNER JOIN ueccp.rs_dictionary d
ON d.node = wm.date_state AND d.node_code = 'SYS_SUPER_PLAN_STATE_PASS') s
ON s.user_id = ss.user_id AND s.tab_id = ss.tab_id AND s.read_meter_month = ss.read_meter_month
where w.water_id is not null
union all
SELECT ss.*, null, null, null
FROM result_list ss
LEFT JOIN (select w.water_id
from ueccp.rs_water_management w
INNER JOIN ueccp.rs_water_management_item wi ON w.water_id = wi.water_properties_id
INNER JOIN ueccp.rs_water_step_name s ON wi.rule_id = s.ID
INNER JOIN ueccp.rs_dictionary d ON s.quota_step_type = d.node :: VARCHAR
WHERE d.node_code = 'SYS_LADDER_TYPE_QUOTA'
GROUP BY w.water_id) w on ss.water_properties_id = w.water_id
where w.water_id is null
ORDER BY water_properties_id, seq, read_meter_count
这段SQL 在pgsql上执行太慢了 如何优化
最新发布
在数据库操作中，当复制数据后发现表的自增Key没有同步最大自增值，可以使用SQL命令如`selectsetval(md_statistics_type_id_seq,(SELECTmax(statistics_id)frommd_statistics_type));`来设置新的起始值，确保自增序列与源数据一致。
扫一扫
1084
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
