sql-server - 仅按父级排序的 T-SQL 递归查询
问题描述
我正在尝试实现以下要求:
我有一个带有备件的产品层次结构。这些备件可以在图表中包含其他部分,这些部分可能具有多层深度。
我的目标是编写一个 T-SQL 查询,它允许我按表的任何列(在示例 Product、Price 或 EndOfWarrantyDate 中)进行排序,但保持层次结构。换句话说,我只想订购父母的物品,而不用担心孩子应该只跟随父母来玩。
在下面的示例中,如果您注意到,绿色行按“EndOfWarrantyDate”排序,但我仍然想在其父级旁边显示子级和子级(我不关心对子级进行排序,只要尊重等级制度)。
注意:上表中的 ID 只是为了更好地识别层次结构,在 db 中我有一个普通的身份列作为 ID。
而且,如果可能的话,我想在单个 SQL 查询中做到这一点,而不必先收集父项,对它们进行排序,然后获取相关的子项和子项。
我现在的CTE看起来像这样,但我不知道如何实现排序。尝试使用 PARTITION,但不知道如何正确应用它来解决我的问题。
WITH
cteProducts (Id, Product, ParentProductId, Price, EndOfWarrantyDate, Depth, RootId)
AS
(
-->>>>>>>>>>Root>>>>>>>>>>>>>>>>>
SELECT Id, Product, ParentProductId, Price, EndOfWarrantyDate, 0 as Depth, Id as RootId
FROM Products
WHERE ParentProductId IS NULL
-->>>>>>>>>>Root>>>>>>>>>>>>>>>>>
UNION ALL
-->>>>>>>>>>Children>>>>>>>>>>>>>>>>>
SELECT e.Id, e.Product, e.ParentProductId, e.Price, e.EndOfWarrantyDate, r.Depth + 1, r.RootId
FROM Products e
INNER JOIN cteProducts r
ON e.ParentProductId = r.Id
-->>>>>>>>>>Children>>>>>>>>>>>>>>>>>
)
SELECT
Id, Product, ParentProductId, Price, EndOfWarrantyDate, Depth, RootId
FROM cteProducts
--ORDER BY RootId, EndOfWarrantyDate
非常感谢任何帮助,谢谢!
解决方案
将父级保留EndOfWarrantyDate
为递归 CTE 的一部分。这样你就可以在最后进行排序。
样本数据
declare @Products table
(
Id nvarchar(10),
Product nvarchar(20),
ParentProductId nvarchar(10),
Price int,
EndOfWarrantyDate date
);
insert into @Products (Id, Product, ParentProductId, Price, EndOfWarrantyDate) values
('1', 'Vacuum', null, 130, '2013-04-02'),
('1.1', 'Power Unit', '1', 200, '2024-01-01'),
('1.1.1', 'Unit part', '1.1', 100, '2024-01-01'),
('1.2', 'Filter', '1', 10, '2022-07-15'),
('2', 'Laptop', null, 600, '2023-06-01'),
('2.1', 'Hard Disk', '2', 200, '2024-03-01'),
('3', 'Washing Machine', null, 1000, '2023-12-01');
解决方案
WITH
cteProducts (Id, Product, ParentProductId, Price, EndOfWarrantyDate, Depth, RootId, ParentDate)
AS
(
-->>>>>>>>>>Root>>>>>>>>>>>>>>>>>
SELECT Id, Product, ParentProductId, Price, EndOfWarrantyDate, 0 as 'Depth', Id as 'RootId'
,EndOfWarrantyDate as 'ParentDate' -- new field
FROM @Products
WHERE ParentProductId IS NULL
-->>>>>>>>>>Root>>>>>>>>>>>>>>>>>
UNION ALL
-->>>>>>>>>>Children>>>>>>>>>>>>>>>>>
SELECT e.Id, e.Product, e.ParentProductId, e.Price, e.EndOfWarrantyDate, r.Depth + 1, r.RootId
,r.ParentDate -- new field
FROM @Products e
INNER JOIN cteProducts r
ON e.ParentProductId = r.Id
-->>>>>>>>>>Children>>>>>>>>>>>>>>>>>
)
SELECT ParentDate,
Id, Product, ParentProductId, Price, EndOfWarrantyDate, Depth, RootId
FROM cteProducts
order by ParentDate, Id; -- new field as first sort field
结果
ParentDate Id Product ParentProductId Price EndOfWarrantyDate Depth RootId
---------- ---------- -------------------- --------------- ----------- ----------------- ----------- ----------
2013-04-02 1 Vacuum NULL 130 2013-04-02 0 1
2013-04-02 1.1 Power Unit 1 200 2024-01-01 1 1
2013-04-02 1.1.1 Unit part 1.1 100 2024-01-01 2 1
2013-04-02 1.2 Filter 1 10 2022-07-15 1 1
2023-06-01 2 Laptop NULL 600 2023-06-01 0 2
2023-06-01 2.1 Hard Disk 2 200 2024-03-01 1 2
2023-12-01 3 Washing Machine NULL 1000 2023-12-01 0 3
推荐阅读
- azure-devops - 如何使用命令行任务将 Azure Pipeline 变量打印到文件?
- debugging - 应用程序的发布版本未请求许可。调试版本正常工作
- sql - 空结果预期实际值
- php - codeigniter 中的分页 URL 问题
- webots - 为什么我不能在我的原型的 BallJoint 节点的“设备”字段中插入 RotationalMotor 节点?
- linux - 如何杀死已使用源执行的脚本?
- python - 在 QDateTimeEdit 中获取当前日期时间
- if-statement - 根据单元格中的值将数据从一张表复制到另一张表
- asp.net-mvc - url.action 不会在 asp.net mvc 中生成 url
- swift - NSCollectionView 的完成处理程序/事件