Στην SQL, η ρήτρα WITH είναι επίσης γνωστή ως CTE. Είναι ένα ισχυρό χαρακτηριστικό που μας δίνει τη δυνατότητα να δημιουργήσουμε προσωρινά σύνολα αποτελεσμάτων μέσα σε ένα ερώτημα. Ένας σημαντικός ρόλος των CTE είναι η απλοποίηση των σύνθετων ερωτημάτων σε μικρότερα και επαναχρησιμοποιήσιμα υποερωτήματα. Αυτό βοηθά να γίνει ο κώδικας πιο ευανάγνωστος και διατηρήσιμος μακροπρόθεσμα.
Ελάτε μαζί μας σε αυτό το σεμινάριο καθώς εξερευνούμε τη λειτουργία των Κοινών εκφράσεων πίνακα χρησιμοποιώντας τον όρο WITH και την υποστηριζόμενη λειτουργικότητα.
Απαιτήσεις:
Για λόγους επίδειξης, θα χρησιμοποιήσουμε τα ακόλουθα:
- MySQL έκδοση 8.0 και νεότερη
- Δείγμα βάσης δεδομένων Sakila
Εφόσον πληρούνται οι δεδομένες απαιτήσεις, μπορούμε να συνεχίσουμε να μαθαίνουμε περισσότερα για τα CTE και την ρήτρα WITH.
Ρήτρα SQL WITH
Ο όρος WITH μας επιτρέπει να ορίσουμε ένα ή περισσότερα προσωρινά σύνολα αποτελεσμάτων τα οποία είναι γνωστά ως Κοινές εκφράσεις πίνακα.
Μπορούμε να αναφερθούμε στα CTE που προκύπτουν στο κύριο ερώτημα όπως κάθε άλλος πίνακας ή σύνολο αποτελεσμάτων. Αυτό διαδραματίζει κρίσιμο ρόλο στη δημιουργία αρθρωτών ερωτημάτων SQL.
Αν και η σύνταξη του CTE μπορεί να διαφέρει ελαφρώς ανάλογα με τις απαιτήσεις σας, τα παρακάτω δείχνουν τη βασική σύνταξη του CTE στην SQL:
ΜΕ cte_name (στήλη 1, στήλη 2, ...) AS (
-- Ερώτημα CTE
ΕΠΙΛΟΓΗ...
ΑΠΟ ...
ΟΠΟΥ ...
)
-- Κύριο ερώτημα
ΕΠΙΛΟΓΗ...
ΑΠΟ ...
ΕΓΓΡΑΦΕΙΤΕ στο cte_name ON...
ΟΠΟΥ ...
Ξεκινάμε με τη λέξη-κλειδί WITH που λέει στη βάση δεδομένων SQL ότι θέλουμε να δημιουργήσουμε και να χρησιμοποιήσουμε το CTE.
Στη συνέχεια, καθορίζουμε το όνομα για το CTE που μας επιτρέπει να το αναφέρουμε σε άλλα ερωτήματα.
Καθορίζουμε επίσης μια προαιρετική λίστα ονομάτων στηλών εάν το CTE περιλαμβάνει τα ψευδώνυμα στηλών.
Στη συνέχεια, προχωράμε στον ορισμό του ερωτήματος CTE. Αυτό περιέχει όλες τις εργασίες ή τα δεδομένα που εκτελεί το CTE εσωκλείονται σε ένα ζευγάρι παρενθέσεων.
Τέλος, καθορίζουμε το κύριο ερώτημα που αναφέρεται στο CTE.
Παράδειγμα χρήσης:
Ένας από τους καλύτερους τρόπους για να κατανοήσετε πώς να χρησιμοποιείτε και να εργάζεστε με CTE είναι να δείτε ένα πρακτικό παράδειγμα.
Πάρτε για παράδειγμα τη δειγματοληπτική βάση δεδομένων Sakila. Ας υποθέσουμε ότι θέλουμε να βρούμε τους 10 κορυφαίους πελάτες με τον μεγαλύτερο αριθμό ενοικιάσεων.
Ρίξτε μια ματιά στο παρακάτω CTE που εμφανίζεται.
Χρησιμοποιώντας τη ρήτρα SQL WITH για να βρείτε τους 10 κορυφαίους πελάτες με τους υψηλότερους αριθμούς ενοικίων:
ΜΕ ΕΝΟΙΚΙΑΣΕΙΣ Πελατών AS (SELECT c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) AS rental_count
ΑΠΟ πελάτη γ
JOIN rental r ON c.customer_id = r.customer_id
ΟΜΑΔΑ ΑΝΑ c.customer_id, c.first_name, c.last_name
)
ΕΠΙΛΟΓΗ *
ΑΠΟ CustomerRentals
ΠΑΡΑΓΓΕΛΙΑ ΚΑΤΑ rental_count DESC
ΟΡΙΟ 10;
Στο συγκεκριμένο παράδειγμα, ξεκινάμε ορίζοντας ένα νέο CTE χρησιμοποιώντας τη λέξη-κλειδί WITH ακολουθούμενη από το όνομα που θέλουμε να εκχωρήσουμε στο CTE. Σε αυτή την περίπτωση, το ονομάζουμε «Ενοικιάσεις πελατών».
Μέσα στο σώμα CTE, υπολογίζουμε τον αριθμό ενοικίων για κάθε πελάτη, συνδέοντας τον πίνακα πελάτη και ενοικιάσεων.
Τέλος, στο κύριο ερώτημα, επιλέγουμε όλες τις στήλες από το CTE, παραγγέλνουμε τα αποτελέσματα με βάση τον αριθμό ενοικίων (φθίνουσα σειρά) και περιορίζουμε την έξοδο μόνο στις πρώτες 10 σειρές.
Αυτό μας επιτρέπει να φέρουμε τους πελάτες με τον μεγαλύτερο αριθμό ενοικιάσεων, όπως φαίνεται στην ακόλουθη έξοδο:
Αναδρομικά CTE
Σε ορισμένες άλλες περιπτώσεις, μπορεί να έχετε να κάνετε με ιεραρχικές δομές δεδομένων. Εδώ μπαίνουν στο παιχνίδι τα αναδρομικά CTE.
Ας πάρουμε για παράδειγμα μια περίπτωση όπου θέλουμε να πλοηγηθούμε στην ιεραρχική οργάνωση ή να αναπαραστήσουμε μια δομή που μοιάζει με δέντρο. Μπορούμε να χρησιμοποιήσουμε τη λέξη-κλειδί WITH RECURSIVE για να δημιουργήσουμε ένα αναδρομικό CTE.
Δεδομένου ότι δεν υπάρχουν ιεραρχικά δεδομένα που να μπορούμε να χρησιμοποιήσουμε στη βάση δεδομένων Sakila για να επιδείξουμε ένα αναδρομικό CTE, ας δημιουργήσουμε ένα βασικό παράδειγμα.
Τμήμα ΔΗΜΙΟΥΡΓΙΑΣ ΤΡΑΠΕΖΙΟΥ (Department_id INT PRIMARY KEY AUTO_INCREMENT,
όνομα_τμήματος VARCHAR(255) NOT NULL,
parent_department_id INT,
ΞΕΝΟ ΚΛΕΙΔΙ (parent_department_id) ΑΝΑΦΟΡΕΣ τμήμα(department_id)
)
ΕΙΣΑΓΩΓΗ ΣΤΟ τμήμα (όνομα_τμήματος, αναγνωριστικό_τμήματος_γονέα)
ΑΞΙΕΣ
('Εταιρική', NULL),
('Οικονομικά', 1),
('HR', 1),
('Λογιστική', 2),
«Στρατολόγηση», 3),
('Μισθοδοσία', 4);
Σε αυτήν την περίπτωση, έχουμε ένα δείγμα πίνακα «τμημάτων» με μερικά τυχαία δεδομένα. Για να βρούμε την ιεραρχική δομή των τμημάτων, μπορούμε να χρησιμοποιήσουμε ένα αναδρομικό CTE ως εξής:
ΜΕ ΑΝΑΔΡΟΜΙΚΗ ΤΜΗΜΑΙΕΡΑΡΧΙΑ ΩΣ (SELECT Department_id, Department_name, parent_department_id
ΑΠΟ τμήμα
ΠΟΥ το parent_department_id ΕΙΝΑΙ ΜΗΧΑΝΟ
ΕΝΩΣΗ ΟΛΩΝ
SELECT d.department_id, d.department_name, d.parent_department_id
ΑΠΟ τμήμα δ
JOIN DepartmentHierarchy dh ON d.parent_department_id = dh.department_id
)
ΕΠΙΛΟΓΗ *
ΑΠΟ Τμήμα Ιεραρχίας;
Σε αυτήν την περίπτωση, το αναδρομικό CTE ξεκινά με τμήματα που έχουν NULL 'parent_department_id' (τμήματα ρίζας) και ανακτά αναδρομικά τα θυγατρικά τμήματα.
συμπέρασμα
Σε αυτό το σεμινάριο, μάθαμε για τις πιο θεμελιώδεις και χρήσιμες δυνατότητες σε βάσεις δεδομένων SQL, όπως οι Κοινές εκφράσεις πίνακα, κατανοώντας τον τρόπο εργασίας με τη λέξη-κλειδί WITH.