TechCodeview

سوف نستخدم تعبيرات الجدول الشائعة أو CTEs الخاصة بـ SQL Server لتسهيل عمليات الصلات المعقدة والاستعلامات الفرعية. كما يوفر أيضًا طريقة للاستعلام عن البيانات الهرمية، مثل التسلسل الهرمي التنظيمي. تقدم هذه المقالة نظرة عامة كاملة على CTE وأنواع CTE ومزاياها وعيوبها وكيفية استخدامها في SQL Server.

ما هو CTE في SQL Server؟

CTE (تعبير الجدول العام) هو مجموعة نتائج لمرة واحدة موجودة فقط طوال مدة الاستعلام . يسمح لنا بالإشارة إلى البيانات ضمن نطاق تنفيذ بيان SELECT أو INSERT أو UPDATE أو DELETE أو CREATE VIEW أو MERGE واحد. وهو مؤقت لأنه لا يمكن تخزين نتيجته في أي مكان وسيتم فقدانه بمجرد اكتمال تنفيذ الاستعلام. لقد جاء لأول مرة مع إصدار SQL Server 2005. يفضل DBA دائمًا استخدام CTE كبديل للاستعلام الفرعي/العرض. وهي تتبع معيار ANSI SQL 99 وهي متوافقة مع SQL.

بناء جملة CTE في SQL Server

يتضمن بناء جملة CTE اسم CTE، وقائمة أعمدة اختيارية، وبيان/استعلام يحدد تعبير الجدول المشترك (CTE). بعد تعريف CTE، يمكننا استخدامه كطريقة عرض في استعلام SELECT وINSERT وUPDATE وDELETE وMERGE.

ما يلي هو بناء الجملة الأساسي لـ CTE في SQL Server:

 WITH cte_name (column_names) AS (query) SELECT * FROM cte_name; 

في بناء الجملة هذا:

• لقد قمنا أولاً بتحديد اسم CTE الذي سيتم الإشارة إليه لاحقًا في الاستعلام.
• الخطوة التالية هي إنشاء قائمة من الأعمدة المفصولة بفواصل. فهو يضمن أن عدد الأعمدة في وسيطات تعريف CTE يجب أن يكون هو نفسه عدد الأعمدة في الاستعلام. إذا لم نحدد أعمدة وسيطات CTE، فسوف نستخدم أعمدة الاستعلام التي تحدد CTE.
• بعد ذلك، سنستخدم الكلمة الأساسية AS بعد اسم التعبير ثم نحدد عبارة SELECT التي تملأ مجموعة النتائج الخاصة بها CTE.
• وأخيرًا، سوف نستخدم اسم CTE في استعلام مثل عبارة SELECT وINSERT وUPDATE وDELETE وMERGE.

وينبغي أن نأخذ في الاعتبار أثناء كتابة تعريف استعلام CTE؛ لا يمكننا استخدام الجمل التالية:

1. ORDER BY إلا إذا كنت تستخدم أيضًا عبارة TOP
2. داخل
3. جملة OPTION مع تلميحات الاستعلام
4. للتصفح

الصورة أدناه هي تمثيل لتعريف استعلام CTE.

الجزء الأول هنا هو تعبير CTE الذي يحتوي على استعلام SQL يمكن تشغيله بشكل مستقل في SQL. والجزء الثاني هو الاستعلام الذي يستخدم CTE لعرض النتيجة.

مثال

دعونا نفهم كيفية عمل CTE في SQL Server باستخدام أمثلة مختلفة. هنا سنستخدم الجدول عميل ' للمظاهرة. لنفترض أن هذا الجدول يحتوي على البيانات التالية:

في هذا المثال، اسم CTE هو customer_in_newyork ، يقوم الاستعلام الفرعي الذي يحدد CTE بإرجاع الأعمدة الثلاثة اسم العميل، البريد الإلكتروني، و ولاية . ونتيجة لذلك، سيعيد عملاء CTE_in_newyork جميع العملاء الذين يعيشون في ولاية نيويورك.

بعد تحديد CTE Customers_in_newyork، قمنا بالإشارة إليه في ملف يختار بيان للحصول على تفاصيل هؤلاء العملاء الموجودين في نيويورك.

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York') SELECT c_name, email, state FROM customers_in_NewYork; 

بعد تنفيذ البيان أعلاه، فإنه سوف يعطي الإخراج التالي. هنا، يمكننا أن نرى أن النتيجة تُرجع فقط معلومات العميل الموجود في ولاية نيويورك.

