mysql - 或在 MySQL 中加入

Question

我想知道将 OR 放在 JOIN 中有什么问题，因为它似乎没有响应 2 个条件:(k0. keyword = 'Nombre' AND k0.value LIKE 'Cliente3') OR (k0.keyword = 'Nombre' AND k0.value = 'Cliente4'))。它总是返回完整的表格内容，并且应该只返回 5 组。我目前正在使用多个连接，我只是在测试一些东西并想到了这个并且想了解这个行为。

SELECT d.id, d.name, d.created, d.updated 
FROM documents d 
     INNER JOIN permissions p 
     ON (d.wfid = p.wfId AND d.docid = p.docId AND p.userid = 1) 
     INNER JOIN keywords k0 
     ON (k0.document_id = d.id AND (k0.keyword = 'Nombre' AND k0.value LIKE 'Cliente3') 
         OR (k0.keyword = 'Nombre' AND k0.value = 'Cliente4')) 
ORDER BY k0.value DESC, d.id DESC LIMIT 0, 12;

+------+-----------------------------+---------------------+---------------------+
| id   | name                        | created             | updated             |
+------+-----------------------------+---------------------+---------------------+
| 5355 | Documento - Digitalización  | 2014-03-31 12:57:51 | 2014-03-31 12:57:51 |
| 5354 | Documento - Digitalización  | 2014-03-28 11:14:11 | 2014-03-28 11:14:11 |
| 5270 | DOC2                        | 2014-03-07 11:41:23 | 2014-02-25 13:41:57 |
| 5269 | DOC2                        | 2014-03-07 11:41:23 | 2014-02-25 12:39:51 |
| 5268 | DOC2                        | 2014-03-07 11:41:23 | 2014-02-25 12:39:25 |
| 5267 | DOC1                        | 2014-03-07 11:41:23 | 2014-02-25 12:39:10 |
| 5266 | DOC1                        | 2014-03-07 11:41:23 | 2014-03-06 01:35:16 |
| 5265 | DOC1                        | 2014-03-07 11:41:23 | 2014-02-25 12:38:37 |
| 5264 | DOC1                        | 2014-03-07 11:41:23 | 2014-02-25 12:38:25 |
| 5263 | DOC1                        | 2014-04-07 11:23:00 | 2014-03-20 09:54:30 |
| 5269 | DOC2                        | 2014-03-07 11:41:23 | 2014-02-25 12:39:51 |
| 5268 | DOC2                        | 2014-03-07 11:41:23 | 2014-02-25 12:39:25 |
+------+-----------------------------+---------------------+---------------------+
12 rows in set (0.00 sec)

mysql> select * from keywords where keyword = 'Nombre';
+-------+-------------+---------+----------+
| id    | document_id | keyword | value    |
+-------+-------------+---------+----------+
| 15888 |        5263 | Nombre  | Cliente1 |
| 15889 |        5264 | Nombre  | Cliente1 |
| 15890 |        5265 | Nombre  | Cliente1 |
| 15891 |        5266 | Nombre  | Cliente2 |
| 15892 |        5267 | Nombre  | Cliente3 |
| 15893 |        5268 | Nombre  | Cliente3 |
| 15894 |        5269 | Nombre  | Cliente3 |
| 15895 |        5270 | Nombre  | Cliente4 |
+-------+-------------+---------+----------+
8 rows in set (0.00 sec)

Answer 1

我认为您的 OR 标准有点困惑。尝试移动括号:

k0.document_id = d.id AND 
((k0.keyword = 'Nombre' AND k0.value LIKE 'Cliente3') OR 
     (k0.keyword = 'Nombre' AND k0.value = 'Cliente4'))

这应该使您的连接保持完整。