SQL查询按外键分组如何

Question

我的数据库中有两个这样的表：

Genre: Horror, Romance, Commedy, 
Film: film1, film2, film3,film4,film5,film6

在电影表中，我有一个外键连接到流派表的主键。这是我的问题：

我想获取所有电影并按流派对它们进行分组并显示在数据列表中，查询应该是什么？

我想要的结果是这样的：

Commedy: film5, film6
Horror: film1, film 2
Romance: film3, film4

现在我使用此查询：

Select genre,film
from Genre
inner join film on film.genrID=genre.genreID
order by genre,film ASC

我得到了这个结果：

Horror: film1
Horror: film2
Romance: film3...
...

我想按类型对电影进行分组。

Answer 1

SQL Server 2008 ：

SELECT um.genre,
                 CONVERT(varchar(1000),  stuff(  
                          (SELECT   
                              ', ' +  film 
                              FROM tbl_Film p2  
                              WHERE p2.genreid=um.id  
                              ORDER BY film  
                              FOR XML PATH('')  
                           ) 
                           ,1,2, ''  
                       ))AS film
               FROM tbl_Film p1   
               inner join tbl_Genre um on um.id=p1.genreid   
              GROUP BY    
              um.id,um.genre

Answer 2

MySQL：

Select genre, group_concat(film) films
    from Genre
    inner join film on film.genrID=genre.genreID
    group by Genre.genreID, genre
    order by genre,films ASC

神谕：

Select genre, wm_concat(film) films
    from Genre
    inner join film on film.genrID=genre.genreID
    group by genrID, genre
    order by genre,films ASC

我没有MS SQL Server进行测试，但这必须有效：

select a.genre, SUBSTRING(d.Films,1, LEN(d.Films) - 1) FilmList
from 
  (
    SELECT genrID,genre
    FROM Genre
  ) a
CROSS APPLY
  (
    SELECT [film] + ', ' 
    FROM (
      Select genrID,film
      from Genre
      inner join film on film.genrID=genre.genreID
    ) AS B 
    WHERE A.genrID = B.genrID 
    FOR XML PATH('')
  ) D (Films)