MySQL रिकर्सिव CTE में महारत: पदानुक्रम पार करना और श्रृंखला उत्पन्न करना

एक रिकर्सिव CTE (कॉमन टेबल एक्सप्रेशन) एक सबक्वेरी है जो अपने स्वयं के नाम का उपयोग करके स्वयं को संदर्भित करती है। इसे WITH RECURSIVE का उपयोग करके परिभाषित किया जाता है और इसमें एक समाप्ति स्थिति होनी चाहिए। रिकर्सिव CTE श्रृंखला उत्पन्न करने, पदानुक्रमित डेटा पार करने, और ग्राफ ट्रैवर्सल के लिए उपयोगी हैं।

सिंटैक्स

WITH RECURSIVE cte_name (col1, col2, ...) AS (
  -- नॉन-रिकर्सिव (बेस केस): प्रारंभिक पंक्तियाँ
  SELECT col1, col2 FROM table_name
  UNION ALL
  -- रिकर्सिव केस: cte_name को संदर्भित करता है
  SELECT col1, col2 FROM cte_name WHERE condition
) SELECT * FROM cte_name;

• पहला SELECT बेस केस है जो प्रारंभिक पंक्तियाँ प्रदान करता है।
• UNION ALL प्रत्येक पुनरावृत्ति से पंक्तियाँ जोड़ता है (DISTINCT डुप्लिकेट हटाता है)।
• दूसरा SELECT रिकर्सिव केस है जो WHERE स्थिति विफल होने तक चलता है।

उदाहरण 1: पहले 5 विषम संख्याएँ उत्पन्न करें

WITH RECURSIVE odd_no (sr_no, n) AS (
  SELECT 1, 1
  UNION ALL
  SELECT sr_no + 1, n + 2 FROM odd_no WHERE sr_no < 5
) SELECT * FROM odd_no;

+-------+---+
| sr_no | n |
+-------+---+
| 1     | 1 |
| 2     | 3 |
| 3     | 5 |
| 4     | 7 |
| 5     | 9 |
+-------+---+

बेस केस (1, 1) लौटाता है। प्रत्येक पुनरावृत्ति sr_no को 1 से और n को 2 से बढ़ाती है। रिकर्सन तब रुकती है जब sr_no 5 तक पहुंच जाता है।

उदाहरण 2: कर्मचारी पदानुक्रम

एक अधिक व्यावहारिक उपयोग मैनेजर-कर्मचारी पदानुक्रम पार करना:

-- मान लें: employees(id, name, manager_id)
WITH RECURSIVE org_chart (id, name, level) AS (
  -- बेस: शीर्ष-स्तरीय मैनेजर (कोई मैनेजर नहीं)
  SELECT id, name, 0 FROM employees WHERE manager_id IS NULL
  UNION ALL
  -- रिकर्सिव: प्रत्यक्ष रिपोर्ट खोजें
  SELECT e.id, e.name, oc.level + 1
  FROM employees e JOIN org_chart oc ON e.manager_id = oc.id
) SELECT * FROM org_chart ORDER BY level;

यह शीर्ष-स्तरीय मैनेजर (स्तर 0) से शुरू होने वाला एक संगठनात्मक पेड़ बनाता है और प्रत्येक स्तर पर सभी रिपोर्ट को रिकर्सिवली ढूंढता है।

मुख्य बिंदु

• रिकर्सिव SELECT के WHERE क्लॉज में हमेशा एक समाप्ति स्थिति शामिल करें ताकि अनंत लूप से बचा जा सके।
• MySQL में डिफ़ॉल्ट रिकर्सन सीमा 1000 पुनरावृत्तियों की है (cte_max_recursion_depth के माध्यम से कॉन्फ़िगर करने योग्य)।
• प्रदर्शन के लिए UNION ALL का उपयोग करें; डुप्लिकेट हटाने की आवश्यकता होने पर ही UNION DISTINCT।

निष्कर्ष

रिकर्सिव CTE WITH RECURSIVE का उपयोग करके बेस केस और रिकर्सिव केस के साथ सेल्फ-रेफरेंसिंग क्वेरी को परिभाषित करते हैं। वे MySQL में पदानुक्रमित डेटा ट्रैवर्सल (ऑर्ग चार्ट, श्रेणी पेड़), श्रृंखला उत्पन्न करने, और ग्राफ ट्रैवर्सल के लिए आवश्यक हैं।