```
-- 各个国家项目数量分布
/*
SELECT
count(distinct project_code) as project_count,
country
FROM view.ic_dev15oc_mart.ads_oo_po_vehicle_model_state_bi
GROUP BY country
*/
-- select id,code,name,level,parent_id,help_sort FROM view.ic_dev15oc_mart.view_dwd_plm_imp_plm_pm_prj_t_17010_hw_dev15oc_1054010535
-- where parent_id='2091924844666945548'
-- where level= 5
/*
-- RECURSIVE
WITH RECURSIVE hierarchy AS (
SELECT
id,
parent_id,
level,
CAST(ROW_NUMBER() OVER (ORDER BY id) AS STRING) as base_num,
id as root_id
FROM view.ic_dev15oc_mart.view_dwd_plm_imp_plm_pm_prj_t_17010_hw_dev15oc_1054010535
WHERE level = 1
UNION ALL
SELECT
t.id,
t.parent_id,
t.level,
CONCAT(
h.base_num,
'.',
CAST(ROW_NUMBER() OVER (
PARTITION BY t.parent_id
ORDER BY t.id
) AS STRING)
),
h.root_id
FROM view.ic_dev15oc_mart.view_dwd_plm_imp_plm_pm_prj_t_17010_hw_dev15oc_1054010535 t
INNER JOIN hierarchy h
ON t.parent_id = h.id
AND t.level = h.level + 1
)
SELECT
id,
parent_id,
level,
base_num as multilevel_num
FROM hierarchy
ORDER BY root_id, multilevel_num
*/
/*
WITH sorted_hierarchy AS (
SELECT
id,
parent_id,
level
FROM view.ic_dev15oc_mart.view_dwd_plm_imp_plm_pm_prj_t_17010_hw_dev15oc_1054010535
)
SELECT
l1.id AS level1_id,
l2.id AS level2_id,
l3.id AS level3_id,
l4.id AS level4_id,
l5.id AS level5_id
FROM
(SELECT * FROM sorted_hierarchy WHERE level = 1) l1
LEFT JOIN
(SELECT * FROM sorted_hierarchy WHERE level = 2) l2
ON l1.id = l2.parent_id
LEFT JOIN
(SELECT * FROM sorted_hierarchy WHERE level = 3) l3
ON l2.id = l3.parent_id
LEFT JOIN
(SELECT * FROM sorted_hierarchy WHERE level = 4) l4
ON l3.id = l4.parent_id
LEFT JOIN
(SELECT * FROM sorted_hierarchy WHERE level = 5) l5
ON l4.id = l5.parent_id
-- ORDER BY l1.rank_in_level, l2.rank_in_level ,l3.rank_in_level, l4.rank_in_level,l5.rank_in_level;
*/
SELECT
l1.id AS level1_id,
l1.rank AS rank1,
l2.id AS level2_id,
l2.level AS level2,
l2.help_sort AS help_sort2,
l2.order AS order2,
l3.id AS level3_id,
l3.level AS level3,
l3.help_sort AS help_sort3,
l3.order AS order3,
l4.id AS level4_id,
l4.level AS level4,
l4.help_sort AS help_sort4,
l4.order AS order4,
l5.id AS level5_id,
l5.level AS level5,
l5.help_sort AS help_sort5
l5.order AS order5,
FROM
(SELECT id,parent_id,help_sort, ROW_NUMBER() OVER (ORDER BY id ASC) AS rank FROM view.ic_dev15oc_mart.view_dwd_plm_imp_plm_pm_prj_t_17010_hw_dev15oc_1054010535 WHERE level = 1 ) l1
LEFT JOIN
(SELECT id,parent_id,level,help_sort,ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY help_sort ASC) AS order FROM view.ic_dev15oc_mart.view_dwd_plm_imp_plm_pm_prj_t_17010_hw_dev15oc_1054010535 WHERE level = 2) l2
ON l1.id = l2.parent_id
LEFT JOIN
(SELECT id,parent_id,level,help_sort,ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY help_sort ASC) AS order FROM view.ic_dev15oc_mart.view_dwd_plm_imp_plm_pm_prj_t_17010_hw_dev15oc_1054010535 WHERE level = 3) l3
ON l2.id = l3.parent_id
LEFT JOIN
(SELECT id,parent_id,level,help_sort,ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY help_sort ASC) AS order FROM view.ic_dev15oc_mart.view_dwd_plm_imp_plm_pm_prj_t_17010_hw_dev15oc_1054010535 WHERE level= 4) l4
ON l3.id = l4.parent_id
LEFT JOIN
(SELECT id,parent_id,level,help_sort,ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY help_sort ASC) AS order FROM view.ic_dev15oc_mart.view_dwd_plm_imp_plm_pm_prj_t_17010_hw_dev15oc_1054010535 WHERE level = 5) l5
ON l4.id = l5.parent_id
order by rank1, level2,help_sort2,level3,help_sort3, level4,help_sort4, level5,help_sort5```line 8:7 cannot recognize input near 'l2' '.' 'order' in selection target
最新发布
扫一扫
939
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
