خواندن ۹ دقیقه·۲ سال پیش

جدول مشتق شده (derived table) در SQL Server چیست؟

جدول مشتق شده یک table expression است که در قسمت FROM یک کوئری ظاهر می شود. درواقع یک subquery است که درون یک clause FROM قرار گرفته است. به دلیل قرار گرفتن در یک عبارت FROM، مجموعه نتایج subquery را می توان مشابه جدول SQL Server استفاده کرد.شما می توانید جداول مشتق شده را زمانی اعمال کنید که استفاده از نام مستعار ستون (column aliases) امکان پذیر نباشد زیرا قبل از اینکه نام مستعار مشخص شود، بند دیگری توسط SQL translator پردازش می شود.

ا subquery در قسمت FROM باید یک نام داشته باشد. یکی از دلایل گنجاندن یک جدول مشتق شده در یک outer query، ساده کردن outer query است. چون می‌توانید پیچیدگی یک کوئری را در یک جدول مشتق شده در یک عبارت FROM مدفون کنید و به مقادیر ستون subquery در جدول مشتق شده مراجعه کنید.

از آنجایی که می توانید با جداول مشتق شده مانند جداول معمولی SQL Server رفتار کنید، می توانید دو یا چند جدول مشتق شده را join کنید. همچنین، می توانید کوئری هایی را که به جدول مشتق شده در داخل یکدیگر ارجاع می دهند، قرار دهید. یک نمونه کد در این بخش از آموزش این قابلیت جدول مشتق شده را برای شمارش رکوردهای تکراری در یک منبع داده نشان می دهد.

ایجاد یک جدول مشتق شده پایه (Creating a Basic Derived Table)

کوئری زیر به عنوان subquery برای یک جدول مشتق شده عمل می کند. اغلب ایده خوبی است که قبل از استفاده از T-SQL برای subquery در یک جدول مشتق شده، مجموعه نتایج یک query را بررسی کنید. کوئری زیر مقادیر سه ستون جدول CountryRegion در پایگاه داده AdventureWorks2014 را فهرست می کند. هر ردیف در جدول مربوط به یک کشور است. ستون CountryRegionCode کلید اصلی (primary key) جدول CountryRegion است. این code یک مقدار فیلد نوع داده short nvarchar است که یک کشور را نشان می دهد. می توانید آن را به عنوان مخفف نام کشور در نظر بگیرید. ستون Name نام کامل کشور در یک ردیف در جدول است. ستون ModifiedDate یک فیلد datetime (تاریخ) است که نشان دهنده آخرین زمانی است که ردیف وارد شده یا تغییر کرده است، هر کدام که جدیدترین باشد.

-- columns of data from CountryRegion table in Person schema
SELECT
  [CountryRegionCode],
  [Name],
  [ModifiedDate]
FROM [AdventureWorks2014].[Person].[CountryRegion]

در اینجا گزیده ای از ده ردیف اول در مجموعه نتایج از کوئری قبلی است. از گوشه سمت راست نمایشگر توجه کنید که دویست و سی و هشت ردیف در مجموعه نتایج کامل وجود دارد:

بلوک کد T-SQL بعدی یک مثال جدول مشتق شده بسیار ابتدایی است. درونی ترین کوئری در اسکریپت زیر شامل کوئری برای سه ستون از جدول CountryRegion در عبارت FROM آن است. subquery در قسمت FROM دارای نام my_derived_table است. کوئری outer-most (بیرونی ترین کوئری) از جدول مشتق شده به عنوان منبع خود استفاده می کند. علاوه بر این، سه عملیات (operation) خاص انجام می شود.

ابتدا ستونی با نام Country Alphabet index فهرست موارد SELECT را هدایت می کند. این یک ستون محاسبه شده (computed column) بر اساس فیلد Name از درونی ترین کوئری (inner-most) است. تابع substring اولین حرف از مقدار ستون Name را از inner-most کوئری استخراج (extract) می کند.
دوم، آیتم SELECT بعدی نام مستعار (alias name) Country Name را به ستون Name از درونی ترین پرس و جو اختصاص می دهد. هنگامی که مجموعه نتایج با یک SELECT نمایش داده می شود، با نام مستعار آن ظاهر می شود. با این حال، همچنان باید به ستون Name از درونی ترین کوئری با نام نامستعار(un-aliased) آن در موارد لیست SELECT و همچنین WHERE و ORDER BY ارجاع دهید.
سوم، بیرونی ترین کوئری نیز نتایج خود را با کاراکتر اول مقدار ستون Name از جدول مشتق شده مرتب می کند.