مرض الاعتلال الدماغي المزمن المتعدد

في بعض الحالات، سنحتاج إلى إنشاء استعلامات CTE متعددة وضمها معًا لرؤية النتائج. قد نستخدم مفهوم CTEs المتعدد في هذا السيناريو. نحتاج إلى استخدام عامل الفاصلة لإنشاء استعلامات CTE متعددة ودمجها في عبارة واحدة. يجب أن يسبق عامل الفاصلة '،' اسم CTE للتمييز بين CTE المتعددة.

تساعدنا CTEs المتعددة في تبسيط الاستعلامات المعقدة التي يتم ضمها معًا في النهاية. كان لكل قطعة معقدة CTE الخاصة بها، والتي يمكن بعد ذلك الرجوع إليها وضمها خارج جملة NOT.

ملاحظة: يمكن تعريف تعريف CTE المتعدد باستخدام UNION أو UNION ALL أو JOIN أو INTERSECT أو EXCEPT.

ويوضح بناء الجملة أدناه ذلك بشكل أكثر وضوحا:

 WITH cte_name1 (column_names) AS (query), cte_name2 (column_names) AS (query) SELECT * FROM cte_name UNION ALL SELECT * FROM cte_name; 

مثال

دعونا نفهم كيفية عمل CTE المتعددة في SQL Server. وهنا سوف نستخدم ما ورد أعلاه عميل 'طاولة للمظاهرة.

في هذا المثال، قمنا بتعريف اسمي CTE customer_in_newyork و customer_in_california . ثم تقوم مجموعة نتائج الاستعلامات الفرعية الخاصة بـ CTEs بملء CTE. وأخيرًا، سوف نستخدم أسماء CTE في الاستعلام الذي سيعيد جميع العملاء الموجودين في نيويورك و ولاية كاليفورنيا .

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York'), customers_in_California AS (SELECT * FROM customer WHERE state = 'California') SELECT c_name, email, state FROM customers_in_NewYork UNION ALL SELECT c_name, email, state FROM customers_in_California; 

ولاية نيويورك وكاليفورنيا.

لماذا نحتاج CTE؟

مثل طرق عرض قاعدة البيانات والجداول المشتقة، يمكن أن تسهل CTEs كتابة وإدارة الاستعلامات المعقدة من خلال جعلها أكثر سهولة في القراءة وأكثر بساطة. يمكننا تحقيق هذه الخاصية عن طريق تقسيم الاستعلامات المعقدة إلى كتل بسيطة يمكن إعادة استخدامها في إعادة كتابة الاستعلام.

بعض حالات استخدامه مذكورة أدناه:

• يكون ذلك مفيدًا عندما نحتاج إلى تحديد جدول مشتق عدة مرات ضمن استعلام واحد.
• يكون مفيدًا عندما نحتاج إلى إنشاء بديل لطريقة عرض في قاعدة البيانات.
• يكون ذلك مفيدًا عندما نحتاج إلى إجراء نفس الحساب عدة مرات على مكونات استعلام متعددة في وقت واحد.
• يكون ذلك مفيدًا عندما نحتاج إلى استخدام وظائف التصنيف مثل ROW_NUMBER() وRANK() وNTILE().

بعض مزاياها مذكورة أدناه:

جافا الرياضيات العشوائية

• CTE يسهل صيانة التعليمات البرمجية بشكل أسهل.
• يزيد CTE من سهولة قراءة الكود.
• أنه يزيد من أداء الاستعلام.
• يتيح CTE تنفيذ الاستعلامات العودية بسهولة.

أنواع CTE في SQL Server

يقسم SQL Server CTE (تعبيرات الجدول الشائعة) إلى فئتين عريضتين:

1. CTE العودية
2. CTE غير العودية

CTE العودية

يُعرف تعبير الجدول الشائع باسم CTE العودي الذي يشير إلى نفسه. ويستند مفهومها على العودية، والتي يتم تعريفها بأنها ' تطبيق عملية أو تعريف متكرر بشكل متكرر .' عندما نقوم بتنفيذ استعلام عودي، فإنه يتكرر بشكل متكرر عبر مجموعة فرعية من البيانات. يتم تعريفه ببساطة على أنه استعلام يستدعي نفسه. هناك شرط نهائي في مرحلة ما، لذلك فهو لا يطلق على نفسه اسم لا نهائي.

