我需要帮助编写一个复杂的（对我来说）SELECT语句

Ok. I'm new to MySQL have a SELECT statement I can't wrap my head around.

好。我是MySQL新手，有一个SELECT语句，我无法理解。

I have a table of books with a primary key of ASIN (10-digit ISBN), and I have a table of tags with a auto-incrementing primary key. Then I have a junction table to show which ASINs have which tags associated with them.

我有一张带有ASIN主键（10位ISBN）的书籍表，我有一张带有自动递增主键的标签表。然后我有一个联结表来显示哪些ASIN具有与之关联的标签。

I can use this SELECT statement to give any books using a single tag:

我可以使用此SELECT语句为任何书籍使用单个标记：

SELECT b.asin, b.title, b.img_thumb, b.filename FROM books AS b
INNER JOIN tag_junction AS tj USING (asin)
WHERE tj.tag_id=14 
ORDER BY title

But what I'd like to do is write a query that will give any books that contain multiple tags, both by AND and by OR. I've tried just writing it as follows below but that doesn't work.

但我想做的是编写一个查询，通过AND和OR，为任何包含多个标签的书籍提供。我试过写下面的内容如下，但这不起作用。

SELECT b.asin, b.title, b.img_thumb, b.filename FROM books AS b
INNER JOIN tag_junction AS tj USING (asin)
WHERE tj.tag_id=14 AND tj.tag_id=12 
ORDER BY title

So basically I'd like the query to return any books that use both tag_id 14 and tag_id 12. I'd also like to be able to query books that use either 14 OR 12.

所以基本上我希望查询返回任何同时使用tag_id 14和tag_id 12的书籍。我也希望能够查询使用14或12的书籍。

What am I missing?

我错过了什么？

6 个解决方案

#1

Use:

使用：

SELECT b.asin, 
       b.title, 
       b.img_thumb, 
       b.filename 
  FROM BOOKS b
  JOIN TAG_JUNCTION tj ON tj.asin = b.asin
 WHERE tj.tag_id IN (12, 14)

This will return BOOKS records that are associated to:

这将返回与以下内容关联的BOOKS记录：

tag_id 12
tag_id 12
tag_id 14
tag_id 14
combination of tag_id 12 and tag_id 14
tag_id 12和tag_id 14的组合

As long as one of them is satisfied, the associated BOOKS record will be returned.

只要其中一个满意，就会返回相关的BOOKS记录。

To return where all the tags match, you have to add GROUP BY and HAVING clauses:

要返回所有标记匹配的位置，您必须添加GROUP BY和HAVING子句：

SELECT b.asin, 
       b.title, 
       b.img_thumb, 
       b.filename 
  FROM BOOKS b
  JOIN TAG_JUNCTION tj ON tj.asin = b.asin
 WHERE tj.tag_id IN (12, 14)
GROUP BY b.asin, b.title, b.img_thumb, b.filename 
  HAVING COUNT(DISTINCT tj.tag_id) = 2

The number for the count in the HAVING clause MUST MATCH the number of tags specified in the IN clause.

HAVING子句中的计数数必须与IN子句中指定的标记数相匹配。

#2

Just doing this should give you all three scenarios:

这样做应该会给你三个场景：

SELECT b.asin, b.title, b.img_thumb, b.filename FROM books AS b
INNER JOIN tag_junction AS tj USING (asin)
WHERE (tj.tag_id=14 OR tj.tag_id=12) ORDER BY title

Books that have 14 AND 12 also match the criteria of a book that has 14 OR 12 :o)

具有14 AND 12的书籍也符合具有14 OR 12的书籍的标准：o）

#3

To sum up what others have said, OR is inclusive, unless specified otherwise. You'll find this to be generally true in Computerland in programming and scripting languages, in bitwise operations at the CPU level, and in gate logic at the hardware level. It's also this way in logic (philosophy).

除非另有说明，否则总结其他人所说的内容，或者是包容性的。您会发现，在Computerland中，编程和脚本语言，CPU级别的按位操作以及硬件级别的门逻辑都是如此。逻辑（哲学）也是这样。

To become a whiz at ANDs and ORs and other logical things, check out truth tables: http://en.wikipedia.org/wiki/Truth_table.

要成为ANDs和OR以及其他逻辑事物的高手，请查看真值表：http：//en.wikipedia.org/wiki/Truth_table。

#4

This should work for your AND case. Note that the hard-coded 2 must match the number of IDs in the IN clause:

这应该适用于您的AND案例。请注意，硬编码2必须与IN子句中的ID数相匹配：

select *
from books 
where asin in (
    SELECT b.asin
    FROM books b
    INNER JOIN tag_junction tj on b.asin = tj.asin
    WHERE tj.tag_id in (12, 14)
    group by b.asin
    having count(distinct tj.tag_id) = 2
)

For your OR case you can do this:

对于您的OR案例，您可以这样做：

SELECT b.asin, b.title, b.img_thumb, b.filename
FROM books AS b
INNER JOIN tag_junction tj on b.asin = tj.asin
WHERE tj.tag_id in (12, 14)

#5

To match both tags, simply use modified version of queries already posted. Using same query you do for filtering, additionaly check if tags count for given book equals to 2 - this assures, both were matched.

要匹配这两个标记，只需使用已发布的已修改版本的查询。使用相同的查询进行过滤，另外检查给定书籍的标签是否等于2 - 这确保两者都匹配。

SELECT b.asin, b.title, b.img_thumb, b.filename 
FROM books b
JOIN tag_junction tj ON tj.asin = b.asin
WHERE tj.tag_id IN (12, 14)
AND 
( 
  SELECT COUNT(*) 
  FROM books ib 
  JOIN tag_junction itj ON (itj.asin = ib.asin)
  WHERE itj.tag_id IN (12, 14) AND ib.asin = b.asin
) = 2
GROUP BY b.asin
ORDER BY title

#6

Use distinct:

使用不同：

SELECT distinct b.asin, b.title, b.img_thumb, b.filename  
FROM books b 
JOIN tag_junction tj ON tj.asin = b.asin 
WHERE tj.tag_id IN (12, 14) 
AND  
(  
  SELECT COUNT(*)  
  FROM books ib  
  JOIN tag_junction itj ON (itj.asin = ib.asin) 
  WHERE itj.tag_id IN (12, 14) AND ib.asin = b.asin 
) = 2 
GROUP BY b.asin 
ORDER BY title

#1