sql语法错误,信息: mismatched input 'by' expecting ')'(line 10, pos 10) == SQL == with huilv as ( -- 所有年月汇率 select t2.year, CONCAT(CAST(t2.year AS STRING), LPAD (CAST(t2.month AS STRING), 2, '0')) as cal_date, min(t2.exchange_rate) as qty FROM dm_sever_label_nct t0 left join ods_srm_pu_acceptance_item t1 ON t1.item_no = t0.item_no and t1.has_red = 0 left JOIN ods_srm_pu_acceptance t2 ON t1.acpt_id = t2.id AND t2.dr = 0 AND t2.latest = 1 and t2.source_type='InStorage' and t2.std_currency = 'CNY' and t2.orig_currency = 'USD' where t1.item_no is not null and t2.exchange_rate is not null and t2.year is not null and t2.month is not null group by t2.year, CONCAT(CAST(t2.year AS STRING),LPAD (CAST(t2.month AS STRING), 2, '0')), order by CONCAT(CAST(t2.year AS STRING),LPAD (CAST(t2.month AS STRING), 2, '0')) ----------^^^ ), huilv_data as ( -- 所有内存条代码汇率 select t0.item_no, '汇率' as data_type, t2.year as cal_year, t2.cal_date, t2.qty FROM dm_sever_label_nct t0 left join huilv t2 where t0.item_no is not null and t2.qty is not null ), ls_jl_gb as ( -- 历史进料GB select t0.item_no, '历史进料GB' as data_type, t2.year as cal_year, CONCAT(CAST(t2.year AS STRING),LPAD (CAST(t2.month AS STRING), 2, '0')) as cal_date, SUM(CAST(t1.acpt_qty as float)*CAST(t0.item_rlgb1 as float)) as qty FROM dm_sever_label_nct t0 left join ods_srm_pu_acceptance_item t1 ON t1.item_no = t0.item_no and t1.has_red = 0 left JOIN ods_srm_pu_acceptance t2 ON t1.acpt_id = t2.id AND t2.dr = 0 AND t2.latest = 1 and t2.source_type='InStorage' and t2.std_currency = 'CNY' and t2.orig_currency = 'USD' where t1.item_no is not null and t2.id is not null group by t0.item_no, t2.year, t2.month ), ls_jl_sl as ( -- 历史进料数量 select t0.item_no, '历史进料数量(条)' as data_type, t2.year as cal_year, CONCAT(CAST(t2.year AS STRING),LPAD (CAST(t2.month AS STRING), 2, '0')) as cal_date, sum(t1.acpt_qty) as qty FROM dm_sever_label_nct t0 left join ods_srm_pu_acceptance_item t1 ON t1.item_no = t0.item_no and t1.has_red = 0 left JOIN ods_srm_pu_acceptance t2 ON t1.acpt_id = t2.id AND t2.dr = 0 AND t2.latest = 1 and t2.source_type='InStorage' and t2.std_currency = 'CNY' and t2.orig_currency = 'USD' where t1.item_no is not null and t2.id is not null group by t0.item_no, t2.year, t2.month ), ls_jl_dj_cny as ( -- 历史进料单价 select t0.item_no, '历史进料单价(CNY/条)' as data_type, t2.year as cal_year, CONCAT(CAST(t2.year AS STRING),LPAD (CAST(t2.month AS STRING), 2, '0')) as cal_date, first(t1.old_std_price) as qty FROM dm_sever_label_nct t0 left join ods_srm_pu_acceptance_item t1 ON t1.item_no = t0.item_no and t1.has_red = 0 left JOIN ods_srm_pu_acceptance t2 ON t1.acpt_id = t2.id AND t2.dr = 0 AND t2.latest = 1 and t2.source_type='InStorage' and t2.std_currency = 'CNY' and t2.orig_currency = 'USD' where t1.item_no is not null and t2.id is not null group by t0.item_no, t2.year, t2.month ), ls_jl_dj_usd as ( -- 历史进料单价 select t0.item_no, '历史进料单价(USD/条)' as data_type, t2.year as cal_year, CONCAT(CAST(t2.year AS STRING),LPAD (CAST(t2.month AS STRING), 2, '0')) as cal_date, first(t1.old_orig_price) as qty FROM dm_sever_label_nct t0 left join ods_srm_pu_acceptance_item t1 ON t1.item_no = t0.item_no and t1.has_red = 0 left JOIN ods_srm_pu_acceptance t2 ON t1.acpt_id = t2.id AND t2.dr = 0 AND t2.latest = 1 and t2.source_type='InStorage' and t2.std_currency = 'CNY' and t2.orig_currency = 'USD' where t1.item_no is not null and t2.id is not null group by t0.item_no, t2.year, t2.month ), ls_jl_je as ( -- 历史进料金额 select t0.item_no, '历史进料金额(CNY)' as data_type, t2.year as cal_year, CONCAT(CAST(t2.year AS STRING),LPAD (CAST(t2.month AS STRING), 2, '0')) as cal_date, sum(t1.acpt_std_amount) as qty FROM dm_sever_label_nct t0 left join ods_srm_pu_acceptance_item t1 ON t1.item_no = t0.item_no and t1.has_red = 0 left JOIN ods_srm_pu_acceptance t2 ON t1.acpt_id = t2.id AND t2.dr = 0 AND t2.latest = 1 and t2.source_type='InStorage' and t2.std_currency = 'CNY' and t2.orig_currency = 'USD' where t1.item_no is not null and t2.id is not null group by t0.item_no, t2.year, t2.month ), ls_jl_dj_cnygb as ( -- 历史进料单价 select t0.item_no, '历史进料单价(CNY/GB)' as data_type, t0.cal_year, t0.cal_date, first(cast(t0.qty as float)/CAST(t1.item_rlgb1 as float)) as qty FROM ls_jl_dj_cny t0 left join dm_sever_label_nct t1 ON t1.item_no = t0.item_no where t1.item_no is not null group by t0.item_no, t0.cal_year, t0.cal_date ), ls_jl_dj_usdgb as ( -- 历史进料单价 select t0.item_no, '历史进料单价(USD/GB)' as data_type, t0.cal_year, t0.cal_date, first(t0.qty/CAST(t1.item_rlgb1 as float)) as qty FROM ls_jl_dj_usd t0 left join dm_sever_label_nct t1 ON t1.item_no = t0.item_no where t1.item_no is not null group by t0.item_no, t0.cal_year, t0.cal_date ), ls_ck_dj_cny as ( select t0.item_no, '历史出库单价(CNY/条)' as data_type, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 4) as cal_year, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 6) as cal_date, first(t1.item_cost) as qty FROM dm_sever_label_nct t0 left join edw_sup_mfg_material_transactions t1 on t1.inventory_item_code = t0.item_no and t1.transaction_source_type_name = '任务或计划' and t1.subinventory_code not like '%NX%' where t1.inventory_item_code is not null group by t0.item_no, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 4), SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 6) ), ls_ck_dj_usd as ( select t0.item_no, '历史出库单价(USD/条)' as data_type, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 4) as cal_year, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 6) as cal_date, first(CAST(t1.item_cost as float)/CAST(hl.qty as float)) as qty FROM dm_sever_label_nct t0 left join huilv_data hl on t0.item_no = hl.item_no left join edw_sup_mfg_material_transactions t1 on t1.inventory_item_code = t0.item_no and t1.transaction_source_type_name = '任务或计划' and t1.subinventory_code not like '%NX%' and SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 6) = hl.cal_date where t0.item_no is not null and t1.item_cost is not null group by t0.item_no, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 4), SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 6) ), ls_ck_je as ( select t0.item_no, '历史出库金额(CNY)' as data_type, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 4) as cal_year, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 6) as cal_date, sum(t1.outstock_money) as qty FROM dm_sever_label_nct t0 left join edw_sup_mfg_material_transactions t1 on t1.inventory_item_code = t0.item_no and t1.transaction_source_type_name = '任务或计划' and t1.subinventory_code not like '%NX%' where t1.inventory_item_code is not null group by t0.item_no, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 4), SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 6) ), ls_ck_dj_cnygb as ( select t0.item_no, '历史出库单价GB(CNY/GB)' as data_type, t0.cal_year, t0.cal_date, first(CAST(t0.qty as float)/CAST(t1.item_rlgb1 as float)) as qty FROM ls_ck_dj_cny t0 left join dm_sever_label_nct t1 on t0.item_no = t1.item_no where t1.item_no is not null group by t0.item_no, t0.cal_year, t0.cal_date ), ls_ck_dj_usdgb as ( select t0.item_no, '历史出库单价GB(USD/GB)' as data_type, t0.cal_year, t0.cal_date, first(CAST(t0.qty as float)/CAST(t1.item_rlgb1 as float)) as qty FROM ls_ck_dj_usd t0 left join dm_sever_label_nct t1 on t0.item_no = t1.item_no where t1.item_no is not null group by t0.item_no, t0.cal_year, t0.cal_date ), ls_ck_sl as ( select t0.item_no, '历史出库数量(条)' as data_type, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 4) as cal_year, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 6) as cal_date, sum(t1.outstock_qty) as qty FROM dm_sever_label_nct t0 left join edw_sup_mfg_material_transactions t1 on t1.inventory_item_code = t0.item_no and t1.transaction_source_type_name = '任务或计划' and t1.subinventory_code not like '%NX%' where t1.inventory_item_code is not null group by t0.item_no, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 4), SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 6) ), ls_ck_gb as ( select t0.item_no, '历史出库GB' as data_type, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 4) as cal_year, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 6) as cal_date, sum(CAST(t1.outstock_qty as float)*CAST(t0.item_rlgb1 as float)) as qty FROM dm_sever_label_nct t0 left join edw_sup_mfg_material_transactions t1 on t1.inventory_item_code = t0.item_no and t1.transaction_source_type_name = '任务或计划' and t1.subinventory_code not like '%NX%' where t1.inventory_item_code is not null group by t0.item_no, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 4), SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 6) ), ls_kc_sl as ( select t0.item_no, '历史库存条' as data_type, SUBSTR(t1.cal_date, 1, 4) as cal_year, SUBSTR(t1.cal_date, 1, 6) as cal_date, t1.qty as qty FROM dm_sever_label_nct t0 left join dm_server_history_stock t1 on t0.item_no = t1.item_no where t1.item_no is not null and t1.cal_date is not null group by t0.item_no, SUBSTR(REPLACE(t1.cal_date, '-', ''), 1, 4), SUBSTR(REPLACE(t1.cal_date, '-', ''), 1, 6), t1.qty ), ls_kc_gb as ( select t0.item_no, '历史库存GB' as data_type, SUBSTR(t1.cal_date, 1, 4) as cal_year, SUBSTR(t1.cal_date, 1, 6) as cal_date, sum(t1.qty*t0.item_rlgb1) as qty FROM dm_sever_label_nct t0 left join dm_server_history_stock t1 on t0.item_no = t1.item_no where t1.item_no is not null and t1.cal_date is not null group by t0.item_no, SUBSTR(REPLACE(t1.cal_date, '-', ''), 1, 4), SUBSTR(REPLACE(t1.cal_date, '-', ''), 1, 6) ), ls_kc_cny as ( select t0.item_no, '历史库存单价(CNY)' as data_type, SUBSTR(t1.cal_date, 1, 4) as cal_year, SUBSTR(t1.cal_date, 1, 6) as cal_date, first(t1.stock_amount/t1.qty) as qty FROM dm_sever_label_nct t0 left join dm_server_history_stock t1 on t0.item_no = t1.item_no where t1.item_no is not null and t1.cal_date is not null group by t0.item_no, SUBSTR(REPLACE(t1.cal_date, '-', ''), 1, 4), SUBSTR(REPLACE(t1.cal_date, '-', ''), 1, 6), t1.stock_amount, t1.qty ), ls_kc_usd as ( select t0.item_no, '历史库存单价(USD)' as data_type, t1.cal_year, t1.cal_date, first(t1.qty/t2.qty) as qty FROM dm_sever_label_nct t0 left join ls_kc_cny t1 on t0.item_no = t1.item_no left join huilv_data t2 on t0.item_no = t1.item_no and t1.cal_date = t2.cal_date where t1.item_no is not null and t2.item_no is not null and t1.cal_date is not null group by t0.item_no, t1.cal_year, t1.cal_date, t1.qty, t2.qty ), ls_kc_cnygb as ( select t0.item_no, '历史库存单价(CNY/GB)' as data_type, SUBSTR(t1.cal_date, 1, 4) as cal_year, SUBSTR(t1.cal_date, 1, 6) as cal_date, first(t1.stock_amount/t0.item_rlgb1) as qty FROM dm_sever_label_nct t0 left join dm_server_history_stock t1 on t0.item_no = t1.item_no where t1.item_no is not null and t1.cal_date is not null group by t0.item_no, SUBSTR(REPLACE(t1.cal_date, '-', ''), 1, 4), SUBSTR(REPLACE(t1.cal_date, '-', ''), 1, 6), t1.stock_amount, t0.item_rlgb1 ), ls_kc_usdgb as ( select t0.item_no, '历史库存单价(USD/GB)' as data_type, t1.cal_year, t1.cal_date, first(t1.qty/t0.item_rlgb1) as qty FROM dm_sever_label_nct t0 left join ls_kc_usd t1 on t0.item_no = t1.item_no where t1.item_no is not null and t1.cal_date is not null group by t0.item_no, t1.cal_year, t1.cal_date, t1.qty, t0.item_rlgb1 ), main_data as ( select * from ls_kc_sl --历史库存数量 UNION select * from ls_kc_gb --历史库存GB UNION select * from ls_kc_cny --历史库存CNY UNION select * from ls_kc_usd --历史库存USD UNION select * from ls_kc_cnygb --历史库存CNY/GB UNION select * from ls_kc_usdgb --历史库存USD/GB UNION select * from huilv_data --汇率 UNION select * from ls_jl_sl --历史进料条 UNION select * from ls_jl_gb --历史进料GB UNION select * from ls_jl_je --历史进料金额 UNION select * from ls_jl_dj_cnygb --历史进料单价元/GB UNION select * from ls_jl_dj_cny --历史进料单价元/条 UNION select * from ls_jl_dj_usdgb --历史进料单价USD/GB UNION select * from ls_jl_dj_usd --历史进料单价USD/条 UNION select * from ls_ck_sl --历史出库条 UNION select * from ls_ck_gb --历史出库gb UNION select * from ls_ck_je --历史出库金额 UNION select * from ls_ck_dj_cny --历史出库单价元/条 UNION select * from ls_ck_dj_cnygb --历史出库单价元/GB UNION select * from ls_ck_dj_usdgb --历史出库单价usd/GB UNION select * from ls_ck_dj_usd --历史出库单价USD/条 ) INSERT OVERWRITE TABLE dm_server_price_stock_202512 select nct.item_no, '' as item_name, nct.class1, nct.class2, nct.item_bm as bm, '' as jixing, nct.kehu, nct.is_china, nct.is_zte, nct.item_pp, main.data_type, main.cal_year, main.cal_date, main.qty, 0 as num_other1, 0 as num_other2, 0 as num_other3, 0 as num_other4, '' as num_other1, '' as num_other2, '' as num_other3, '' as num_other4, '' as edw_create_date, '' as edw_last_update, '' as edw_data_source, '' as valid_flag from dm_sever_label_nct nct left join main_data main on main.item_no = nct.item_no