如何根据Id获取逐行数据 [英] how to get row by row data based on Id

问题描述

我有一个表PackageDetails

，数据如下

Package_ID Package_Name [等级]描述Main_Package_Id

5项目1A 2项目1B空白

6 PKG-1 3包1 5

7 PKG-2 3包2 5

8 PKG-3 3包3 13

9 PKG-4 3套餐4 13

13项目1B 2项目2B NULL

14项目2 2项目2 NULL

12 PKG-7 3套餐7 14

----依此类推

来自上表数据5,13是Main_Package_Id'所以我们在Main_Package_Id列中有NULL值。 Package_ID'的6,7是sub_packages为5，类似Package_ID'的8,9是sub_packages为13.



现在我想要以下格式的输出：

Package_ID Package_Name [等级]描述Main_Package_Id

5项目1A 2项目1B空白

6 PKG-1 3包1 5
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 PackageDetails
having data as below
Package_ID Package_Name [Level] Description Main_Package_Id
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
---- and so on
from 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 1 5
7 PKG-2 3 Package 2 5
13 Project 1B 2 Project 2B NULL
8 PKG-3 3 Package 3 13
9 PKG-4 3 Package 4 13

推荐答案

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 packagedetails
where Main_package_id is null
union all
select packagedetails.*,newpackageid,cte.Lvl -1 as Lvls
from packagedetails
join cte on packagedetails.Main_package_id =cte.package_id
where 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 row
From cte

这篇关于如何根据Id获取逐行数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！