sqlschool.gr logo

articles

Articles of SQLschool.gr Team

T-SQL Challenge #1:Different Level Ordering in Hierarchy

Antonios Chatzipavlis
Sunday 06 March 2011

Το πρόβλημα

Πριν από μερικές μέρες μια συνάδελφος ήρθε με το εξής πρόβλημα, ήθελε να δείξει κάποια δεδομένα σε ένα treeview control σε ένα web page. Στην ουσία ήταν μια ιεραρχία που από την δομή του πίνακα έβγαινε αρκετά εύκολα με ένα order by clause. Όμως δεν ήταν τόσο απλά τα πράγματα, ήθελε να υπάρχει ταξινόμηση ανά επίπεδο ιεραρχίας το οποίο ορίζονταν από ένα άλλο πεδίο.

Για να εξηγήσουμε καλύτερα την πρόκληση αυτή ας έρθουμε να δούμε το πώς ήταν τα δεδομένα της

Ο πίνακας της ήταν ο εξής

CREATE TABLE T (A INT, B INT, C INT, D INT, P INT)

Το πεδίο Α ορίζει το 1ο level της ιεραρχίας, το Β το 2ο, το C το 3ο, το D το 4ο και το P είναι αυτό που ορίζει το σειρά του αντίστοιχου στοιχείου στο level σε σχέση με τα άλλα στοιχεία στο ίδιο level.

Ας έρθουμε να γεμίσουμε τον πίνακα αυτό με μερικά δεδομένα

-- 1st Level
insert into T values (1,null,null,null,2), (2,null,null,null,1), (3,null,null,null,3)

--2nd Level
insert into T
select y.A,x.A,x.B,null,x.P from T x cross join T y

--3rd Level
insert into T
select distinct y.A,x.A,x.B,null,x.P from (select * from T where B is not null) x
cross join (select * from T where B is not null) y

--4th Level
insert into T
select distinct y.A,x.A,x.B,x.C,x.P from (select * from T where C is not null) x
cross join (select * from T where C is not null) y

Εάν εκτελέσουμε το παρακάτω query θα έχουμε άμεσα την ιεραρχία μας αλλά χωρίς τον ορισμό της σειράς του στοιχείου ανά level.

select * from T order by A,B,C,D

To αποτέλεσμα μας θα είναι το παρακάτω

A B C D P
1 NULL NULL NULL 2
1 1 NULL NULL 2
1 1 1 NULL 2
1 1 1 1 2
1 1 1 2 1
1 1 1 3 3
1 1 2 NULL 1
1 1 2 1 2
1 1 2 2 1
1 1 2 3 3
1 1 3 NULL 3
1 1 3 1 2
1 1 3 2 1
1 1 3 3 3
1 2 NULL NULL 1
1 2 1 NULL 2
1 2 1 1 2
1 2 1 2 1
1 2 1 3 3
1 2 2 NULL 1
1 2 2 1 2
1 2 2 2 1
1 2 2 3 3
1 2 3 NULL 3
1 2 3 1 2
1 2 3 2 1
1 2 3 3 3
1 3 NULL NULL 3
1 3 1 NULL 2
1 3 1 1 2
1 3 1 2 1
1 3 1 3 3
1 3 2 NULL 1
1 3 2 1 2
1 3 2 2 1
1 3 2 3 3
1 3 3 NULL 3
1 3 3 1 2
1 3 3 2 1
1 3 3 3 3
2 NULL NULL NULL 1
2 1 NULL NULL 2
2 1 1 NULL 2
2 1 1 1 2
2 1 1 2 1
2 1 1 3 3
2 1 2 NULL 1
2 1 2 1 2
2 1 2 2 1
2 1 2 3 3
2 1 3 NULL 3
2 1 3 1 2
2 1 3 2 1
2 1 3 3 3
2 2 NULL NULL 1
2 2 1 NULL 2
2 2 1 1 2
2 2 1 2 1
2 2 1 3 3
2 2 2 NULL 1
2 2 2 1 2
2 2 2 2 1
2 2 2 3 3
2 2 3 NULL 3
2 2 3 1 2
2 2 3 2 1
2 2 3 3 3
2 3 NULL NULL 3
2 3 1 NULL 2
2 3 1 1 2
2 3 1 2 1
2 3 1 3 3
2 3 2 NULL 1
2 3 2 1 2
2 3 2 2 1
2 3 2 3 3
2 3 3 NULL 3
2 3 3 1 2
2 3 3 2 1
2 3 3 3 3
3 NULL NULL NULL 3
3 1 NULL NULL 2
3 1 1 NULL 2
3 1 1 1 2
3 1 1 2 1
3 1 1 3 3
3 1 2 NULL 1
3 1 2 1 2
3 1 2 2 1
3 1 2 3 3
3 1 3 NULL 3
3 1 3 1 2
3 1 3 2 1
3 1 3 3 3
3 2 NULL NULL 1
3 2 1 NULL 2
3 2 1 1 2
3 2 1 2 1
3 2 1 3 3
3 2 2 NULL 1
3 2 2 1 2
3 2 2 2 1
3 2 2 3 3
3 2 3 NULL 3
3 2 3 1 2
3 2 3 2 1
3 2 3 3 3
3 3 NULL NULL 3
3 3 1 NULL 2
3 3 1 1 2
3 3 1 2 1
3 3 1 3 3
3 3 2 NULL 1
3 3 2 1 2
3 3 2 2 1
3 3 2 3 3
3 3 3 NULL 3
3 3 3 1 2
3 3 3 2 1
3 3 3 3 3

