我有一个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表设计不正确
如果标题是幻想类型是冒险,幻想,搜索条件是
[冒险]=找到
[幻想]=找到
[冒险,幻想]=找到
[冒险,幻想,行动]=未找到
那么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'
以上是有效的,我想知道是否有性能改进