نمایش رکوردهای متناظر یا فرزند مربوط به والد به صورت رشته متصل

نمایش همه فرزندان والد در یک خط در SQL Server

توسط admin | گروه sql | 1398/09/08

نظرات 0

گاهی اوقات لازم است که رکوردهای جدول فرزند مرتبط با یک جدول والد به صورت یک رشته واحد نمایش داده شود. مثلا تمامی درسهای اخذ شده یک دانش آموز به همراه نمره آنها در یک خط یا یک فیلد نمایش داده شود. لطفا به لیست زیر دقت فرمائید تا بهتر متوجه شوید:

StID        StNameSname

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

1           ALI

2           Mehrdad

3           BITA

4           AZAM

و جدول فرزند به شکل زیر است 

StMarkID    StID        MarkOfCurse            CurseName

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

1           1           20                     Riazi

2           1           18                     Fizik

3           1           16                     Shimi

4           2           18                     Riazi

5           2           19.5                   Fizik

6           2           14.5                   Shimi

7           2           20                     Shimi

8           3           14                     Riazi

9           3           20                     Fizik

10          4           20                     Shimi

11          4           19.4                   Honar

12          4           20                     Khaiiati

 

که در دو جدول فوق فیلد STID فیلد مشترک بین دو جدول می باشد. خروجی که مد نظر است به شکل زیر میباشد:

StID        StNameSname       Children

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

1           ALI               Riazi : 20,Fizik : 18,Shimi : 16

2           Mehrdad           Riazi : 18,Fizik : 19.5,Shimi : 14.5,Shimi : 20

3           BITA              Riazi : 14,Fizik : 20

4           AZAM              Shimi : 20,Honar : 19.4,Khaiiati : 20

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

در ایتدا دیتابیس و جدولها را ایجاد می نماییم

 

USE [master]

GO


-- SQL 2016 بررسی وجود پایگاه داده و کد حذف آن برای نسخهای قبل از 

IF DB_ID('DbTest')>0

BEGIN

ALTER DATABASE [DbTest] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

DROP DATABASE [DbTest]

END

GO


-- و بالاتر SQL 2016 بررسی جهت وجود دیتابیس و حذف آن در

-- DROP DATABASE IF EXISTS sampleDB

-- GO


-- ایجاد دیتابیس

create Database DbTest

go

use [DbTest]

go

-- ایجاد جدول

CREATE TABLE tblStudents

(

StID   INT PRIMARY KEY  

,StNameSname     NVARCHAR(100)

)

go

CREATE TABLE tblStudentsMarks

(

StMarkID   INT PRIMARY KEY  

,StID INT

,CurseName     NVARCHAR(100)

,MarkOfCurse float

)

go

-- درج چند رکورد در جدول

INSERT INTO [dbo].[tblStudents] ([StID] ,[StNameSname]) 

VALUES

           (1,N'ALI'),(2,N'Mehrdad'),(3,N'BITA'),(4,N'AZAM')

GO

INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])

     VALUES

           (1,1,N'Riazi',20),(2,1,N'Fizik',18),(3,1,N'Shimi',16),

   (4,2,N'Riazi',18),(5,2,N'Fizik',19.5),(6,2,N'Shimi',14.5),(7,2,N'Shimi',20),

   (8,3,N'Riazi',14),(9,3,N'Fizik',20),

   (10,4,N'Shimi',20),(11,4,N'Honar',19.4),(12,4,N'Khaiiati',20)

GO

 

 

روش اول: استفاده از دستور FOR XML PATH و STUFF 

 

SELECT DISTINCT ST2.StID, ST2.StNameSname, 

STUFF((SELECT        ', ' + GD1.CurseName + N' : ' + convert(nvarchar(20) , GD1.MarkOfCurse) AS [text()]

                                 FROM    (select  s.StID , s.StNameSname, m.StMarkID , m.CurseName, m.MarkOfCurse 

from tblStudents s

inner join tblStudentsMarks m on s.StID = m.StID) GD1

                                 WHERE        GD1.StID = ST2.StID 

                                 ORDER BY GD1.StID FOR XML PATH('')), 1 ,1, '') [StudentsMarks]

FROM    (select  s.StID , s.StNameSname, m.StMarkID , m.CurseName, m.MarkOfCurse 

from tblStudents s

inner join tblStudentsMarks m on s.StID = m.StID) AS  ST2

روش دوم: استفاده از دستور FOR XML PATH و SUBSTRING 

 

SELECT DISTINCT ST2.StID, ST2.StNameSname, 

SUBSTRING ((SELECT        ', ' + GD1.CurseName + N' : ' + convert(nvarchar(20) , GD1.MarkOfCurse) AS [text()]

                                 FROM    (select  s.StID , s.StNameSname, m.StMarkID , m.CurseName, m.MarkOfCurse 

from tblStudents s

inner join tblStudentsMarks m on s.StID = m.StID) GD1

                                 WHERE        GD1.StID = ST2.StID --AND GD1.PFactorDetailID = ST2.PFactorDetailID

                                 ORDER BY GD1.StID FOR XML PATH('')), 2, 1000) [StudentsMarks]

FROM    (select  s.StID , s.StNameSname, m.StMarkID , m.CurseName, m.MarkOfCurse 

from tblStudents s

inner join tblStudentsMarks m on s.StID = m.StID) AS  ST2


 

روش سوم: استفاده از دستور FOR XML PATH و STUFF و WITH CTE

with StTemp (StudID , StName , StCurseName , StMark)

as

(

select  s.StID , s.StNameSname, m.CurseName, m.MarkOfCurse 

from tblStudents s

inner join tblStudentsMarks m on s.StID = m.StID

)

SELECT DISTINCT StudID, StName,

    REPLACE(

        STUFF(

           (SELECT

                ',' + t2.StCurseName + N' : ' + convert(nvarchar(20) , StMark)

                FROM StTemp  t2

                WHERE StTemp.StudID=t2.StudID

                ORDER BY t2.StudID

                FOR XML PATH(''), TYPE

           ).value('.','varchar(max)')

           ,1,2, ''

        ), 

    ',,', ',') AS ChildValues

FROM StTemp

روش چهارم: استفاده از متد STRING_AGG ورژن اس کیو ال باید 2017 یا بالاتر باشد.
 
 
select s.StID , s.StNameSname, STRING_AGG(m.CurseName + N' : ' + convert(nvarchar(20) , m.MarkOfCurse) , ',') as Children
from tblStudents s
inner join tblStudentsMarks m on s.StID = m.StID
group by s.StID , s.StNameSname
خروجی
 

StID

StNameSname

StudentsMarks

1

ALI

 Riazi : 20, Fizik : 18, Shimi : 16

2

Mehrdad

 Riazi : 18, Fizik : 19.5, Shimi : 14.5, Shimi : 20

3

BITA

 Riazi : 14, Fizik : 20

4

AZAM

 Shimi : 20, Honar : 19.4, Khaiiati : 20

 

پرفورمنس:
استفاده از دستور String_agg دارای بهترین پرفورمنس و استفاده از With در مثال فوق دارای بد ترین پرفورمنس می باشد.
 

 

 

 

0 نظر

نظر محترم شما در مورد مقاله های وب سایت برنامه نویسی و پایگاه داده

نظرات محترم شما در خدمات رسانی بهتر ما را یاری می نمایند. لطفا اگر مایل بودید یک نظر ما را مهمان فرمائید. آدرس ایمیل و وب سایت شما نمایش داده نخواهد شد.

حرف 500 حداکثر