Το πρόβλημα
Πριν από μερικές μέρες μια συνάδελφος ήρθε με το εξής πρόβλημα, ήθελε να δείξει κάποια δεδομένα σε ένα 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
SQLschool.gr Old 
Μια εναλλακτικη λυση ειναι η παρακατω:
SELECT t1.A, t1.B, t1.C, t1.D, t1.P
FROM T AS t1
OUTER APPLY (SELECT o.P FROM T AS o WHERE o.A = t1.A AND o.B IS NULL AND o.C IS NULL AND o.D IS NULL) AS A_order
OUTER APPLY (SELECT o.P FROM T AS o WHERE o.A = t1.A AND o.B =t1.B AND o.C IS NULL AND o.D IS NULL) AS B_order
OUTER APPLY (SELECT o.P FROM T AS o WHERE o.A = t1.A AND o.B =t1.B AND o.C = t1.C AND o.D IS NULL) AS C_order
ORDER BY A_order.p, A,
B_order.p, B,
C_order.p, C,
CASE WHEN t1.D IS NULL THEN CAST(NULL AS INT) ELSE t1.P END
Original Λυση: 310 scans on T, 310 logical reads, ~60ms elapsed time
Εναλλακτικη : 4 scans on T, 4 logical reads, ~3ms elapsed time
Οταν ο αριθμος εγγραφων στον πινακα Τ μεγαλωνει, η διαφορα στον χρονο εκτελεσης των δυο λυσεων γινεται πιο αισθητη:
truncate table T
— 1st Level
insert into T(A, P)
select row_number() over (order by x) as A, row_number() over (order by y) as B
from
(
select top 20 ABS(checksum(newid())) as x, ABS(checksum(newid())) as y
from sys.all_columns
) AS data
order by A
— 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
Επισης, η λυση με string concatenation δεν επιστρεφει σωστη ταξινομηση οταν η στηλη P περιεχει αρνητικες τιμες
ενδιαφέρουσα άποψη θα την μελετήσω και θα επανέλθω σε ευχαριστώ για την επισήμανση σου
Σωστή η παρατήρηση σου αλλά το σενάριο για το οποίο υλοποιήθηκε η λύση δεν θα είχε ποτέ αρνητικές τιμές. Αν υπήρχε η περίπτωση αυτή θα την είχα λάβει υπόψην