SQL：如何根据类别进行选择？

有两张桌子，categories and books我想根据给定的类别选择所有书籍。

类别表：

cat_id | book_id
----------------
1      | 1
2      | 1
3      | 1
3      | 2

图书表：

id  | name
----------------
1   | abc
2   | def

我试过了SELECT * FROM categories WHERE cat_id IN(1,3)但随后它会返回至少包含给定类别之一的书籍。我想要的是它只返回包含所有类别的书籍，因此它应该只返回 book_id = 1 的所有（或一个）行，因为它是唯一具有所有给定类别的书籍。

Try:

select book_id
from categories
group by book_id
having sum( ( cat_id in (1,3) )::int ) = 2

或者，如果您打算从支持直接向其传递数组的语言将数组传递给 postgres（如下所示：http://fxjr.blogspot.com/2009/05/npgsql-tips-using-in-queries-with.html http://fxjr.blogspot.com/2009/05/npgsql-tips-using-in-queries-with.html）， 用这个：

select book_id
from categories
group by book_id
having sum( ( cat_id = ANY(ARRAY[1,3]) )::int ) = 2

如果你想获取书名：

select categories.book_id, books.name
from categories
join books on books.id = categories.book_id
group by categories.book_id
    ,books.name
having sum( ( categories.cat_id in (1,3) )::int ) = 2

@Evan Carroll，修改查询：

ANSI SQL方式：

select categories.book_id, books.name
from categories
join books on books.id = categories.book_id
group by categories.book_id
    ,books.name
having count(case when categories.cat_id in (1,3) then 1 end) = 2

无书名：

select book_id
from categories
group by book_id
having count( case when cat_id in (1,3) then 1 end ) = 2

在同一子句中内联条件及其计数值有什么好处（即having）而不是单独将条件放入where子句及其计数having条款？...

select book_id
from categories
where category_id in (1,3)
group by book_id
having count(*) = 2

...如果我们将条件及其计数值内联having条款，我们可以方便地进行查询列出类别为 1 和 3，或类别为 2、3 和 4 的所有书籍。面向未来的 FTW！另外，组合类别的测试及其计数是彼此相邻的，再加上可读性方面的因素。

为了方便这种查询：

select book_id
from categories
group by book_id
having 
    count( case when cat_id in (1,3) then 1 end ) = 2 
    or count( case when cat_id in (2,3,4) then 1 end ) = 3

为了实现性能（有时，同时实现性能和可读性；不要混合得很好），must将having子句的元素测试复制到where子句：

select book_id
from categories
where cat_id in (1,2,3,4)
group by book_id
having 
    count( case when cat_id in (1,3) then 1 end ) = 2 
    or count( case when cat_id in (2,3,4) then 1 end ) = 3

[EDIT]

顺便说一句，这是惯用的 MySQL：

select book_id
from categories
group by book_id
having sum( cat_id in (1,3) ) = 2