时间戳

首页 > 解决方案 > 连接组中的某些行？

sql - 连接组中的某些行？

问题描述

假设我有以下表格：

vs_tbl电影

MOVIEID   MOVIENAME  
11        Star Wars

vs_tblGenreBridge

MOVIEID   GENREID
11        878
11        28
11        12

vs_tblGenres

GENREID   GENRETITLE
28        Action    
12        Adventure
878       Science Fiction

vs_tblActors

ACTORID   STAGELNAME    STAGEFNAME
1         Lucas         George

vs_tblCastMembers

CASTMEMBERROLEID   MOVIEID  ACTORID
351                11       1
352                11       1
353                11       1

vs_tblCastMemberRoles

CASTMEMBERROLEID   CASTMEMBERROLETITLE   CASTMEMBERROLEDESC
351                Directing             Director
352                Production            Executive Producer
353                Writing               Writer

我想显示给定演员的所有角色，结果集采用以下格式：

GENRETITLE  MOVIENAME   ACTORID STAGELNAME  STAGEFNAME  CASTMEMBERROLEID    CASTMEMBERROLEDESC

为此，我编写了以下查询（获取 ActorID = 1 的角色，即 George Lucas）：

SELECT vs_tblGenres.GenreTitle,
       vs_tblMovies.MovieName,
       vs_tblActors.ActorID, 
       vs_tblActors.StageLName, 
       vs_tblActors.StageFName,
       vs_tblCastMembers.CastMemberRoleID,
       vs_tblCastMemberRoles.CastMemberRoleDesc

FROM vs_tblCastMembers

  INNER JOIN vs_tblActors ON vs_tblCastMembers.ActorID = vs_tblActors.ActorID
  INNER JOIN vs_tblMovies ON vs_tblCastMembers.MovieID = vs_tblMovies.MovieID
  INNER JOIN vs_tblGenreBridge ON vs_tblMovies.MovieID = vs_tblGenreBridge.MovieID
  INNER JOIN vs_tblGenres ON vs_tblGenreBridge.GenreID = vs_tblGenres.GenreID
  INNER JOIN vs_tblCastMemberRoles ON vs_tblCastMembers.CastMemberRoleID = vs_tblCastMemberRoles.CastMemberRoleID

WHERE vs_tblActors.ActorID = 1

GROUP BY vs_tblGenres.GenreTitle, 
         vs_tblMovies.MovieName, 
         vs_tblActors.ActorID, 
         vs_tblActors.StageLName, 
         vs_tblActors.StageFName, 
         vs_tblCastMembers.CastMemberRoleID,
         vs_tblCastMemberRoles.CastMemberRole Desc

哪些输出：

| GENRETITLE      | MOVIENAME | ACTORID | STAGELNAME | STAGEFNAME | CASTMEMBERROLEID | CASTMEMBERROLEDESC |
|-----------------|-----------|---------|------------|------------|------------------|--------------------|
| Science Fiction | Star Wars | 1       | Lucas      | George     | 352              | Executive Producer |
| Adventure       | Star Wars | 1       | Lucas      | George     | 352              | Executive Producer |
| Action          | Star Wars | 1       | Lucas      | George     | 351              | Director           |
| Adventure       | Star Wars | 1       | Lucas      | George     | 351              | Director           |
| Science Fiction | Star Wars | 1       | Lucas      | George     | 353              | Writer             |
| Science Fiction | Star Wars | 1       | Lucas      | George     | 351              | Director           |
| Action          | Star Wars | 1       | Lucas      | George     | 353              | Writer             |
| Adventure       | Star Wars | 1       | Lucas      | George     | 353              | Writer             |
| Action          | Star Wars | 1       | Lucas      | George     | 352              | Executive Producer |

我想要做的是合并只有 GenreTitle 不同的情况，因此它不会为每个流派多次列出角色。理想的输出是这样的：

| GENRETITLE                         | MOVIENAME | ACTORID | STAGELNAME | STAGEFNAME | CASTMEMBERROLEID | CASTMEMBERROLEDESC |
|------------------------------------|-----------|---------|------------|------------|------------------|--------------------|
| Action, Adventure, Science Fiction | Star Wars | 1       | Lucas      | George     | 352              | Executive Producer |
| Action, Adventure, Science Fiction | Star Wars | 1       | Lucas      | George     | 351              | Director           |
| Action, Adventure, Science Fiction | Star Wars | 1       | Lucas      | George     | 353              | Writer             |

在 Oracle 12c 中执行此操作的最简单方法是什么？

标签： sqloraclegroup-by

解决方案

你快到了。您所要做的就是拉出OracleLISTAGG聚合函数以将所有GenreTitles 聚合在一起，相对于其他GROUP BY字段。因此，您需要GenreTitle从GROUP BY子句中删除。

从文档：

对于指定的度量，LISTAGG 对 ORDER BY 子句中指定的每个组中的数据进行排序，然后连接度量列的值。

更新查询：

SELECT LISTAGG(vs_tblGenres.GenreTitle, ', ') WITHIN GROUP (ORDER BY vs_tblGenres.GenreTitle) AS GenreTitle,
       vs_tblMovies.MovieName,
       vs_tblActors.ActorID, 
       vs_tblActors.StageLName, 
       vs_tblActors.StageFName,
       vs_tblCastMembers.CastMemberRoleID,
       vs_tblCastMemberRoles.CastMemberRoleDesc

FROM vs_tblCastMembers

  INNER JOIN vs_tblActors ON vs_tblCastMembers.ActorID = vs_tblActors.ActorID
  INNER JOIN vs_tblMovies ON vs_tblCastMembers.MovieID = vs_tblMovies.MovieID
  INNER JOIN vs_tblGenreBridge ON vs_tblMovies.MovieID = vs_tblGenreBridge.MovieID
  INNER JOIN vs_tblGenres ON vs_tblGenreBridge.GenreID = vs_tblGenres.GenreID
  INNER JOIN vs_tblCastMemberRoles ON vs_tblCastMembers.CastMemberRoleID = vs_tblCastMemberRoles.CastMemberRoleID

WHERE vs_tblActors.ActorID = 1

GROUP BY vs_tblMovies.MovieName, 
         vs_tblActors.ActorID, 
         vs_tblActors.StageLName,
         vs_tblActors.StageFName, 
         vs_tblCastMembers.CastMemberRoleID,
         vs_tblCastMemberRoles.CastMemberRole Desc

推荐阅读