我有一个MySQL表
书桌

+--------+-------------+-----+
| bookno | bookname    | ... |
+--------+-------------+-----+
| 1      | FINALFANTASY| ... |
+--------+-------------+-----+

可授权
+--------+-------------+-----+
| bookno | Authorname  | ... |
+--------+-------------+-----+
| 1      | SQUARE      | ... |
+--------+-------------+-----+
| 1      | ENIX        | ... |
+--------+-------------+-----+

所以我想做一个搜索条件,得到与结果匹配的书。
我试着
select b.bookname,a.authorname from booktable as b
left outer join authortable a on b.bookno = a.bookno
where a.authorname = "square" and a.authorname = "enix"

它只适用于一个where条件,但当我尝试使用两个authorname时,没有找到结果。我该怎么办?
(此查询使用“或”但不使用“和”,但我真的需要与搜索条件匹配的值,或者如果有某些搜索条件不匹配但不为空,则不应显示该值(在这种情况下,此查询不工作)

最佳答案

Tim Biegeleisen的答案很好,但是如果您需要完全匹配,下面的最后一个SQL是正确的:

SELECT * FROM book;
SELECT * FROM author;

/* this SQL will return book's author name more than 2 also true */
SELECT b.bookname, a.authorname
FROM book AS b
JOIN author AS a ON b.bookno = a.bookno
JOIN (
  SELECT bookno FROM author
  WHERE authorname in ('SQUARE', 'ENIX')
  GROUP BY 1
  HAVING count(*) = 2
) AS a2 ON b.bookno = a2.bookno;

/* this sQL will return only 2 and all matched authors: */
SELECT b.bookname, a.authorname
FROM book AS b
JOIN author AS a ON b.bookno = a.bookno
JOIN (
  SELECT bookno FROM author
  WHERE authorname in ('SQUARE', 'ENIX')
  GROUP BY 1
  HAVING count(*) = 2
) AS a2 ON b.bookno = a2.bookno
JOIN (
  SELECT bookno FROM author
  GROUP BY 1
  HAVING count(distinct authorname) = 2
) AS a3 ON b.bookno = a3.bookno

PS1-不需要左连接
PS2-不需要计数不同-除非您的author表设计不正确
mysql - 如何从左侧外部联接中查找具有AND条件的完全匹配项-LMLPHP
如果标题是幻想类型是冒险,幻想,搜索条件是
[冒险]=找到
[幻想]=找到
[冒险,幻想]=找到
[冒险,幻想,行动]=未找到
那么SQL将是:
SELECT b.bookname, a.authorname
FROM book AS b
JOIN author AS a ON b.bookno = a.bookno
JOIN author AS a1 ON b.bookno = a1.bookno AND a1.authorname = 'SQUARE'
JOIN author AS a2 ON b.bookno = a2.bookno AND a2.authorname = 'ENIX'

以上是有效的,我想知道是否有性能改进

10-05 20:34
查看更多