摘要:
最近在处理stonedb的一个包含内连接包含内连接外连接派生表in子查询和聚合的查询出错的问题, 逻辑非常复杂, 包含的操作符非常多.
本文首先从顶层设计出发, 指出如何分析如此复杂的问题。
查询SQL:
SELECT
B.company_id,
'上划日' ud_type,
2 sort_no,
'合计' fiscal_date,
DATE_FORMAT('2023-06-06', '%Y-%m-%d') fiscal_date,
C.pl_amount
FROM
C1FG_POOL A
INNER JOIN C1FG_PL_NODE B ON
A.row_id = B.pool_id
LEFT JOIN (
SELECT c.node_id,
SUM(d.dr_amount) pl_amount
FROM
C1FG_POOL a,
C1FG_PL_NODE b,
C1FG_PL_ACCOUNT c,
C1FG_PL_SUBSIDIARY d
WHERE
a.row_id = b.pool_id
AND b.row_id = c.node_id
AND c.row_id = d.account_id
AND b.company_id IN (
SELECT t1.row_id
FROM
C1MD_COMPANY t1,
C1