我不确定SQL如何排序以下查询。
这是三个表的外观：
根：
ID AuditID R1 R2 R3
1 1 Key1 Text1 20
节点：
ID NodeID N1 N2 N3
1 1 30 40 Text2
1 2 35 45 Text3
叶：
ID NodeID Metric Value
1 1 L1 50
1 1 L2 60
1 1 L3 70
1 2 L1 80
1 2 L2 90
1 2 L3 100
我希望我的输出按以下顺序显示：
name val
R1 Key1
R2 Text1
R3 20
N1 30
N2 40
N3 Text2
N1 35
N2 45
N3 Text3
L1 50
L2 60
L3 70
L1 80
L2 90
L3 100
我使用以下代码来获取这种格式：
select name, val from(
Select convert(nvarchar(max), runpvt.name) as name, runpvt.val from
(select convert(nvarchar(max), R1) as R1,
convert(nvarchar(max), R2) as R2,
convert(nvarchar(max), R3) as R3 from
root
) root
UNPIVOT
(val for name in ([R1], [R2], [R3])) as runpvt
union all
select name, val from(
select convert(nvarchar(max), nunpvt.name) as name, nunpvt.val from
(select ROW_NUMBER() OVER(ORDER BY ID ASC) AS Row#,
convert(nvarchar(max), N1) as N1,
convert(nvarchar(max), N2) as N2,
convert(nvarchar(max), N3) as N3 from
node) node
UNPIVOT
(val for name in (N1, N2, N3)) as nunpvt)tbl1
union all
select name, val from (
select convert(nvarchar(max), l.Metric) as name, convert(nvarchar(max), l.Value) as val,
ROW_NUMBER() OVER (ORDER BY ID ASC) as ROW## from leaf as l)tbl2
)dtbsvalues
Does the ordering by row_number ensure that I will always get the required order?