يجب أن يكون لـ CTE العودي ملف الاتحاد الكل بيان وتعريف استعلام ثانٍ يشير إلى CTE نفسه ليكون متكررًا.

مثال

دعونا نفهم كيف يعمل CTE العودي في SQL Server. النظر في البيان أدناه، والذي يولد سلسلة من أول خمسة أرقام فردية:

 WITH odd_num_cte (id, n) AS ( SELECT 1, 1 UNION ALL SELECT id+1, n+2 from odd_num_cte where id <5 ) select * from odd_num_cte; < pre> <p>When we execute this recursive CTE, we will see the output as below:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-5.webp" alt="CTE in SQL Server"> <p>The below example is the more advanced recursive CTE. Here, we are going to use the &apos; <strong>jtp_employees</strong> &apos; table for a demonstration that contains the below data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-6.webp" alt="CTE in SQL Server"> <p>This example will display the hierarchy of employee data. Here table provides a reference to that person&apos;s manager for each employee. The reference is itself an employee id within the same table.</p> <pre> WITH cte_recursion (EmpID, FirstName, LastName, MgrID, EmpLevel) AS ( SELECT EmployeeID, FirstName, LastName, ManagerID, 1 FROM jtp_employees WHERE ManagerID IS NULL UNION ALL SELECT emp.EmployeeID, emp.FirstName, emp.LastName, emp.ManagerID, r.EmpLevel + 1 FROM jtp_employees emp INNER JOIN cte_recursion r ON emp.ManagerID = r.EmpID ) SELECT FirstName + &apos; &apos; + LastName AS FullName, EmpLevel, (SELECT FirstName + &apos; &apos; + LastName FROM jtp_employees WHERE EmployeeID = cte_recursion.MgrID) AS Manager FROM cte_recursion ORDER BY EmpLevel, MgrID </pre> <p>This CTE will give the following output where we can see the hierarchy of employee data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-7.webp" alt="CTE in SQL Server"> <h3>Non-Recursive CTE</h3> <p>A common table expression that doesn&apos;t reference itself is known as a non-recursive CTE. A non-recursive CTE is simple and easier to understand because it does not use the concept of recursion. According to the CTE Syntax, each CTE query will begin with a &apos; <strong>With</strong> &apos; clause followed by the CTE name and column list, then AS with parenthesis.</p> <h2>Disadvantages of CTE</h2> <p>The following are the limitations of using CTE in SQL Server:</p> <ul> <li>CTE members are unable to use the keyword clauses like Distinct, Group By, Having, Top, Joins, etc.</li> <li>The CTE can only be referenced once by the Recursive member.</li> <li>We cannot use the table variables and CTEs as parameters in stored procedures.</li> <li>We already know that the CTE could be used in place of a view, but a CTE cannot be nested, while Views can.</li> <li>Since it&apos;s just a shortcut for a query or subquery, it can&apos;t be reused in another query.</li> <li>The number of columns in the CTE arguments and the number of columns in the query must be the same.</li> </ul> <hr></5>

سيعطي CTE المخرجات التالية حيث يمكننا رؤية التسلسل الهرمي لبيانات الموظف:

CTE غير العودية

يُعرف تعبير الجدول الشائع الذي لا يشير إلى نفسه باسم CTE غير العودي. إن مرض CTE غير العودي بسيط وأسهل للفهم لأنه لا يستخدم مفهوم العودية. وفقًا لصيغة CTE، سيبدأ كل استعلام CTE بـ ' مع عبارة 'متبوعة باسم CTE وقائمة الأعمدة، ثم AS بين قوسين.

عيوب CTE

فيما يلي قيود استخدام CTE في SQL Server:

• لا يستطيع أعضاء CTE استخدام عبارات الكلمات الرئيسية مثل Distinct وGroup By وHaving وTop وJoins وما إلى ذلك.
• لا يمكن الرجوع إلى CTE إلا مرة واحدة بواسطة العضو التكراري.
• لا يمكننا استخدام متغيرات الجدول وCTEs كمعلمات في الإجراءات المخزنة.
• نحن نعلم بالفعل أنه يمكن استخدام CTE بدلاً من طريقة العرض، ولكن لا يمكن تداخل CTE، بينما يمكن استخدام طرق العرض.
• وبما أنه مجرد اختصار لاستعلام أو استعلام فرعي، فلا يمكن إعادة استخدامه في استعلام آخر.
• يجب أن يكون عدد الأعمدة في وسيطات CTE هو نفسه عدد الأعمدة في الاستعلام.