SQL Server Recursive query - Can't get order correct based on display order column

eyh26e7m 于 2023-06-28 发布在其他

关注(0)|答案(1)|浏览(109)

I have a bill of material table (MBOM) and I am trying to write a recursive query. The parts are to be displayed under their respective parent in order defined by displayorder. I can't figure out how to get this in the correct order. The query I am using is

WITH tree ( id, parentid, level, displayorder, uom, quantity ) AS (
    SELECT
        id,
        parentid,
        0 as level,
        displayorder,
        uom,
        quantity
    FROM
        MBOM
    WHERE
        parentid = 47

    UNION ALL

    SELECT
        c2.id,
        c2.parentid,
        tree.level + 1,
        c2.displayorder,
        c2.uom,
        c2.quantity
    FROM
        MBOM c2
        INNER JOIN tree ON tree.id = c2.parentid
)
SELECT
    *
FROM
    tree;

This is my raw data.

parentid	level	id	quantity	uom	displayorder
47	0	17	7.6011	884	10
47	0	6	8.3334	884	20
47	0	19	1	665	30
47	0	55	1	665	40
47	0	102	1	665	50
47	0	103	1	665	60
47	0	104	1	665	70
47	0	58	1	665	80
55	1	41	60	665	10
55	1	4	6	665	20
55	1	45	4	665	40
55	1	2	4	665	50
55	1	1	4	665	60
55	1	115	1	665	70
55	1	118	1	665	80
55	1	142	1	665	90
55	1	119	1	665	100
55	1	125	1	665	110
55	1	359	1	665	130
55	1	164	2	665	140
55	1	155	1	665	150
55	1	160	1	665	160
55	1	67	1	665	170
55	1	57	1	665	180
55	1	123	1	665	190
55	1	106	1	665	120
106	2	6	0.9082	884	10
106	2	92	1	665	30
106	2	131	3	665	40
106	2	372	6	665	20
131	3	130	1	665	10
131	3	12	2	665	20
67	2	66	1	665	10
67	2	5	6	665	20
160	2	114	1	665	10
160	2	5	3	665	20
164	2	163	1	665	10
164	2	12	1	665	20
359	2	6	0.1426	884	10
359	2	41	2	665	20
359	2	71	1	665	30
359	2	141	1	665	40
141	3	140	1	665	10
141	3	12	2	665	20
71	3	70	1	665	10
71	3	5	5	665	20
125	2	6	0.0001	884	10
125	2	93	1	665	30
125	2	161	2	665	40
125	2	374	4	665	20
161	3	162	1	665	10
161	3	12	2	665	20
119	2	6	0.0001	884	10
119	2	65	1	665	30
119	2	129	1	665	40
119	2	121	1	665	50
119	2	374	4	665	20
121	3	12	2	665	20
121	3	120	1	665	10
129	3	128	1	665	10
129	3	12	2	665	20
142	2	42	1	665	10
142	2	144	1	665	20
142	2	146	1	665	30
142	2	148	1	665	40
142	2	150	1	665	50
150	3	405	1	665	20
150	3	149	1	665	10
148	3	405	1	665	20
148	3	147	1	665	10
146	3	405	2	665	20
146	3	145	1	665	10
144	3	405	2	665	20
144	3	143	1	665	10
118	2	6	0.0265	884	10
118	2	41	2	665	20
118	2	117	1	665	30
118	2	81	1	665	40
115	2	6	0.0639	884	10
115	2	41	3	665	20
115	2	116	1	665	30
115	2	111	1	665	40

This is my desired result

parentid	level	id	quantity	uom	displayorder
	0	47	1	665	0
47	1	17	7.6011	884	10
47	1	6	8.3334	884	20
47	1	19	1	665	30
47	1	55	1	665	40
55	2	41	60	665	10
55	2	4	6	665	20
55	2	45	4	665	40
55	2	2	4	665	50
55	2	1	4	665	60
55	2	115	1	665	70
115	3	6	0.0639	884	10
115	3	41	3	665	20
115	3	116	1	665	30
115	3	111	1	665	40
55	2	118	1	665	80
118	3	6	0.0265	884	10
118	3	41	2	665	20
118	3	117	1	665	30
118	3	81	1	665	40
55	2	142	1	665	90
142	3	42	1	665	10
142	3	144	1	665	20
144	4	143	1	665	10
144	4	405	2	665	20
142	3	146	1	665	30
146	4	145	1	665	10
146	4	405	2	665	20
142	3	148	1	665	40
148	4	147	1	665	10
148	4	405	1	665	20
142	3	150	1	665	50
150	4	149	1	665	10
150	4	405	1	665	20
55	2	119	1	665	100
119	3	6	0.0001	884	10
119	3	374	4	665	20
119	3	65	1	665	30
119	3	129	1	665	40
129	4	128	1	665	10
129	4	12	2	665	20
119	3	121	1	665	50
121	4	120	1	665	10
121	4	12	2	665	20
55	2	125	1	665	110
125	3	6	0.0001	884	10
125	3	374	4	665	20
125	3	93	1	665	30
125	3	161	2	665	40
161	4	162	1	665	10
161	4	12	2	665	20
55	2	106	1	665	120
106	3	6	0.9082	884	10
106	3	372	6	665	20
106	3	92	1	665	30
106	3	131	3	665	40
131	4	130	1	665	10
131	4	12	2	665	20
55	2	359	1	665	130
359	3	6	0.1426	884	10
359	3	41	2	665	20
359	3	71	1	665	30
71	4	70	1	665	10
71	4	5	5	665	20
359	3	141	1	665	40
141	4	140	1	665	10
141	4	12	2	665	20
55	2	164	2	665	140
164	3	163	1	665	10
164	3	12	1	665	20
55	2	155	1	665	150
55	2	160	1	665	160
160	3	114	1	665	10
160	3	5	3	665	20
55	2	67	1	665	170
67	3	66	1	665	10
67	3	5	6	665	20
55	2	57	1	665	180
55	2	123	1	665	190
47	1	102	1	665	50
47	1	103	1	665	60
47	1	104	1	665	70
47	1	58	1	665	80

sql-server

来源：https://stackoverflow.com/questions/76561268/recursive-query-cant-get-order-correct-based-on-display-order-column

1条答案

按热度按时间

cx6n0qe31#

SQL will not guarantee any order without an ORDER BY , so you might want to add one:

WITH tree ( id, parentid, level, displayorder, uom, quantity ) AS (
    SELECT
        id,
        parentid,
        0 as level,
        displayorder,
        uom,
        quantity
    FROM
        MBOM
    WHERE
        parentid = 47

    UNION ALL

    SELECT
        c2.id,
        c2.parentid,
        tree.level + 1,
        c2.displayorder,
        c2.uom,
        c2.quantity
    FROM
        MBOM c2
        INNER JOIN tree ON tree.id = c2.parentid
)
SELECT
    *
FROM
    tree
ORDER BY level, displayorder

赞(0）回复(0）举报 2023-06-28

我来回答

SQL Server Recursive query - Can't get order correct based on display order column

1条答案

相关问题

热门标签

最新问答