SQL帮助：Select语句串联一对多关系 [英] SQL Help: Select statement Concatenate a One to Many relationship

问题描述

例如，我有两个表。第一张表是学生，第二张表是学生所修的课程。如何使用select语句，以便可以看到两列的学生和课程，以便课程之间用逗号分隔。

For example I have two tables. The first table is student while the second table are the courses that the a student is taking. How can I use a select statement so that I can see two columns student and courses so that the courses are separated by commas.

谢谢。

推荐答案

假定您正在使用SQL Server 2005：

Assuming you're using SQL Server 2005:

这应该做您想做的事-显然可以根据需要替换字段：

This should do what you're after - obviously replace fields as you need:

出于演示目的，请考虑以下两个表结构：

For demo purposes, consider the following two table structures:

Students(
  STU_PKEY Int Identity(1,1) Constraint PK_Students_StuPKey Primary Key,
  STU_NAME nvarchar(64)
)

Courses(
  CRS_PKEY Int Identity(1, 1) Constraint PK_Courses_CrsPKey Primary Key,
  STU_KEY Int Constraint FK_Students_StuPKey Foreign Key References Students(STU_PKEY),
  CRS_NAME nvarchar(64)
)

现在此查询应执行您要执行的任务：

Now this query should do the job you're after:

Select  s.STU_PKEY, s.STU_NAME As Student,
        Stuff((
            Select  ',' + c.CRS_NAME
            From    Courses c
            Where   s.STU_PKEY = c.STU_KEY
            For     XML Path('')
        ), 1, 1, '') As Courses 
From    Students s
Group By s.STU_PKEY, s.STU_NAME

比当前方法更简单接受的答案...

Way simpler than the currently accepted answer...

这篇关于SQL帮助：Select语句串联一对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！