explain
insert into sale.dwd_sd_order_htcj_mobile
(weidu,resource_group,steel,order_create_date,order_resource_period,end_time,resource_period_percent,resources_order_weight,order_weight,order_weight_peikuan,order_weight_peikuan_daily)
with
riqi as(
select left(a.resource_period,7)resource_period,
max(end_time)end_time,
'2025-07-01' as rq
from (select max(resource_period)resource_period
from sale.tdm_sd_contract_resource_zyz
where resource_period='2025-07-01' -- 资源月控制
)a left join
(select resource_period,end_time
from sale.tdm_sd_contract_resource_zyz_pace
where '2025-07-01' between start_time and end_time -- 日期控制
group by resource_period,end_time
)b on a.resource_period=b.resource_period
group by a.resource_period)
,resources as(
select
steel,
prod_type ,
date_format(n2.resource_period,'%Y-%m') as resource_period,
case when resource_group='出口' then '出口'
when resource_group='河钢汽车板' then '河钢汽车板'
when resource_group='子公司自营' then '子公司自营'
else '河钢销售' end as area_company_name_in,resource_group,
sum(resource_weight) resource_weight
from
(select
steel,
prod_type ,
resource_group, -- 资源组
resource_period,
resource_weight,
ROW_NUMBER ()
over(partition by steel,prod_type,resource_group,resource_period order by update_time desc)num
from sale.tdm_sd_contract_resource_zyz
where date_format(resource_period,'%Y-%m') =
(select resource_period from riqi)
)n2
where num=1
and date_format(n2.resource_period,'%Y-%m')>'2024-11'
group by steel,
prod_type,date_format(n2.resource_period,'%Y-%m')
,case when resource_group='出口' then '出口'
when resource_group='河钢汽车板' then '河钢汽车板'
when resource_group='子公司自营' then '子公司自营'
else '河钢销售' end,resource_group
)
,peikuan as(
select
-- bill_type_name,
a.steel,a.buss_product_line_name,a1.prod_type_daily,a.product_department_name,
n2.area_company_name_in,a.resource_group_name,resource_group_code,
(select rq from riqi)order_create_date,
order_resource_period,resources_order_weight,
sum(order_weight)/10000 order_weight,
sum(order_weight_peikuan)/10000 order_weight_peikuan,
ifnull(order_weight_peikuan_l,0) as order_weight_peikuan_daily
from sale.tdm_sd_htcj_resource_group a
left join (-- 单日订单量
select steel,buss_product_line_name,product_department_name,resource_group_name,date(order_create_date)order_create_date,
sum(order_weight_peikuan) order_weight_peikuan_l
from sale.tdm_sd_htcj_resource_group
where resource_group_name !='总计'
and order_resource_period=(select resource_period from riqi)
and date(order_create_date)=(select rq from riqi) -- 日期控制
group by steel,buss_product_line_name,product_department_name,resource_group_name,date(order_create_date)
)peikuan_daily
on peikuan_daily.steel=a.steel
and peikuan_daily.buss_product_line_name=a.buss_product_line_name
and peikuan_daily.product_department_name=a.product_department_name
and peikuan_daily.resource_group_name=a.resource_group_name
left join (select distinct steel, steel_num,
prod_line_name as production_line_name, line_num,
prod_type_new as prod_type_daily, production_grand_class
from sale.dim_sd_product_info ) a1
on a1.steel = a.steel
and a1.production_line_name = a.buss_product_line_name
left join (select steel,
prod_type,
area_company_name_in,
resource_group,resource_period,
sum(resource_weight) as resources_order_weight
from resources n1
group by
steel,
prod_type,resource_group,resource_period) n2-- 资源量
on a1.steel = n2.steel
and a1.production_line_name = n2.prod_type
and a.resource_group_name=n2.resource_group
where order_resource_period=(select resource_period from riqi)
and date(a.order_create_date)<=(select rq from riqi) -- 日期控制
and a.resource_group_name !='总计'
group by -- bill_type_name,
a.steel,a.buss_product_line_name,a1.prod_type_daily,a.product_department_name,
a.resource_group_name,resource_group_code,n2.area_company_name_in,
order_weight_peikuan_l,
order_resource_period,resources_order_weight)
,pace as(
select a.*
from sale.tdm_sd_contract_resource_zyz_pace a,
riqi
where date_format(a.resource_period,'%Y-%m')=riqi.resource_period
and a.end_time=riqi.end_time
and a.resource_period_percent is not null
)
select '股份'weidu,'股份'resource_group,
order_create_date,order_resource_period,end_time,resource_period_percent,
sum(resources_order_weight)resources_order_weight,
sum(order_weight)order_weight,
sum(order_weight_peikuan)order_weight_peikuan,
sum(order_weight_peikuan_daily)order_weight_peikuan_daily
from peikuan ,pace
where pace.sale_org is null
and pace.steel='股份'
and pace.product_type is null
group by order_create_date,order_resource_period,end_time,resource_period_percent
union all
select '销售组织'weidu,resource_group,order_create_date,order_resource_period,end_time,resource_period_percent,
sum(resources_order_weight)resources_order_weight,
sum(order_weight),
sum(order_weight_peikuan)order_weight_peikuan,
sum(order_weight_peikuan_daily)order_weight_peikuan_daily
from peikuan ,pace
where pace.resource_group in('出口','子公司自营','河钢汽车板','河钢销售')
and pace.steel='股份'
and pace.product_type is null
and peikuan.area_company_name_in=pace.resource_group
group by order_create_date,order_resource_period,
pace.resource_group,pace.end_time,
pace.resource_period_percent
union all
select '钢厂'weidu,peikuan.steel resource_group,order_create_date,order_resource_period,end_time,resource_period_percent,
sum(resources_order_weight)resources_order_weight,
sum(order_weight),
sum(order_weight_peikuan)order_weight_peikuan,
sum(order_weight_peikuan_daily)order_weight_peikuan_daily
from peikuan ,pace
where pace.sale_org is null
and pace.resource_group is null
and pace.product_type is null
and pace.steel not in ('股份','唐邯承')
and peikuan.steel=pace.steel
group by order_create_date,order_resource_period,
peikuan.steel,pace.end_time,
pace.resource_period_percent
union all
select '品种'weidu,peikuan.prod_type_daily resource_group,order_create_date,
order_resource_period,
end_time,ifnull(resource_period_percent,0)resource_period_percent,
ifnull(sum(resources_order_weight),0)resources_order_weight,
sum(order_weight) order_weight,
sum(order_weight_peikuan)order_weight_peikuan,
sum(order_weight_peikuan_daily)order_weight_peikuan_daily
from peikuan left join pace
on pace.product_type is not null
and peikuan.prod_type_daily=pace.product_type
group by order_create_date,order_resource_period,peikuan.prod_type_daily
,pace.end_time,pace.resource_period_percent
union all
select '资源组'weidu,peikuan.resource_group_name resource_group,order_create_date,
order_resource_period,end_time,ifnull(resource_period_percent,0)resource_period_percent,
ifnull(sum(resources_order_weight),0)resources_order_weight,
sum(order_weight) order_weight,
sum(order_weight_peikuan)order_weight_peikuan,
sum(order_weight_peikuan_daily)order_weight_peikuan_daily
from peikuan left join pace
on pace.resource_group is not null
and pace.steel='股份'
and peikuan.resource_group_name=pace.resource_group
group by order_create_date,peikuan.resource_group_name
,order_resource_period,pace.end_time,pace.resource_period_percent
SQL 错误 [1136] [21S01]: Column count doesn't match value count at row 1
扫一扫
5610
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
