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

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

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

نظرات 0

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

StID

StNameSname

1

ALI

2

Mehrdad

3

BITA

4

AZAM

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

StMarkID

StID

CurseName

MarkOfCurse

1

1

Riazi

20

2

1

Fizik

18

3

1

Shimi

16

4

2

Riazi

18

5

2

Fizik

19.5

6

2

Shimi

14.5

7

2

Shimi

20

8

3

Riazi

14

9

3

Fizik

20

10

4

Shimi

20

11

4

Honar

19.4

12

4

Khaiiati

20

 

که در دو جدول فوق فیلد 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

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

 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
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')
GO
INSERT INTO [dbo].[tblStudents] ([StID] ,[StNameSname]) 
VALUES      (2,N'Mehrdad')
GO
INSERT INTO [dbo].[tblStudents] ([StID] ,[StNameSname]) 
VALUES      (3,N'BITA')
GO
INSERT INTO [dbo].[tblStudents] ([StID] ,[StNameSname]) 
VALUES      (4,N'AZAM')
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (1,1,N'Riazi',20)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (2,1,N'Fizik',18)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (3,1,N'Shimi',16)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (4,2,N'Riazi',18)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (5,2,N'Fizik',19.5)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (6,2,N'Shimi',14.5)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (7,2,N'Shimi',20)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (8,3,N'Riazi',14)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (9,3,N'Fizik',20)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (10,4,N'Shimi',20)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (11,4,N'Honar',19.4)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (12,4,N'Khaiiati',20)
GO

 

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
USE DbTest
GO
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
GO

خروجی

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

 

 

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
USE DbTest
GO
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 
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
GO

خروجی

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

  

 

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

 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
USE DbTest
GO
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
GO

 خروجی

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 ورژن اس کیو ال باید 2017 یا بالاتر باشد.
 
 
1
2
3
4
5
6
7
USE DbTest
GO
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
GO
خروجی
 

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 حداکثر

اطلاعات تماس

  • آدرس:اصفهان-خیابان ام کلثوم غربی - بعد خیابان تخم چی - بیست متر بعد از پیتزا ننه شب - کوچه تعمیر گاه سمار زغالی - پلاک 354 - درب مشکی - طبقه هفتم
  • آدرس ایمیل:najafzade@gmail.com
  • وب سایت:http://www.a00b.com/
  • تلفن ثابت:(+98)9131253620
  • تلفن همراه:09131253620