sql - 如何内连接多列？

Question

我正在尝试编写一个用于关键字搜索的存储过程。我们设置数据库的方式。

有一个 Genres 表，其中包含 流派名称 和 流派 ID，然后有一个 Genrebridge 表其中有 genreID1、GenreID2、GenreID3、GenreID4、GenreID5、SongID、AlbumID 和 ArtistID。

如何内部连接每个 GenreID 字段，以便将 Genre name 链接到 Genre Bridge 表

ALTER PROCEDURE [dbo].[usp_album_search_keyword_AlbumNameANDArtistName]
(
    -- Add the parameters for the stored procedure here
    @albumname  varchar(255),
    @artistname varchar(255)
)
As
Begin
Select 
    Distinct a.AlbumTitle, art.ArtistName, a.AvgRatingNBR, a.OriginalPrice, a.DiscountPrice
FROM 
    Albums a 
    inner join Artists art on a.ArtistID = art.ArtistID
    inner join GenreBridge gb on gb.AlbumID = a.AlbumID
    inner join Genres g on g.GenreID = gb.GenreID1
    inner join genres g on g.GenreID = gb.genreID2
    inner join genres g on g.GenreID = gb.GenreID3
    inner join genres g on g.GenreID = gb.GenreID4
    inner join genres g on g.GenreID = gb.GenreID5

    where a.AlbumTitle like '%' + @albumname + '%' 
    and art.ArtistName like '%' + @artistname + '%'
End

Answer 1

除了使用注释中指出的相同别名之外，表 GenreBridge 未正确规范化。为了规范化表格，最好在表格上有一个 GenreID 列，然后根据需要插入尽可能多的行来对专辑的所有流派进行建模。这也将解除对每张专辑的流派数量的任意限制。

作为旁注，您的原始过程似乎根本不使用 Genre (在选择列表或过滤器中)，因此无需加入它，您就赢了不需要DISTINCT。

正如您所指出的，您现在面临连接问题，具体取决于 AlbumGenre 上存在的有效流派列的数量

假设您自始至终都使用 INT 主键，我会将表 GenreBridge 标准化为专辑和流派之间的多:多链接表，同时将其重命名为 AlbumGenre 更好地反射(reflect)many:many约定，例如:

CREATE TABLE dbo.AlbumGenre
(
   AlbumId INT NOT NULL,
   GenreId INT NOT NULL,

   CONSTRAINT PK_AlbumnGenre PRIMARY KEY(AlbumId, GenreId),
   CONSTRAINT FK_AlbumnGenre_Albumn FOREIGN KEY(AlbumId) REFERENCES Albums(AlbumId),
   CONSTRAINT FK_AlbumnGenre_Genre FOREIGN KEY(GenreId) REFERENCES Genres(GenreId)
);

您的程序需要强制执行 1 到 5 个流派的限制(即在插入 AlbumnGenre 记录链接之前，确保该专辑的行数少于 5 行。 )

要列出专辑的所有流派，您只需将 Albumn 加入 AlbumGenre 并在 where 子句中按 AlbumnId 进行过滤(它将返回与类型一样多的行)。

如上所述，不需要 DISTINCT 或加入流派:

ALTER PROCEDURE [dbo].[usp_album_search_keyword_AlbumNameANDArtistName]
(
    -- Add the parameters for the stored procedure here
    @albumname  varchar(255),
    @artistname varchar(255)
)
As
Begin
Select a.AlbumTitle, art.ArtistName, a.AvgRatingNBR, a.OriginalPrice, a.DiscountPrice
FROM 
    Albums a 
    inner join Artists art on a.ArtistID = art.ArtistID
    where a.AlbumTitle like '%' + @albumname + '%' 
    and art.ArtistName like '%' + @artistname + '%'
End