如何根据Id获取逐行数据

7 PKG-2 3套餐2 5 13项目1B 2项目2B NULL 8 PKG-3 3套餐3 13 9 PKG-4 3包4 13 i am having a table PackageDetailshaving data as belowPackage_ID Package_Name [Level] Description Main_Package_Id 5 Project 1A 2 Project 1B NULL 6 PKG-1 3 Package 15 7 PKG-2 3 Package 25 8 PKG-3 3 Package 313 9 PKG-4 3 Package 413 13 Project 1B 2 Project 2BNULL 14 Project 2 2 Project 2NULL 12 PKG-7 3 Package 714 ---- and so onfrom the above table data 5,13 are Main_Package_Id''s so we are having NULL value in that Main_Package_Id column. Package_ID''s 6,7 are sub_packages of 5, similarly Package_ID''s 8,9 are are sub_packages of 13.now i want the output in below format:Package_ID Package_Name [Level] Description Main_Package_Id 5 Project 1A 2 Project 1B NULL 6 PKG-1 3 Package 15 7 PKG-2 3 Package 25 13 Project 1B 2 Project 2BNULL 8 PKG-3 3 Package 313 9 PKG-4 3 Package 413推荐答案 create table packagedetails(package_id int ,Package_name varchar(20),[Level] int,Description varchar(30),Main_package_id int)insert into packagedetails values(5,'Project 1A', 2,'Project 1B', NULL),(6, 'PKG-1', 3, 'Package 1',5),(7, 'PKG-2', 3,' Package 2',5),(8, 'PKG-3', 3 ,'Package 3', 13),(9, 'PKG-4', 3, 'Package 4', 13),(13, 'Project 1B', 2, 'Project 2B', NULL ),(14, 'Project 2', 2, 'Project 2', NULL ),(12, 'PKG-7', 3 ,'Package 7', 14)with cte as(select packagedetails.*,case when Main_package_id is null then package_id else Main_package_id end as newpackageid,1 as Lvl From packagedetailswhere Main_package_id is nullunion allselect packagedetails.*,newpackageid,cte.Lvl -1 as Lvlsfrom packagedetailsjoin cte on packagedetails.Main_package_id =cte.package_idwhere packagedetails.Main_package_id is not null)select package_id,package_name,level,description,row_number() over (partition by newpackageid order by lvl desc) as rowFrom cte 这篇关于如何根据Id获取逐行数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！