WITH ACT
AS (SELECT
DATE_PART('year', AGE(date_trunc('month', CURRENT_DATE), TO_DATE(C.READ_METER_MONTH, 'YYYY-MM-DD'))) *
12 + DATE_PART('month', AGE(date_trunc('month', CURRENT_DATE),
TO_DATE(C.READ_METER_MONTH, 'YYYY-MM-DD'))) DEBTS_MONTHS,
A.ARCH_NO,
A.ID USER_ID,
C.AREA_ID,
C.ID meter_read_id,
D.PAY_TYPE,
B.DEBTS_AMOUNT,
C.READ_METER_MONTH,
C.LAST_MONTH_READ,
C.THIS_MONTH_READ,
C.BOOK_IN_NO,
T.ID TAB_ID,
T.TAB_TYPE,
C.entry_time,
CASE WHEN B.ACCT_ITEM_TYPE_CODE = '0010001' THEN B.DEBTS_NUM ELSE 0 END BASIC_SUM,
CASE WHEN B.ACCT_ITEM_TYPE_CODE = '0020001' THEN B.DEBTS_NUM ELSE 0 END SEW_WA_SUM,
CASE WHEN B.ACCT_ITEM_TYPE_CODE = '0010001' THEN B.DEBTS_AMOUNT ELSE 0 END BASIC_FEE,
CASE WHEN B.ACCT_ITEM_TYPE_CODE = '0020001' THEN B.DEBTS_AMOUNT ELSE 0 END SEW_WA_FEE
FROM ueccp.rs_meter_read c
INNER JOIN ueccp.rs_USER_ARCH A
ON c.arch_no = a.arch_no AND A.USER_STATE <> 8 AND A.tenant_id = 'T001'
INNER JOIN ueccp.rs_TAB_ARCH T ON c.user_id = t.user_id
LEFT JOIN ueccp.rs_ACCT_ITEM b
ON B.meter_read_id = C.ID AND B.STATUS NOT IN (4, 5, 7) AND B.DEBTS_AMOUNT > 0 AND
B.is_offset != 1
LEFT JOIN ueccp.rs_ACCOUNT D ON c.user_id = D.USER_ID
WHERE 1 = 1
AND t.area_id IN
(-1, 1214008, 1214012, 1214025, 1214186, 1214187, 1214188, 1214189, 1214190, 1214191, 1214192,
1214193, 1214194, 1214195, 1214196, 1214197, 1214198, 1214199, 1214200, 1214201, 1214202, 1214203,
1214204, 1214205, 1214206, 1214207, 1214208, 1214209, 1214210, 1214211, 1214212, 1214213, 1214214,
1214215, 1214216, 1214217, 1214218, 1214219, 1214220, 1214074, 1214075, 1214076, 1214077, 1214078,
1214079, 1214080, 1214081, 1214082, 1214083, 1214084, 1214085, 1214013, 1214086, 1214087, 1214088,
1214089, 1214090, 1214091, 1214092, 1214093, 1214094, 1214095, 1214096, 1214097, 1214098, 1214099,
1214014, 1214100, 1214101, 1214102, 1214103, 1214104, 1214105, 1214106, 1214107, 1214108, 1214109,
1214110, 1214015, 1214016, 1214111, 1214112, 1214113, 1214114, 1214115, 1214116, 1214117, 1214017,
1214118, 1214119, 1214120, 1214121, 1214122, 1214123, 1214124, 1214125, 1214126, 1214127, 1214128,
1214129, 1214130, 1214131, 1214132, 1214133, 1214134, 1214135, 1214136, 1214137, 1214138, 1214139,
1214140, 1214141, 1214142, 1214143, 1214144, 1214018, 1214145, 1214146, 1214147, 1214148, 1214149,
1214150, 1214151, 1214152, 1214153, 1214154, 1214155, 1214156, 1214019, 1214020, 1214157, 1214158,
1214159, 1214160, 1214161, 1214162, 1214163, 1214164, 1214165, 1214166, 1214167, 1214168, 1214169,
1214170, 1214171, 1214172, 1214173, 1214174, 1214175, 1214176, 1214177, 1214021, 1214022, 1214023,
1214178, 1214024, 1214179, 1214180, 1214181, 1214182, 1214183, 1214184, 1214185, 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, 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, 1214278, 1214279,
1214280, 1214281, 1214031, 1214282, 1214283, 1214284, 1214285, 1214286, 1214287, 1214032, 1214288,
1214033, 1214289, 1214034, 1214290, 1214291, 1214292, 1214293, 1214294, 1214295, 1214296, 1214297,
1214298, 1214299, 1214300, 1214301, 1214302, 1214303, 1214304, 1214305, 1214306, 1214307, 1214308,
1214309, 1214310, 1214311, 1214035, 1214312, 1214010, 1214036, 1214313, 1214314, 1214315, 1214316,
1214317, 1214318, 1214319, 1214320, 1214321, 1214322, 1214323, 1214324, 1214325, 1214326, 1214037,
1214327, 1214328, 1214329, 1214330, 1214331, 1214332, 1214038, 1214333, 1214334, 1214335, 1214039,
1214336, 1214337, 1214338, 1214339, 1214040, 1214340, 1214341, 1214342, 1214343, 1214344, 1214041,
1214042, 1214043, 1214345, 1214044, 1214346, 1214347, 1214045, 1214348, 1214046, 1214349, 1214047,
1214048, 1214350, 1214351, 1214352, 1214049, 1214353, 1214354, 1214355, 1214356, 1214357, 1214006,
1214007, 1214364, 1214374, 1214384, 1214394, 1214404, 1214395, 1214414, 1214415, 1214396, 1214405,
1214416)
AND C.READ_METER_STATE = 174),
result AS (SELECT F.NAME AREA_NAME,
B.BOOK_IN_NO,
A.MAIN_ARCH_ID MAIN_ARCH_NO,
A.ID USER_ID,
A.ARCH_NO,
A.USER_NAME,
B.RECEIVABLE_FEE,
B.DEBTS_NUM,
B.MIN_READ_METER_MONTH,
B.MAX_READ_METER_MONTH,
B.DEBTS_MONTHS,
A.USER_ADDRESS,
A.TOUCH_PHONE,
A.CONTACT_PHONE,
(SELECT NAME FROM ueccp.rs_DICTIONARY DA WHERE DA.NODE = A.USER_TYPE) userTypeStr,
B.PAY_TYPE,
B.LAST_MONTH_READ,
B.BASIC_SUM,
B.SEW_WA_SUM,
B.BASIC_FEE,
B.SEW_WA_FEE,
C.START_READ_METER_MONTH,
C.END_READ_METER_MONTH,
C.TOTAL_FEE,
ROUND(C.TOTAL_NUM, 0) TOTAL_NUM,
coalesce(t1.THIS_MONTH_READ, t1.LAST_MONTH_READ) THIS_MONTH_READ,
to_char(e.entry_time, 'yyyy-mm-dd hh24:mi:ss') entry_time,
COALESCE(tt.debts_amount, 0) debtsAmount,
(C.total_fee - COALESCE(tt.debts_amount, 0)) oldReceIvableFee,
(SELECT bb.meter_staff_phone
FROM ueccp.rs_area_meter_rela bb
WHERE bb.area_id = f.area_id) meter_staff_phone,
(SELECT dd.name FROM ueccp.rs_dictionary dd WHERE dd.node = d.pay_type) payTypeStr,
D.balance
FROM ueccp.rs_USER_ARCH A,
(SELECT T.ARCH_NO,
MAX(T.USER_ID) USER_ID,
MAX(T.AREA_ID) AREA_ID,
SUM(T.DEBTS_AMOUNT) RECEIVABLE_FEE,
COUNT(DISTINCT meter_read_id) DEBTS_NUM,
MAX(DEBTS_MONTHS) DEBTS_MONTHS,
MIN(T.LAST_MONTH_READ) LAST_MONTH_READ,
MAX(T.THIS_MONTH_READ) THIS_MONTH_READ,
MIN(T.READ_METER_MONTH) MIN_READ_METER_MONTH,
MAX(T.READ_METER_MONTH) MAX_READ_METER_MONTH,
MAX(T.PAY_TYPE) PAY_TYPE,
MAX(T.BOOK_IN_NO) BOOK_IN_NO,
SUM(T.BASIC_SUM) BASIC_SUM,
SUM(T.SEW_WA_SUM) SEW_WA_SUM,
SUM(T.BASIC_FEE) BASIC_FEE,
SUM(T.SEW_WA_FEE) SEW_WA_FEE,
MAX(T.entry_time) entry_time
FROM ACT T
WHERE 1 = 1
AND T.read_meter_month >= '2025-08'
AND T.read_meter_month <= '2025-08'
GROUP BY T.ARCH_NO
HAVING 1 = 1) AS B
LEFT JOIN (SELECT MAX(T.entry_time) entry_time, t.arch_no FROM act t GROUP BY t.arch_no) AS e
ON e.arch_no = b.arch_no
LEFT JOIN (SELECT MAX(r.THIS_MONTH_READ) THIS_MONTH_READ,
MAX(r.LAST_MONTH_READ) LAST_MONTH_READ,
arch_no,
MAX(entry_time) entry_time
FROM act r
GROUP BY arch_no) AS t1 ON t1.entry_time = e.entry_time AND t1.arch_no = e.arch_no,
ueccp.rs_FEEAREA F,
(SELECT MIN(C.READ_METER_MONTH) START_READ_METER_MONTH,
MAX(C.READ_METER_MONTH) END_READ_METER_MONTH,
SUM(C.DEBTS_AMOUNT) TOTAL_FEE,
SUM(C.BASIC_SUM) TOTAL_NUM,
C.ARCH_NO
FROM ACT C
GROUP BY C.ARCH_NO) AS C
LEFT JOIN (SELECT SUM(tt.debts_amount) AS debts_amount, tt.arch_no
FROM act tt
WHERE tt.read_meter_month = (SELECT TO_CHAR(now(), 'yyyy-mm'))
GROUP BY arch_no) tt ON tt.arch_no = C.arch_no,
ueccp.rs_account D
WHERE A.ARCH_NO = B.ARCH_NO
AND A.ARCH_NO = C.ARCH_NO
AND B.AREA_ID = F.AREA_ID
AND a.tenant_id = 'T001'
AND B.RECEIVABLE_FEE > 0
AND A.ID = D.USER_ID
ORDER BY F.AREA_ID, B.BOOK_IN_NO, a.arch_no)
SELECT COUNT(*) AS total
FROM result 语句查询太慢 如何优化
本文总结了半年内优化使用Arch4项目的性能改进措施，包括解决等待提示框导致的IE内存泄露问题，通过修改代码避免全局变量的不当使用；缓存管理的规划不足问题，提出了合理利用缓存的最佳实践；分析并优化了几个关键系统配置，如Linux下WebLogic9启动缓慢的问题，通过设置JAVA_OPTIONS参数加速启动过程；还揭示了一个监听器的Bug，该Bug可能影响操作效率。
扫一扫
1461
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
