我试图进行查询以查找EAV表keywords
和documents
表中的记录,使用OR语句,但它给了我重复的结果:
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.value IN('Pérez, Gabriel',
'DOC1') OR d.name IN ('Pérez, Gabriel', 'DOC1') ))
ORDER BY d.id DESC;
+------+------+---------------------+---------------------+
| id | name | created | updated |
+------+------+---------------------+---------------------+
| 5267 | DOC1 | 2014-03-07 11:41:23 | 2014-02-25 12:39:10 |
| 5267 | DOC1 | 2014-03-07 11:41:23 | 2014-02-25 12:39:10 |
| 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 |
| 5266 | DOC1 | 2014-03-07 11:41:23 | 2014-03-06 01:35:16 |
| 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 |
| 5265 | DOC1 | 2014-03-07 11:41:23 | 2014-02-25 12:38:37 |
| 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 |
| 5264 | DOC1 | 2014-03-07 11:41:23 | 2014-02-25 12:38:25 |
| 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 |
| 5263 | DOC1 | 2014-04-07 11:23:00 | 2014-03-20 09:54:30 |
| 5263 | DOC1 | 2014-04-07 11:23:00 | 2014-03-20 09:54:30 |
+------+------+---------------------+---------------------+
15 rows in set (0.00 sec)
如果我使用SELECT DISTINCT
+------+-----------------------------+---------------------+---------------------+
| 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 |
| 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 |
+------+-----------------------------+---------------------+---------------------+
7 rows in set (0.00 sec)
那么我就得到了我想要的。
我的问题是:
1)为什么第一行是重复的?
2)使用SELECT DISTINCT是不好的还是有助于提高性能?
3)我的查询写得不好吗?我需要在文档表和关键字表中搜索输入的值,以查看是否有匹配(如Google搜索)
mysql> describe documents;
+---------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------+------+-----+---------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(100) | YES | | NULL | |
| wfid | char(50) | YES | | NULL | |
| docid | char(50) | YES | | NULL | |
| created | timestamp | NO | | 0000-00-00 00:00:00 | |
| updated | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+---------+-----------+------+-----+---------------------+-----------------------------+
6 rows in set (0.00 sec)
mysql> describe keywords;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| document_id | int(11) | NO | MUL | NULL | |
| keyword | char(50) | NO | | NULL | |
| value | varchar(250) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
很难从你的问题中辨别出关键字表的确切形式,所以这个答案可能是不正确的。
但是,通常关键字(名称/值对)表可以像这样访问,只检索一个关键字的值。
SELECT d.id, d.name, d.created, d.updated,
k0.value AS doc1_value,
k1.value AS doc2_value
FROM documents AS d
LEFT JOIN keywords AS k0 ON k0.document_id = d.id AND k0.name = 'DOC1'
LEFT JOIN keywords AS k1 ON k1.document_id = d.id AND k1.name = 'DOC2'
这将检索每个文档名称为DOC1和DOC2的关键字属性的值。使用LEFT JOIN来处理特定文档可能缺少关键字值的情况。
您是否正在查找包含特定值的属性的文档?在这种情况下,您可能需要这样做:
SELECT d.id, d.name, d.created, d.updated,
k0.value AS doc1_value,
k1.value AS doc2_value
FROM documents AS d
JOIN keywords AS sk ON sk.document_id = d.id AND sk.value = 'Pérez, Gabriel'
LEFT JOIN keywords AS k0 ON k0.document_id = d.id AND k0.name = 'DOC1'
LEFT JOIN keywords AS k1 ON k1.document_id = d.id AND k1.name = 'DOC2'
注意使用JOIN而不是LEFT JOIN来排除不匹配的行。