sql - 递归 CTE 以获得顶级结果
问题描述
这是我当前的数据集。
| BIMUnique | Description | Quantity | SuperiorUnique | LineCode |
|-----------|--------------------------------------------|----------|----------------|-----------|
| 660084 | Top Level Order Description | 1 | 0 | 01 |
| 660085 | Second Level Order Description | 50 | 660084 | 01 |
| 660086 | Second Level Order Description w/sub order | 200 | 660084 | 02 |
| 660087 | Third Level Order Description | 10 | 660086 | 07 |
我想得到这样的东西
| Top Level Description | Immediate Parent | Item Description | Navigation (LineCode Concatenation) | Qty |
|-----------------------------|--------------------------------------------|--------------------------------------------|-------------------------------------|-----|
| Top Level Order Description | 0 | Top Level Order Description | 01 | 1 |
| Top Level Order Description | Top Level Order Description | Second Level Order Description | 01.01 | 50 |
| Top Level Order Description | Top Level Order Description | Second Level Order Description w/sub order | 01.02 | 200 |
| Top Level Order Description | Second Level Order Description w/sub order | Third Level Order Description | 01.02.07 | 10 |
我当前的 CTE 有两个问题 - 首先它不显示顶级父级,只显示直接。其次,ROW_NUMBER 只计算行数,不反映 LineCode。如果我的最终用户创建了 3 个列表项,然后删除了第 2 项,则系统不会返回并重新排序行号。
WITH bi AS
(
SELECT
m.*,
CAST(ROW_NUMBER() OVER (ORDER BY m.LineCode) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS Tree
FROM BidItems m with (nolock)
WHERE m.SuperiorUnique = 0 AND m.JobUnique = '12591'
UNION ALL
SELECT
m.*,
bi.Tree + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY m.SuperiorUnique ORDER BY m.LineCode) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN
FROM BidItems m with (nolock)
JOIN bi ON m.SuperiorUnique = bi.BIMUnique
WHERE m.JobUnique = '12591'
)
SELECT
Job.Number,
Job.Description,
bi.Tree,
bi.LineCode,
bi.Description,
bi.Quantity,
bi.TotalCosts,
bi.*
FROM Job AS job with (nolock)
INNER JOIN bi ON bi.JobUnique = Job.JOBUnique
INNER JOIN BidItems AS sup with (nolock) ON bi.SuperiorUnique = sup.BIMUnique
LEFT JOIN BidItemDetail AS bid with (nolock) ON bid.BidItemUnique = bi.BIMUnique
ORDER BY Bi.Tree
我们在 MSSQL 2012
更新: LineOrder 应该是 LineCode。
解决方案
考虑以下查询,它从根到叶子遍历树。我真的不认为需要row_number()
生成路径,这显然是由LineNumber
s 组成的。
with cte (TopLevelDescription, ImmediateParent, ItemDescription, Navigation, Qty, BIMUnique)
as (
select
Description,
cast(0 as varchar(60)),
Description,
cast(LineOrder as varchar(max)),
Qty,
BIMUnique
from BidItems
where SuperiorUnique = 0
union all
select
c.TopLevelDescription,
c.ItemDescription,
b.Description,
c.Navigation + '.' + b.LineOrder,
b.Qty,
b.BIMUnique
from cte c
inner join BidItems b on b.SuperiorUnique = c.BIMUnique
)
select * from cte
顶层描述 | 直属父母 | 物品描述 | 导航 | 数量 | BIM独特 :---------------------------- | :-------------------------------------------------------- | :-------------------------------------------------------- | :--------- | --: | --------: 顶级订单说明 | 0 | 顶级订单说明 | 1 | 1 | 660084 顶级订单说明 | 顶级订单说明 | 二级订单说明 | 1.1 | 50 | 660085 顶级订单说明 | 顶级订单说明 | 二级订单描述 w/sub order | 1.2 | 200 | 660086 顶级订单说明 | 二级订单描述 w/sub order | 三级订单说明 | 1.2.7 | 10 | 660087
推荐阅读
- javascript - 控制台输出不显示数组元素的数量。代码中无法访问的数组元素
- automated-tests - CAPL - TestStepInconclusive - “当前上下文中不允许使用函数”
- android - LifecycleRegistry 的 popParentState 处的 ArrayIndexOutOfBoundsException
- java - 如何解决命令提示符中的“找不到或加载主类类名”错误?
- asp.net - asp.net mvc 中单选按钮的 HTML 帮助器语法
- reactjs - 从 axios 响应设置状态并在渲染中使用它
- javascript - 在 VS Code 中检查样式组件的 CSS 错误
- c# - FFMPEG。结合命名管道中的 rawAudio 和 rawVideo
- angularjs - 在创建范围 angularjs 的指令中访问和操作变量
- url - myurl.com 没有发送任何数据。ERR_EMPTY_RESPONSE