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








 
   پاسخ چالش قبلی (حل مسائل مرتبط با یک روش یکسان)
 
   T-SQL Challenges
   ۷۴۰۸
   این مقاله حاوی فایل ضمیمه نمی باشد
   محمد سلیم آبادی
   ۱۳۹۰/۳/۲۷
نسخه قابل چاپ نسخه قابل چاپ

/*Question #1*/
WITH
Cte AS
(

      SELECT DISTINCT sno, pno_list
        FROM SupParts SP
          CROSS APPLY

         
(SELECT ',' + pno
             FROM SupParts
            WHERE sno = SP.sno
            ORDER BY sno
              FOR XML PATH('')) AS D(pno_list)

)

SELECT
A.sno, B.sno
  FROM Cte AS A,
       Cte AS B
 WHERE A.pno_list = B.pno_list

   AND A.sno < B.sno;
 

/*Question #2*/
WITH
Cte AS
(

      SELECT DISTINCT sno, pno_list
        FROM SupParts SP
          CROSS APPLY

         
(SELECT ',' + pno
             FROM SupParts
            WHERE sno = SP.sno
            ORDER BY sno
              FOR XML PATH('')) AS D(pno_list)

)

SELECT
DISTINCT STUFF(B.sno_list, 1, 1, ''),
                STUFF(A.pno_list, 1, 1, '')
  FROM Cte A
    CROSS APPLY

   
(SELECT ',' + sno
       FROM Cte
      WHERE pno_list = A.pno_list
      ORDER BY sno

        FOR XML PATH('')) B(sno_list);
 

/*Question #3*/
SELECT
DISTINCT SP.sno, D.pno_list
  FROM SupParts SP
    CROSS APPLY

   
(SELECT '$' + pno + '~' + CAST(COUNT(*) AS VARCHAR(10))
       FROM SupParts
      WHERE sno = SP.sno
      GROUP BY pno
      ORDER BY pno     
        FOR XML PATH('')) D(pno_list)
    CROSS APPLY

   
(SELECT '$' + pno + '~' + CAST(cnt AS VARCHAR(10))
       FROM Goal
      ORDER BY pno
        FOR XML PATH('')) T(pno_list)

  WHERE D.pno_list = T.pno_list;