我正在尝试为关键字搜索编写存储过程.我们设置数据库的方式.
有一个Genres表有Genre名称和Genre ID,然后有一个Genrebridge表有genreID1,GenreID2,GenreID3,GenreID4,GenreID5,SongID,AlbumID和ArtistID.
如何内部连接每个GenreID字段,因此类型名称链接到类型桥表
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
解决方法
除了使用注释中提到的相同别名外,表格GenreBridge未正确标准化.要规范化表格,最好在表格上放置一个GenreID列,然后根据需要插入尽可能多的行来为专辑的所有类型进行建模.这也将解除对每张专辑类型数量的任意限制.
作为旁注,您的原始程序似乎根本不使用Genre(在选择列表或过滤器中),因此无需加入它,您将不需要DISTINCT.
正如您所注意到的,您现在面临着连接问题,具体取决于AlbumGenre上存在的有效流派列数
假设你在整个使用INT主键,我会正常化表GenreBridge是一个多方面的：专辑和流派之间的许多链接表,并在同一时间将其重命名AlbumGenre以更好地反映很多：很多约定,例如：
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,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
