SQL -使用多个类别选择不同的行

Question

我有三个数据库表：

故事

• Id
• Title
• Content

类别

• Title

• Id

StoryCategory

• StoryId
• CategoryId

注意:故事和类别有多到多的关系(一个故事可以有多个类别，一个类别可以有多个故事)，所以我创建了StoryCategory。另外，一个故事也不可能有分类。

我还有一个网页(我在用剃须刀)：

<form action="" method="get">
    <label for="keyword">Keyword:</label>
    <input name="keyword" type="text" value="@Page.Keyword" />
    <label for="category">Category:</label>
    <select name="category">
        <option value="">All</option>
    @foreach(var category in Page.Categories)
    {
        <option value=@category.Id @(category.Id == Page.Category? "selected=selected" : "")>@category.Title</option>
    }
    </select>
    <input type="submit" value="Search" />
</form> 

为了简单起见，此页面允许用户输入关键字来搜索特定的故事，还允许用户选择故事所属的类别。

我找不到一种方法来找到与特定类别相关的故事。下面是我的第一个代码(关键字忽略了关注类别)：

select s.title --columns will be added here such as name of category/ies, author etc.
from story as s
left join --left join so the uncategorized stories will not be excluded
storyCategory as sc
on s.id = sc.storyId
where sc.categoryId = @selectedCategory --this line is removed when there is no category selected

样本数据：

• 故事

id标题内容1火影忍者2漂白剂

• 类别

id标题1幻想2动作3 drama

• StoryCategory

storyId categoryId 1 1 1 2 2 1

问题是，如果没有选定的类别，如果故事有多个类别，它也会多次出现：

naruto (fantasy)
naruto (action)
bleach (fantasy)

我其实知道发生了什么，但我想不出解决问题的最佳办法。

Answer 1

使用DISTINCT关键字，左联接对WHERE条件是无用的

select DISTINCT
  s.title --columns will be added here such as name of category/ies, author etc.
from story as s
join storyCategory as sc--left join so the uncategorized stories will not be excluded
on s.id = sc.storyId
where sc.categoryId = @selectedCategory --this line is removed when there is no category selected

如果您需要附加到每个故事的类别列表-请查看并检查下一个查询。对sql server有效：

DECLARE @Stories TABLE(Id INT IDENTITY PRIMARY KEY, NAME NVARCHAR(100) NOT NULL) 
DECLARE @Categories TABLE(Id INT IDENTITY PRIMARY KEY, NAME NVARCHAR(100) NOT NULL) 
DECLARE @Fork TABLE(StoryId INT NOT NULL, CategoryId INT NOT NULL, PRIMARY KEY(StoryId, CategoryId)) 

INSERT @Stories 
VALUES ('Story1'), ('Story2'), ('Story3') 

INSERT @Categories 
VALUES ('Category1'), ('Category2'), ('Category3') 

INSERT @Fork 
VALUES(1,1), (1,2), (3,3), (2,3) 

DECLARE @selectedCategory INT = 3 

select 
s.NAME, 
( 
SELECT c.Name + ',' 
FROM @Categories c 
JOIN @Fork f ON f.CategoryId = c.Id AND f.StoryId = s.Id 
ORDER BY c.Name 
FOR XML PATH('') 
) Categories 
from @stories s

Answer 2

如果没有将其限制在单个类别中，请尝试如下所示：

select s.title,count(*) as NumCategories 
from story as s
left join storyCategory as sc on s.id = sc.storyId
group by s.title

您还可以使用类似的方法找到一些类别，它显示了故事中的类别数以及第一个和最后一个类别(根据标题字符)。

select s.title,count(*) as NumCategories,min(sc.title),max(sc.title)
from story as s
left join storyCategory as sc on s.id = sc.storyId
group by s.title