SELECT
SUM(
CASE
WHEN yb.auart IN (‘ZRE’, ‘ZCR’, ‘ZRV’) THEN (0 - yb.netwr_cny)
ELSE yb.netwr_cny
END - IFNULL(bp1.ncny, 0) + IFNULL(bp1.cny, 0)
) AS NETWR_CNY
FROM
(
SELECT
date_format(bp.audat, ‘%Y-%m’) AS ym,
bp.auart,
bp.netwr_cny,
bp.id
FROM
bi_sales_place bp
WHERE
bp.kunnr NOT IN (‘102044’, ‘2100’)
and date_format(bp.audat, ‘%Y’) =‘2023’
AND bp.vtweg <> ‘90’
AND LENGTH(bp.kunnr) <> 4
AND NOT EXISTS (
SELECT 1
FROM bi_sales_place
WHERE vkorg = ‘1000’ AND kunnr = ‘100172’ AND id = bp.id
)
UNION ALL
SELECT
date_format(bp.audat, ‘%Y-%m’) AS ym,
NULL AS auart,
bp.netwr_cny,
bp.id
FROM
bi_sales_place_supplement bp
WHERE
bp.kunnr NOT IN (‘102044’, ‘2100’)
and date_format(bp.audat, ‘%Y’) =‘2023’
AND bp.vtweg <> ‘90’
AND LENGTH(bp.kunnr) <> 4
AND NOT EXISTS (
SELECT 1
FROM bi_sales_place_supplement
WHERE vkorg = ‘1000’ AND kunnr = ‘100172’ AND id = bp.id
)
) yb
LEFT JOIN (
SELECT
id,
CASE WHEN auart IN (‘ZRE’, ‘ZCR’, ‘ZRV’) THEN (0 - netwr_cny) ELSE netwr_cny END AS ncny,
CASE WHEN auart IN (‘ZRE’, ‘ZCR’, ‘ZRV’) THEN (0 - netwr1_cny) ELSE netwr1_cny END AS cny,
date_format(audat, ‘%Y-%m’) AS ym
FROM
bi_sales_place
WHERE
date_format(audat, ‘%Y%m’) = date_format(redat, ‘%Y%m’)
) bp1 ON yb.id = bp1.id AND yb.ym = bp1.ym
执行计划
===================================================================
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
|0 |SCALAR GROUP BY | |1 |66496 |
|1 |└─NESTED-LOOP OUTER JOIN | |59 |66495 |
|2 | ├─SUBPLAN SCAN |yb |59 |65281 |
|3 | │ └─UNION ALL | |59 |65281 |
|4 | │ ├─TABLE FULL SCAN |bp |50 |65071 |
|5 | │ └─TABLE FULL SCAN |bp |9 |210 |
|6 | └─DISTRIBUTED TABLE GET|bi_sales_place|1 |21 |
Outputs & filters:
0 - output([T_FUN_SUM(CASE WHEN yb.auart IN (‘ZRE’, ‘ZCR’, ‘ZRV’) THEN cast(0, DECIMAL(1, 0)) - yb.netwr_cny ELSE yb.netwr_cny END - IFNULL(CASE WHEN
bi_sales_place.id IS NOT NULL THEN CASE WHEN bi_sales_place.auart IN (‘ZRE’, ‘ZCR’, ‘ZRV’) THEN cast(0, DECIMAL(1, 0)) - bi_sales_place.netwr_cny ELSE bi_sales_place.netwr_cny
END ELSE cast(NULL, DECIMAL(14, 2)) END, cast(0, DECIMAL(14, 2))) + IFNULL(CASE WHEN bi_sales_place.id IS NOT NULL THEN CASE WHEN bi_sales_place.auart IN
(‘ZRE’, ‘ZCR’, ‘ZRV’) THEN cast(0, DECIMAL(1, 0)) - bi_sales_place.netwr1_cny ELSE bi_sales_place.netwr1_cny END ELSE cast(NULL, DECIMAL(14, 2)) END, cast(0,
DECIMAL(14, 2))))]), filter(nil), rowset=256
group(nil), agg_func([T_FUN_SUM(CASE WHEN yb.auart IN (‘ZRE’, ‘ZCR’, ‘ZRV’) THEN cast(0, DECIMAL(1, 0)) - yb.netwr_cny ELSE yb.netwr_cny END - IFNULL(CASE
WHEN bi_sales_place.id IS NOT NULL THEN CASE WHEN bi_sales_place.auart IN (‘ZRE’, ‘ZCR’, ‘ZRV’) THEN cast(0, DECIMAL(1, 0)) - bi_sales_place.netwr_cny ELSE
bi_sales_place.netwr_cny END ELSE cast(NULL, DECIMAL(14, 2)) END, cast(0, DECIMAL(14, 2))) + IFNULL(CASE WHEN bi_sales_place.id IS NOT NULL THEN CASE WHEN
bi_sales_place.auart IN (‘ZRE’, ‘ZCR’, ‘ZRV’) THEN cast(0, DECIMAL(1, 0)) - bi_sales_place.netwr1_cny ELSE bi_sales_place.netwr1_cny END ELSE cast(NULL,
DECIMAL(14, 2)) END, cast(0, DECIMAL(14, 2))))])
1 - output([yb.auart], [yb.netwr_cny], [bi_sales_place.id], [bi_sales_place.auart], [bi_sales_place.netwr_cny], [bi_sales_place.netwr1_cny]), filter(nil), rowset=256
conds(nil), nl_params_([yb.id(:0)], [yb.ym(:1)]), use_batch=false
2 - output([yb.id], [yb.ym], [yb.auart], [yb.netwr_cny]), filter(nil), rowset=256
access([yb.id], [yb.ym], [yb.auart], [yb.netwr_cny])
3 - output([UNION([1])], [UNION([2])], [UNION([3])], [UNION([4])]), filter(nil), rowset=256
4 - output([date_format(bp.audat, ‘%Y-%m’)], [bp.auart], [bp.netwr_cny], [bp.id]), filter([date_format(bp.audat, ‘%Y’) = ‘2023’], [LENGTH(bp.kunnr) !=
4], [(T_OP_NOT_IN, bp.kunnr, (‘102044’, ‘2100’))], [bp.vtweg != ‘90’], [lnnvl(cast(bp.vkorg = ‘1000’, TINYINT(-1, 0))) OR lnnvl(cast(bp.kunnr = ‘100172’,
TINYINT(-1, 0)))]), rowset=256
access([bp.id], [bp.kunnr], [bp.audat], [bp.vtweg], [bp.auart], [bp.netwr_cny], [bp.vkorg]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false,false,false,false,false],
range_key([bp.id]), range(MIN ; MAX)always true
5 - output([date_format(bp.audat, ‘%Y-%m’)], [cast(NULL, VARCHAR(4))], [bp.netwr_cny], [bp.id]), filter([date_format(bp.audat, ‘%Y’) = ‘2023’], [LENGTH(bp.kunnr)
!= 4], [(T_OP_NOT_IN, bp.kunnr, (‘102044’, ‘2100’))], [bp.vtweg != ‘90’], [lnnvl(cast(bp.vkorg = ‘1000’, TINYINT(-1, 0))) OR lnnvl(cast(bp.kunnr = ‘100172’,
TINYINT(-1, 0)))]), rowset=256
access([bp.id], [bp.kunnr], [bp.audat], [bp.vtweg], [bp.netwr_cny], [bp.vkorg]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false,false,false,false,false],
range_key([bp.id]), range(MIN ; MAX)always true
6 - output([bi_sales_place.id], [bi_sales_place.auart], [bi_sales_place.netwr_cny], [bi_sales_place.netwr1_cny]), filter([date_format(bi_sales_place.audat,
‘%Y%m’) = date_format(bi_sales_place.redat, ‘%Y%m’)], [:1 = CASE WHEN bi_sales_place.id IS NOT NULL THEN date_format(bi_sales_place.audat, ‘%Y-%m’) ELSE
cast(NULL, VARCHAR(26)) END]), rowset=256
access([bi_sales_place.id], [bi_sales_place.audat], [bi_sales_place.redat], [bi_sales_place.auart], [bi_sales_place.netwr_cny], [bi_sales_place.netwr1_cny]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false,false],
range_key([bi_sales_place.id]), range(MIN ; MAX),
range_cond([:0 = bi_sales_place.id])