SQL：如何根据类别进行选择？ [英] SQL: How do I make a selection based on categories?

问题描述

有两个表，分别是类别和 books ，我想根据给定选择所有书籍

There are two tables, categories and books and I'd like to select all books based on the given categories.

类别表：

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

Books table：

Books table:

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

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

I've tried SELECT * FROM categories WHERE cat_id IN(1,3) but then it returns books containing at least one of the given categories. What I'd like is that it only returns the books containing ALL the categories, so it should only return all (or one) rows where book_id = 1 since it's the only book with all the given categories.

推荐答案

尝试：

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 ），请使用以下方法：

Or if you intend to pass an array to postgres from language that supports passing array directly to it(like this: http://fxjr.blogspot.com/2009/05/npgsql-tips-using-in-queries-with.html), use this:

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，修改查询：

@Evan Carroll, amending the query:

ANSI SQL方式：

ANSI SQL way:

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

---

内联条件和在相同子句中的计数值（即具有），而不是将条件分别放在 where 子句中，并将其计数放在 having 子句？...

---

What's the advantage of inlining the condition and its count value in the same clause(i.e. having) as opposed to separately putting the condition in where clause and its count in having clause?...

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！加上组合类别的测试及其数量紧挨着，加上可读性方面的因素。

...If we inline both the condition and its count value in having clause, we can facilitate an inquiry of let's say list all books with categories of 1 and 3, or with categories of 2 and 3 and 4. Future-proofing FTW! Plus the testing of combined categories and its count are next to each other, plus factor in terms of readability.

为方便这种查询：

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

要获得性能（有时兼具性能和可读性；不能很好地混合使用），必须将具有having子句的元素的测试重复到where子句：

To achieve performance(sometimes, achieving both performance and readability; don't mix well), must duplicate the testing of elements of having clause to where clause:

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

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

BTW, here's the idiomatic MySQL:

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

这篇关于SQL：如何根据类别进行选择？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！