-- country name and country region code in alphabetical order 
-- alphabetized by country name
SELECT
  SUBSTRING(name, 1, 1) [Country Alphabet index],
  name [Country Name],
  [CountryRegionCode]
FROM ( -- columns of data from CountryRegion table in Person schema
      SELECT
         [CountryRegionCode],
        [Name],
        [ModifiedDate]
      FROM [AdventureWorks2014].[Person].[CountryRegion]
     ) my_derived_table
ORDER BY LEFT(name, 1)

در اینجا گزیده ای است که ده ردیف اول مجموعه نتیجه را از اسکریپت قبلی نشان می دهد. همه ستون ها با ستون های جدول مشتق شده یکسان نیستند.

ستون اول index ِ الفبای اسم کشور را دارد. مقدار این index فقط اولین حرف برای اسم یک کشور است که از طریق تابع substring استخراج می شود.
ستون دوم دارای header ستونی از Country Name است که نام مستعار Name از جدول مشتق شده است.
ستون سوم همان نامی است که در جدول مشتق شده است - CountryRegionCode.
ستون سوم از جدول مشتق شده در بیرونی ترین کوئری فیلتر می شود. فیلتر کردن با عدم درج نام ستون ModifiedDate در بیرونی ترین کوئری انجام می شود.
ترتیب سطرها در مجموعه نتایج بر اساس ORDER BY در انتهای کوئری است. توجه داشته باشید که کد T-SQL به نام مستعار - (aliased name) (Name) و نه نام نا مستعار (Country Name) (un-aliased name) در ORDER BY ارجاع می دهد.
در نهایت، توجه داشته باشید که دویست و سی و هشت ردیف در مجموعه نتایج بیرونی ترین کوئری وجود دارد. تمام سطرهای جدول مشتق شده به بیرونی ترین کوئری منتقل می شوند. اما، می‌توانید به‌صورت اختیاری از یک فیلتر در WHERE برای محدود کردن عبور سطرها از جدول مشتق‌شده به بیرونی‌ترین کوئری استفاده کنید.

ا join کردن derived Table ها (Joining Derived Tables)

این بخش دو جدول اضافی از پایگاه داده Adventureworks2014 را معرفی می کند. اولین مورد از این جدول ها جدول CountryRegionCurrency و دومین جدول اضافی جدول Currency است.

جدول CountryRegionCurrency یک junction table بین جدول CountryRegion و Currency است.
کلید اصلی جدول CountryRegionCurrency یک کلید ترکیبی بر اساس دو فیلد است: CountryRegionCode و CurrencyCode. یادتون باشه که:

-- ا CountryRegionCode همچنین کلید اصلی (primary key) جدول CountryRegion است.

-- ا CurrencyCode همچنین کلید اصلی (pk) جدول Currency است.

هر ردیف در جدول CountryRegion می تواند با صفر، یک یا بیش از یک ردیف در جدول Currency مطابقت داشته باشد (مچ شود).
هر ردیف در جدول Currency می تواند با یک یا چند ردیف در جدول CountryRegion مطابقت داشته باشد.

اسکریپت‌های T-SQL زیر تمام ردیف‌های جداول CountryRegionCurrency و Currency را فهرست می‌کنند. یک اسکریپت برای فهرست کردن ردیف‌های جدول CountryRegion در مثال قبل ظاهر می‌شود.

-- columns of data from CountryRegionCurrency table in Sales schema
SELECT
  [CountryRegionCode],
  [CurrencyCode],
  [ModifiedDate]
FROM [AdventureWorks2014].[Sales].[CountryRegionCurrency]

----------------------------------------------------------------------------------

-- columns of data from Currency table in Sales schema
SELECT
  [CurrencyCode],
  [Name],
  [ModifiedDate]
FROM [AdventureWorks2014].[Sales].[Currency]

در اینجا گزیده ای با ده ردیف اول از هر یک از دو کوئری قبلی آورده شده است.

پنجره بالا ردیف هایی از جدول CountryRegionCurrency را نشان می دهد.
پنجره پایین ردیف هایی از جدول Currency را نشان می دهد.
تعداد سطرهای هر جدول در گزیده زیر نشان داده نشده است. 109 ردیف در جدول CountryRegionCurrency و 105 ردیف در جدول Currency وجود دارد.

