具有多个联接的资料​​查询 [英] Stuff Query with multiple joins

问题描述

SELECT 
    S.enroll_no, 
    Pm.periodname 
FROM
    studentattendencedetails AS SA 
LEFT JOIN 
    studentattendencemaster AS SM ON SA.attendencemasterid = SM.id 
LEFT JOIN 
    tbl_periodmaster AS Pm ON SA.periodid = Pm.id 
LEFT JOIN 
    students AS S ON SA.studentid = S.id 
WHERE  
    SA.isabsent = 2 
    AND s.enroll_no IN ('YYYYYYYYY','XXXXXXXXXX')
ORDER BY 
    S.enroll_no

输出:

Enroll_No   PeriodName 
-----------------------
YYYYYYYYY   1
YYYYYYYYY   2
XXXXXXXXXX  6
XXXXXXXXXX  1
XXXXXXXXXX  7

我想用逗号分隔的值来显示时段名称，如下所示:

I would like to display the period name with comma-separated values as follows

Enroll_No   PeriodName
------------------------
YYYYYYYYY   1,2
XXXXXXXXXX  1,6,7

我尝试了以下查询

SELECT 
    t.enroll_no, 
    periodList  = STUFF((SELECT ', ' + Pm.periodname 
                         FROM studentattendencedetails AS SA 
                         LEFT JOIN studentattendencemaster AS SM ON SA.attendencemasterid = SM.id 
                         LEFT JOIN tbl_periodmaster AS Pm ON SA.periodid = Pm.id 
                         LEFT JOIN students AS S ON SA.studentid = S.id 
                         WHERE s.enroll_no IN ('YYYYYYYYY','XXXXXXXXXX') 
                           AND SA.isabsent = 2
                         FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
FROM 
    dbo.students AS t 
WHERE   
    t.enroll_no IN ('YYYYYYYYY','XXXXXXXXXX')
ORDER BY 
    t.enroll_no;

输出:

Enroll_No   PeriodName
---------------------------
YYYYYYYYY   6, 1, 2, 1, 7
XXXXXXXXXX  6, 1, 2, 1, 7

推荐答案

您需要这样做:

所以基本上您错过了一个地方，其中t.Enroll_no = s.Enroll_no，然后是 在您自己的查询中进行分组

So basically your missing a where t.Enroll_no = s.Enroll_no and then a group by in your own query

declare @table table (enroll nvarchar(50),periodname int)

insert into @table

values


('YYYYYYYYY' , 1),
('YYYYYYYYY' , 2),
('XXXXXXXXXX', 6),
('XXXXXXXXXX',1 ),
('XXXXXXXXXX',7 )

select enroll, PeriodList = stuff((select N', '+cast(PeriodName as nvarchar(2)) from @table t2 where t.enroll = t2.enroll order by periodname 
FOR XML PATH (N''),TYPE).value(N' .[1]', N'nvarchar(max)'),1,2,N'') from @table t
group by enroll

根据主题所有者的评论更新

您可以创建视图:

Create view dbo.EnrollmentPeriodList as 

SELECT 
    S.enroll_no, 
    Pm.periodname 
FROM
    studentattendencedetails AS SA 
LEFT JOIN 
    studentattendencemaster AS SM ON SA.attendencemasterid = SM.id 
LEFT JOIN 
    tbl_periodmaster AS Pm ON SA.periodid = Pm.id 
LEFT JOIN 
    students AS S ON SA.studentid = S.id 
WHERE  
    SA.isabsent = 2 
    AND s.enroll_no IN ('YYYYYYYYY','XXXXXXXXXX') 

然后是代码

select enroll, PeriodList = stuff((select N', '+cast(PeriodName as nvarchar(2)) from dbo.EnrollmentPeriodList t2 where t.enroll = t2.enroll order by periodname 
FOR XML PATH (N''),TYPE).value(N' .[1]', N'nvarchar(max)'),1,2,N'') from dbo.EnrollmentPeriodList t
group by enroll

这篇关于具有多个联接的资料​​查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！