ورود به سیستم 
 
    محتویات سایت
        برچسب های محبوب 








 
   متصل کردن داده های قبلی و بعدی به سطر جاری
 
   SQL Server
   ۷۹۵۰
   این مقاله حاوی فایل ضمیمه نمی باشد
   محمد سلیم آبادی
   ۱۳۹۰/۲/۱۰
نسخه قابل چاپ نسخه قابل چاپ

مقدمه

برای حل بعضی از مسائل نیاز داریم که مقادیر سطرهایی که نزدیکترین مقدار را به کلید سطر جاری دارند را در سطر فعلی انتقال دهیم.

در حالت معمول برای تولید ای نتیجه نیاز به self-join هستیم. البته توابع Window ی LEAD و LAG که استاندارد نیز هستند نیاز ما را به self-join بر طرف می کند. هنوز SQL Server از این دو تابع پیشتیبانی نمی کند. ولی در برخی دیگر از RDBMS ها این دو تابع پیاده سازی شده اند.

اگر ما جدولی داشته باشیم که دارای یک ستونی به نام nbr بوده و اعداد متوالی نه دقیقا پشت سر هم داشته باشد و ستونی به نام val که مقداری در آن ذخیره می شود. آنگاه هدف ما آوردن مقادیر سطرهایی که نزدیک ترین nbr را به سطر فعلی دارند به سطر جاری است.


خب، اگر مقادیر دقیقا بطور متوالی و پشت سر هم باشند و هیچگونه فضای هرزی بین ارقام وجود نداشته باشد کافیست که جدول با خودش دو بار join شود. ولی در این مورد اینگونه نیست.

مساله

در کلاس درس پایگاه داده های دانشگاهی استاد مساله SQL فوق را مطرح می کند و ذکر می کند جوابهایی پذیرفته خواهند بود که 1.استاندارد بوده 2.راه حل تنها از یک SELECT تشکلیل شده باشد 3.استفاده از توابع Window ممنوع است 4.تنها عبارت جدولی قابل بکارگیری derived table میباشد.

جدول داریم شامل دو ستون با نام های nbr و val که اولی برای نگهداری مقداری متوالی و دیگری مقدار مرتبط به سطر بکار می روند.

CREATE TABLE Nums
(
nbr INTEGER NOT NULL PRIMARY KEY,
 val INTEGER NOT NULL);

INSERT INTO Nums (nbr, val)
VALUES
(1, 0), (5, 7), (9, 4

نتیجه مورد نظر همانیست که در مقدمه مطرح شد.

راه حل ها

جواب اول:

توسط subquery های وابسته در ماده select مقادیر بعدی و قبلی را بدست می آوریم یعنی:

 

SELECT (
         SELECT MAX(nbr)
           FROM Nums
          WHERE nbr < A.nbr
       ) AS pre_nbr,
       (
         SELECT val
           FROM Nums
          WHERE nbr = ( 
                        SELECT MAX(nbr)
                          FROM Nums
                         WHERE nbr < A.nbr
                      )
       ),
       nbr,
       val,
       (
          SELECT MIN(nbr)
            FROM Nums
           WHERE nbr > A.nbr
       ) AS nxt_nbr,
       (
          SELECT val
            FROM Nums
           WHERE nbr = (
                         SELECT MIN(nbr)
                           FROM Nums
                          WHERE nbr > A.nbr
                       )
       ) AS nxt_val
  FROM Nums AS A;

 

این روش مشکل عملکردی جدی دارد به دلیل اینکه subquery های زیادی در ماده select شرکت کرده اند.

جواب دوم:

برای اینکه از تکرار subquery هایی که مقادیر pre_nbr و nxt_nbr را تولید می کنند جلوگیری کنیم ابتدا در یک جدول مشتق شده این دو عبارت را بدست آورده سپس در از آن در subquery هایی که توسط انها val را محاسبه می کنیم استفاده می کنیم به این شکل:

SELECT pre_nbr,
       (
         SELECT val
           FROM Nums
          WHERE nbr = pre_nbr
       ),
       nbr,
       val,
       nxt_nbr,
       (
          SELECT val
            FROM Nums
           WHERE nbr = nxt_nbr
       ) AS nxt_val
  FROM (
         SELECT (
                   SELECT MAX(nbr)
                     FROM Nums
                    WHERE nbr < A.nbr
                ) AS pre_nbr,
                nbr,
                val,
                (
                   SELECT MIN(nbr)
                     FROM Nums
                    WHERE nbr > A.nbr
                ) AS nxt_nbr
           FROM Nums AS A
        ) AS D;

این روش هم نتوسنته هنوز از بکارگیری subquery در ماده select خودداری کند.

جواب سوم:
برای رفع کردن نیاز به
subquery هایی که مقادیر ستون val را بدست می آورند از JOIN به شکل زیر استفاده کرد:

SELECT pre_nbr,
       A.val AS pre_val,
       D.nbr,
       D.val,
       nxt_nbr,
       B.val AS nxt_val
  FROM (
         SELECT (
                   SELECT MAX(nbr)
                     FROM Nums
                    WHERE nbr < A.nbr
                ) AS pre_nbr,
                nbr,
                val,
                (
                   SELECT MIN(nbr)
                     FROM Nums
                    WHERE nbr > A.nbr
                ) AS nxt_nbr
           FROM Nums AS A
        ) AS D
 LEFT JOIN Nums AS A
   ON D.pre_nbr = A.nbr
 LEFT JOIN Nums AS B
   ON D.nxt_nbr = B.nbr;

جواب چهارم:

یک روش آن است که جدول را با خودش به شرط اینکه nbr جدول فعلی برابر باشد با nbr-1 جدول بعدی برای دستری به سطرهای بعد و... .
از آنجایی که هدف خلق راه حلی است که
portable بوده و حق استفاده از CTE، View و Ranking را نداریم ناچارا کوئری به این شکل در می آید:

SELECT A.nbr, A.val,
       B.nbr, B.val,
       C.nbr, C.val
  FROM (
          SELECT *,
                 (SELECT COUNT(*)
                    FROM Nums
                   WHERE nbr <= A.nbr) AS rnk
            FROM Nums AS A
       ) AS A
  RIGHT JOIN 
       (
          SELECT *,
                 (SELECT COUNT(*)
                    FROM Nums
                   WHERE nbr <= A.nbr) AS rnk
            FROM Nums AS A
       ) AS B ON B.rnk = A.rnk + 1
  LEFT JOIN
       (
          SELECT *,
                 (SELECT COUNT(*)
                    FROM Nums
                   WHERE nbr <= A.nbr) AS rnk
            FROM Nums AS A
       ) AS C ON B.rnk = C.rnk - 1;      

جواب پنجم:

در نهایت بشکل هوشمندانه ای مساله حل شد:

SELECT T2.*, T1.*, T3.*
  FROM Nums AS T1
       LEFT JOIN Nums AS T2
         ON T2.nbr = (SELECT MAX(nbr)
                        FROM Nums
                       WHERE nbr < T1.nbr)
       LEFT JOIN Nums AS T3
         ON T3.nbr = (SELECT MIN(nbr)
                        FROM Nums
                       WHERE nbr > T1.nbr);