-- از آنجایی که 238 ردیف در جدول CountryRegion وجود دارد، این تأیید می کند که کشورهای زیادی در جدول CountryRegion بدون ارز مشابه در جدول Currency وجود دارد.

-- به طور مشابه، از آنجایی که تعداد ردیف‌های جدول Currency کمتر از تعداد ردیف‌های جدول CountryRegionCurrency است، این تأیید می‌کند که برخی از کشورها می‌توانند بیش از یک ارز مشابه داشته باشند.

پنجره بالایی مرحله قبل را در ردیف سوم و چهارم خود نشان می دهد

-- هر دو ردیف مربوط به Austria است که برای CountryRegionCode آن مقدار AT دارد.

-- ردیف سوم به Austrian Shilling (ATS) به عنوان یک ارز اشاره می کند.

-- ردیف چهارم یورو (EUR) را به عنوان واحد پول نشان می دهد.

اسکریپت بعدی نحوه تعیین یک inner joinبین دو جدول مشتق شده را نشان می دهد - یکی بر اساس جدول CountryRegion و دیگری بر اساس جدول CountryRegionCurrency.

جدول مشتق شده با اسم مختصر cr مجموعه ای از 238 ردیف نتایج را از جدول CountryRegion برمی گرداند.
جدول مشتق شده با اسم مختصر crc مجموعه ای از 109 ردیف نتایج را از جدول CountryRegionCurrency برمی گرداند.
عملگر inner join و تطبیق آن بر روی کلمات کلیدی (CountryRegionCode) نشان می دهد که هر سطر در مجموعه نتایج جدول مشتق شده cr باید با هر تعداد ردیف در مجموعه نتایج جدول مشتق شده crc مطابقت داشته باشد هر زمان که مقدار CountryRegionCode در هر دو جدول یکسان باشد.
از آنجایی که فقط 109 ردیف در جدول مشتق شده crc وجود دارد و هر یک از ردیف های آن با یک ردیف در جدول مشتق شده cr مطابقت دارد، مجموعه نتایج برای کوئری بیرونی شامل 109 ردیف است.

-- there are 109 country-currency pairings
-- this query demonstrates an inner join with derived tables
-- some countries have more than one matching currency
SELECT
  cr.CountryRegionCode,
  cr.Name [Country Name],
  crc.CurrencyCode
FROM ( -- there are 238 country region codes
       SELECT
         [CountryRegionCode],
         [Name],
         [ModifiedDate]
       FROM [AdventureWorks2014].[Person].[CountryRegion]
     ) cr
INNER JOIN 
     ( -- there are 109 currency codes in CountryRegionCurrency
       SELECT
         [CountryRegionCode],
         [CurrencyCode],
         [ModifiedDate]
       FROM [AdventureWorks2014].[Sales].[CountryRegionCurrency]
     ) crc ON cr.CountryRegionCode = crc.CountryRegionCode

گزیده زیر ده سطر اول از بیرونی ترین مجموعه نتیجه را نشان می دهد.

مجموعه نتایج دارای سه ستون بر اساس موارد لیست SELECT از کوئری بیرونی در اسکریپت قبلی است.

ستون ها برای:

--- ا CountryRegionCode از جدول مشتق شده cr

--- ا Name از جدول مشتق شده cr. به این قسمت نام مستعار (alias name) Country Name اختصاص داده شده است

--- ا CurrencyCode از جدول مشتق شده crc

به دلیل شامل شدن نام کشور برای مقادیر مطابق CountryRegionCode، کاربران فهرست می توانند به سرعت از یک مقدار CountryRegionCode به نام کشور مطابق آن برسند.
با درج نام کشور در مجموعه نتایج، به راحتی می توان تشخیص داد که Austria و Belgium دارای چندین نوع ارز در مجموعه نتایج هستند.

یکی از راه های بالقوه برای بهبود مجموعه نتایج قبلی، اضافه کردن نام ارز به عنوان یک ستون است. می‌توانید با افزودن یک left join از ردیف‌های جدول Currency به ردیف‌های جدول CountryRegionCurrency در کوئری قبلی، به این نتیجه برسید. اسکریپت زیر نحوه دستیابی به این نتیجه را با جداول مشتق شده نشان می دهد.

جدول مشتق sql server Derived Table

Girl

شاید از این پست‌ها خوشتان بیاید