CTE
نقل قول: نحوه استفاده از CTE



CTE به معنی Common Table Expression است که توسط آن می توانید بر روی یک نتیجه موقت که با WITH مشخص می کنید، پرس و جوی دیگری را اجرا کنید (مثلاً UPDATE، DELETE، SELECT) و از SQL 2005 به بعد معرفی شد. دقت کنید که CTE لزوماً در حالت Recursion استفاده نمی شه و در حالت غیر بازگشت هم می تونید استفاده کنید.

در حالت Recursion یا بازگشت که برای جداول پدر-فرزندی (جدولی که کلید خارجی به خودش داره) که برای ایجاد درختواره ها استفاده می شن، ابتدا در یک پرس و جو (که در اسکریپت شما با قسمت initialization مشخص شده) باید رکورد ریشه رو مشخص کنید. معمولاً رکوردیه که کلید خارجی اش که پدر رکورد رو مشخص می کنه، NULL هست. بعد این رو UNION ALL می کنید با رکوردهای دیگه ای که از JOIN جدول مورد نظر با CTE ایجاد شده به دست میاد. (قسمتی که در اسکریپت شما با recursion مشخص شده).

چیزی که رخ می ده اینه که در اولین تکرار داخل WITH در قسمت initialization فقط رکورد ریشه و در قسمت recursion رکوردهایی قرار میگیره که ReportsTo اونها برابر EmployeeID به دست اومده از WITH باشه (که چون در اولین تکرار فقط رکورد ریشه داخل WITH قرار داره، رکوردهایی که به مجموعه نتیجه اضافه می شه که پدر اونها رکورد ریشه است. برای این که بهتر متوجه بشوید در پرس و جوی شما یعنی در اولین تکرار کارمندانی مشخص می شوند که مستقیماً به خود رئیس گزارش می دهند. رئیس هم کارمندیه که به کسی گزارش نمی ده، یعنی رکورد ریشه. سپس در تکرار بعدی کارمندانی مشخص می شن که به کارمندانی گزارش می دهند که اکنون در نتیجه ایجاد شده موقت قرار دارند. یعنی کارمندان رده دوم گزارش دهنده). این سیر اونقدر ادامه پیدا می کنه که دیگه رکوردی باقی نمونه. بعد می تونید با نتیجه ایجاد شده هر کاری که بخواهید بکنید. مثلاً روی اون SELECT بزنید. یعنی کاری که الان کرده اید.

نکته ای که بسیار باید بهش دقت کنید این که در رکوردهای جدول شما حلقه وجود نداشته باشه. یعنی رکوردی که پدرش یکی دیگه باشه و وقتی پیگیری می کنی می بینی آخرش به خودش می رسه. این کار باعث می شه که recursion هیچ وقت متوقف نشه.

. آخرین نکته این که اگه قبل از دستور WITH دستور دیگه ای نوشتید، دقت کنید اونو به سمی کالن ختم کرده باشید. مثلاً این طوری:

set no count on;

with ...
منبع :لینک

مثال