Εάν κάποιος δοκιμάσει να να λύσει το πρόβλημα αυτό απλά βάζοντας και το P στο order by δεν θα πάρει το επιθυμητό αποτέλεσμα καθώς θέλω διαφορετικό ordering ανά level.

Η λύση

Για να λυθεί η άσκηση αυτή ιδανικό και εύκολο στην υλοποίηση pattern είναι αυτό των Common Table Expressions (CTE).

Ας δούμε την λύση πρώτα και ας την εξηγήσουμε αμέσως μετά

WITH R (A,B,C,D,P,O)
AS 
(
-- 1st level
SELECT A,B,C,D,P,RIGHT('00000'+CAST(P AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(A AS NVARCHAR(5)),5) 
                FROM T WHERE B IS NULL 
-- 2nd level
UNION ALL
SELECT A,B,C,D,P,RIGHT('00000'+CAST((SELECT P FROM T X WHERE X.A =T.A AND X.B IS NULL) AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(A AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(P AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(B AS NVARCHAR(5)),5)
    FROM T WHERE B IS NOT NULL AND C IS NULL 
-- 3rd Level
UNION ALL
SELECT A,B,C,D,P,RIGHT('00000'+CAST((SELECT P FROM T X WHERE X.A =T.A AND X.B IS NULL) AS NVARCHAR(5)),5)
                 +RIGHT('00000'+CAST(A AS NVARCHAR(5)),5)+
                 +RIGHT('00000'+CAST((SELECT P FROM T X WHERE X.A =T.A AND X.B =T.B AND X.C IS NULL) AS NVARCHAR(5)),5)
                 +RIGHT('00000'+CAST(B AS NVARCHAR(5)),5)+
                 +RIGHT('00000'+CAST(P AS NVARCHAR(5)),5)
                 +RIGHT('00000'+CAST(C AS NVARCHAR(5)),5) 
    FROM T WHERE B IS NOT NULL AND C IS NOT NULL AND D IS NULL 
--4th level
UNION ALL
SELECT A,B,C,D,P,RIGHT('00000'+CAST((SELECT P FROM T X WHERE X.A =T.A AND X.B IS NULL) AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(A AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST((SELECT P FROM T X WHERE X.A =T.A AND X.B =T.B AND X.C IS NULL) AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(B AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST((SELECT P FROM T X WHERE X.A =T.A AND X.B =T.B AND X.C=T.C AND D IS NULL) AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(C AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(P AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(D AS NVARCHAR(5)),5)
                  
    FROM T WHERE B IS NOT NULL AND C IS NOT NULL AND D IS NOT NULL 
)
SELECT * FROM R ORDER BY O

Η εξήγηση της λύσης

Στην περίπτωση μας ξέρουμε τον αριθμό των επιπέδων που έχουμε, καθώς επίσης ξέρουμε και το κριτήριο με το οποίο σε κάθε επίπεδο θέλουμε να τα ταξινομήσουμε το οποίο δεν είναι άλλο από το πεδίο P.

Για να το πετύχουμε αυτό θα πρέπει να έχουμε παράξουμε μια τιμή (Ο στο CTE) που με βάση αυτή θα γίνεται η τελική ταξινόμηση. Η τιμή αυτή θα πρέπει να έχει την δομή ταξινόμηση-level για κάθε γραμμή δεδομένων για το αντίστοιχο level, αλλά και για τους πατέρες αυτού.

Αρχίζοντας από το 1ο level (το εξασφαλίζουμε αυτό με το WHERE B IS NULL) όπου παράγουμε την τιμή για το Ο πεδίο προσθέτοντας την θέση που ορίζετε από το P και το level που ορίζεται από το πεδίο A ως εξής

RIGHT('00000'+CAST(P AS NVARCHAR(5)),5)+RIGHT('00000'+CAST(A AS NVARCHAR(5)),5)

Ο λόγος που χρησιμοποιούμε την μορφή αυτή είναι γιατί πρέπει να εξασφαλίσουμε την ορθή ταξινόμιση καθώς αν δεν χρησιμοποιούμε την RIGHT(‘00000’+CAST(…),5) θα βρεθούμε αντιμέτωποι με το 15 να έρχετε πριν το 2 αν υποθέσουμε ότι οι τιμές σε δύο διαφορετικά records ήταν αυτές.

Συνεχίζουμε με το 2ο level (το εξασφαλίζουμε αυτό με το WHERE B IS NOT NULL AND C IS NULL) παράγουμε την τιμή τιμή για το Ο πεδίο όπως παραπάνω αλλά επειδή είναι το δεύτερο επίπεδο θα πρέπει να προσθέσουμε την αντίστοιχη τιμή του πατέρα του πριν από αυτή την οποία και διαβάζουμε με ένα correlated subquery  όπως παρακάτω

  RIGHT('00000'+CAST((SELECT P FROM T X WHERE X.A =T.A AND X.B IS NULL) AS NVARCHAR(5)),5)
 +RIGHT('00000'+CAST(A AS NVARCHAR(5)),5)
 +RIGHT('00000'+CAST(P AS NVARCHAR(5)),5)
 +RIGHT('00000'+CAST(B AS NVARCHAR(5)),5)

Πράττουμε το ίδιο και για τα επόμενα επίπεδα και στο τέλος λέμε στο CTE να τα κάνει order by O. (SELECT * FROM R ORDER BY O)

Το τελικό αποτέλεσμα

Αυτό θα έχει σαν αποτέλεσμα να ικανοποιήσουμε την ανάγκη μας στο 100% όπως φαίνεται παρακάτω μετά από την εκτέλεση της παραπάνω λύσης.

A B C D P O
2 NULL NULL NULL 1 0000100002
2 2 NULL NULL 1 00001000020000100002
2 2 2 NULL 1 000010000200001000020000100002
2 2 2 2 1 0000100002000010000200001000020000100002
2 2 2 1 2 0000100002000010000200001000020000200001
2 2 2 3 3 0000100002000010000200001000020000300003
2 2 1 NULL 2 000010000200001000020000200001
2 2 1 2 1 0000100002000010000200002000010000100002
2 2 1 1 2 0000100002000010000200002000010000200001
2 2 1 3 3 0000100002000010000200002000010000300003
2 2 3 NULL 3 000010000200001000020000300003
2 2 3 2 1 0000100002000010000200003000030000100002
2 2 3 1 2 0000100002000010000200003000030000200001
2 2 3 3 3 0000100002000010000200003000030000300003
2 1 NULL NULL 2 00001000020000200001
2 1 2 NULL 1 000010000200002000010000100002
2 1 2 2 1 0000100002000020000100001000020000100002
2 1 2 1 2 0000100002000020000100001000020000200001
2 1 2 3 3 0000100002000020000100001000020000300003
2 1 1 NULL 2 000010000200002000010000200001
2 1 1 2 1 0000100002000020000100002000010000100002
2 1 1 1 2 0000100002000020000100002000010000200001
2 1 1 3 3 0000100002000020000100002000010000300003
2 1 3 NULL 3 000010000200002000010000300003
2 1 3 2 1 0000100002000020000100003000030000100002
2 1 3 1 2 0000100002000020000100003000030000200001
2 1 3 3 3 0000100002000020000100003000030000300003
2 3 NULL NULL 3 00001000020000300003
2 3 2 NULL 1 000010000200003000030000100002
2 3 2 2 1 0000100002000030000300001000020000100002
2 3 2 1 2 0000100002000030000300001000020000200001
2 3 2 3 3 0000100002000030000300001000020000300003
2 3 1 NULL 2 000010000200003000030000200001
2 3 1 2 1 0000100002000030000300002000010000100002
2 3 1 1 2 0000100002000030000300002000010000200001
2 3 1 3 3 0000100002000030000300002000010000300003
2 3 3 NULL 3 000010000200003000030000300003
2 3 3 2 1 0000100002000030000300003000030000100002
2 3 3 1 2 0000100002000030000300003000030000200001
2 3 3 3 3 0000100002000030000300003000030000300003
1 NULL NULL NULL 2 0000200001
1 2 NULL NULL 1 00002000010000100002
1 2 2 NULL 1 000020000100001000020000100002
1 2 2 2 1 0000200001000010000200001000020000100002
1 2 2 1 2 0000200001000010000200001000020000200001
1 2 2 3 3 0000200001000010000200001000020000300003
1 2 1 NULL 2 000020000100001000020000200001
1 2 1 2 1 0000200001000010000200002000010000100002
1 2 1 1 2 0000200001000010000200002000010000200001
1 2 1 3 3 0000200001000010000200002000010000300003
1 2 3 NULL 3 000020000100001000020000300003
1 2 3 2 1 0000200001000010000200003000030000100002
1 2 3 1 2 0000200001000010000200003000030000200001
1 2 3 3 3 0000200001000010000200003000030000300003
1 1 NULL NULL 2 00002000010000200001
1 1 2 NULL 1 000020000100002000010000100002
1 1 2 2 1 0000200001000020000100001000020000100002
1 1 2 1 2 0000200001000020000100001000020000200001
1 1 2 3 3 0000200001000020000100001000020000300003
1 1 1 NULL 2 000020000100002000010000200001
1 1 1 2 1 0000200001000020000100002000010000100002
1 1 1 1 2 0000200001000020000100002000010000200001
1 1 1 3 3 0000200001000020000100002000010000300003
1 1 3 NULL 3 000020000100002000010000300003
1 1 3 2 1 0000200001000020000100003000030000100002
1 1 3 1 2 0000200001000020000100003000030000200001
1 1 3 3 3 0000200001000020000100003000030000300003
1 3 NULL NULL 3 00002000010000300003
1 3 2 NULL 1 000020000100003000030000100002
1 3 2 2 1 0000200001000030000300001000020000100002
1 3 2 1 2 0000200001000030000300001000020000200001
1 3 2 3 3 0000200001000030000300001000020000300003
1 3 1 NULL 2 000020000100003000030000200001
1 3 1 2 1 0000200001000030000300002000010000100002
1 3 1 1 2 0000200001000030000300002000010000200001
1 3 1 3 3 0000200001000030000300002000010000300003
1 3 3 NULL 3 000020000100003000030000300003
1 3 3 2 1 0000200001000030000300003000030000100002
1 3 3 1 2 0000200001000030000300003000030000200001
1 3 3 3 3 0000200001000030000300003000030000300003
3 NULL NULL NULL 3 0000300003
3 2 NULL NULL 1 00003000030000100002
3 2 2 NULL 1 000030000300001000020000100002
3 2 2 2 1 0000300003000010000200001000020000100002
3 2 2 1 2 0000300003000010000200001000020000200001
3 2 2 3 3 0000300003000010000200001000020000300003
3 2 1 NULL 2 000030000300001000020000200001
3 2 1 2 1 0000300003000010000200002000010000100002
3 2 1 1 2 0000300003000010000200002000010000200001
3 2 1 3 3 0000300003000010000200002000010000300003
3 2 3 NULL 3 000030000300001000020000300003
3 2 3 2 1 0000300003000010000200003000030000100002
3 2 3 1 2 0000300003000010000200003000030000200001
3 2 3 3 3 0000300003000010000200003000030000300003
3 1 NULL NULL 2 00003000030000200001
3 1 2 NULL 1 000030000300002000010000100002
3 1 2 2 1 0000300003000020000100001000020000100002
3 1 2 1 2 0000300003000020000100001000020000200001
3 1 2 3 3 0000300003000020000100001000020000300003
3 1 1 NULL 2 000030000300002000010000200001
3 1 1 2 1 0000300003000020000100002000010000100002
3 1 1 1 2 0000300003000020000100002000010000200001
3 1 1 3 3 0000300003000020000100002000010000300003
3 1 3 NULL 3 000030000300002000010000300003
3 1 3 2 1 0000300003000020000100003000030000100002
3 1 3 1 2 0000300003000020000100003000030000200001
3 1 3 3 3 0000300003000020000100003000030000300003
3 3 NULL NULL 3 00003000030000300003
3 3 2 NULL 1 000030000300003000030000100002
3 3 2 2 1 0000300003000030000300001000020000100002
3 3 2 1 2 0000300003000030000300001000020000200001
3 3 2 3 3 0000300003000030000300001000020000300003
3 3 1 NULL 2 000030000300003000030000200001
3 3 1 2 1 0000300003000030000300002000010000100002
3 3 1 1 2 0000300003000030000300002000010000200001
3 3 1 3 3 0000300003000030000300002000010000300003
3 3 3 NULL 3 000030000300003000030000300003
3 3 3 2 1 0000300003000030000300003000030000100002
3 3 3 1 2 0000300003000030000300003000030000200001
3 3 3 3 3 0000300003000030000300003000030000300003

Reusability

Το παραπάνω query μπορεί εύκολα να γίνει ένα view το οποίο θα μπορεί να καλεστεί από οπουδήποτε και να παρέχει άμεσα το επιθυμητό αποτέλεσμα χωρίς να χρειαστεί να κάνω κάτι έξτρα στην εφαρμογή που θα το καλέσει. (ξέρετε τώρα αγαπητοί μου συνάδελφοι developers, λίστες επί λιστών, loops μέσα σε loops και άλλα τέτοια ωραία πράγματα τα οποία κάνουμε επειδή μας αρέσει το OOP (object oriented programming, η επεξήγηση αυτή για τους αγαπητούς admins),…). Απλά θέλει μια μικρή αλλαγή στο τελικό SELECT καθώς αυτό έχω ORDER BΥ και όπως είναι γνωστό για να παίξει αυτό σε view θέλει στο SELECT να βάλω την TOP. Για την περίπτωση αυτή το SELECT θα είναι το παρακάτω

SELECT TOP 9999999999 * FROM R ORDER BY O

antonch

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios Chatzipavlis is a highly experienced Data Solutions Consultant and Trainer. He has been working in the IT industry since 1988, holding various roles such as senior developer, IT Manager, Data & AI Solutions Architect and Consultant.

Since 1995, Antonios has focused on modern technologies and software development tools, primarily by Microsoft. He has specialized in Data & AI since 2000, with expertise in Microsoft Data Platform (SQL Server, Azure SQL Databases, Azure Synapse Analytics, Microsoft Fabric, Power BI, AI) and Databricks.

Antonios is also a Microsoft Certified Trainer (MCT) for over 25 years, has been recognized as a Microsoft Most Valuable Professional (MVP) in Data Platform since 2010 and he is in the Data Expert 40 Powerlist 2024 by Boussias. He is the co-founder and visionary behind XLYTiCA, a company dedicated to Data & AI solutions.

Episode

Task Flows in Microsoft Fabric

image

More Episodes...

Tip

Get Certified: Become a Fabric Data Engineer

More Tips...

Become a member

If you want to receive updates from us become a member to our community.

Connect

Explore

Learn

sqlschool.gr © 2010-2025 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.