完整sql如下
with huan as (SELECT fnp.business_month,
fnp.station_code,
fnp.goods_id,
fnp.sale_amount_tax,
fnp.stock_in_out_amount_tax
FROM falcon.fact_noil_psi as fnp
WHERE fnp.business_month = date_format(DATE_SUB(DATE(CONCAT(‘202203’, ‘01’)), INTERVAL 1 MONTH), ‘%Y%m’)),
tong as (SELECT fnp.business_month,
fnp.station_code,
fnp.goods_id,
fnp.sale_amount_tax,
fnp.stock_in_out_amount_tax
FROM falcon.fact_noil_psi as fnp
WHERE fnp.business_month = date_format(DATE_SUB(DATE(CONCAT(‘202203’, ‘01’)), INTERVAL 1 MONTH), ‘%Y%m’))
select ds.prov_code,
ds.prov_name,
ds.area_code,
ds.area_name,
fnp.station_code,
ds.station_name,
g2.GOODS_CATE_PID as goods_one_id,
g2.GOODS_CATE_PID as goods_one_name,
g2.GOODS_CATE_ID as goods_two_id,
g2.GOODS_CATE_NAME as goods_two_name,
gd.GOODS_CATE_ID as goods_three_id,
g1.GOODS_CATE_NAME as goods_three_name,
sum(fnp.sale_amount_tax) / 10000 as sale_amount_notax_ben,
sum(tong.sale_amount_tax) / 10000 as sale_amount_notax_year,
0 tong_saleroom_increase,
sum(huan.sale_amount_tax) / 10000 as sale_amount_notax_month,
0 huan_saleroom_increase,
(IFNULL(sum(fnp.sale_amount_tax), 0) - IFNULL(sum(fnp.stock_in_out_amount_tax), 0)) /
10000 as stock_in_out_amount_tax_ben,
(IFNULL(sum(tong.sale_amount_tax), 0) - IFNULL(sum(tong.stock_in_out_amount_tax), 0)) /
10000 as stock_in_out_amount_tax_year,
0 tong_profit_increase,
(IFNULL(sum(huan.sale_amount_tax), 0) - IFNULL(sum(huan.stock_in_out_amount_tax), 0)) /
10000 as stock_in_out_amount_tax_month,
0 huan_profit_increase
from falcon.fact_noil_psi fnp
LEFT JOIN tong ON tong.station_code = fnp.station_code and tong.goods_id = fnp.goods_id
LEFT JOIN huan on huan.station_code = fnp.station_code and huan.goods_id = fnp.goods_id
LEFT JOIN falcon.dim_station as ds ON ds.station_code = fnp.station_code
left join falcon_bi.gd_goods_info as gd on gd.goods_id = fnp.goods_id
left join falcon_bi.gd_goods_category as g1 on g1.GOODS_CATE_ID = gd.GOODS_CATE_ID
LEFT JOIN falcon_bi.gd_goods_category as g2 ON g2.GOODS_CATE_ID = g1.GOODS_CATE_PID
where fnp.business_month = ‘202203’
/AND EXISTS (SELECT O1.ORGAN_ID
FROM falcon_bi.sys_organ_info O2,
falcon_bi.sys_organ_info O1
WHERE O2.ORGAN_ID in (‘000105’)
AND INSTR(O1.NODE_ROUTE, O2.NODE_ROUTE)= 1
AND O1.ORGAN_ID = ds.station_code
)/
AND EXISTS (SELECT O1.ORGAN_ID
FROM falcon_bi.sys_organ_info O2,
falcon_bi.sys_organ_info O1
WHERE O2.ORGAN_ID in (‘000105’)
AND INSTR(O1.NODE_ROUTE, O2.NODE_ROUTE)= 1
AND O1.ORGAN_ID = fnp.station_code
)
AND EXISTS(SELECT 1
FROM falcon_bi.gd_goods_category O2,
falcon_bi.gd_goods_category O1
WHERE O2.GOODS_CATE_ID in (‘00111’)
AND INSTR(O1.NODE_ROUTE, O2.NODE_ROUTE) = 1
AND O1.GOODS_CATE_ID = gd.GOODS_CATE_ID)
GROUP BY ds.prov_code, ds.prov_name, ds.area_code, ds.area_name, fnp.station_code, ds.station_name, g2.GOODS_CATE_PID,
g2.GOODS_CATE_PID, g2.GOODS_CATE_ID, g2.GOODS_CATE_NAME, gd.GOODS_CATE_ID, g1.GOODS_CATE_NAME
order by ds.prov_code, ds.area_code, fnp.station_code, g2.GOODS_CATE_PID, g2.GOODS_CATE_ID
这个是异常结果
正确结果
