select - 需要有关 SELECT 语句的帮助

Question

我表达了记录和可以附加到记录的搜索标签之间的关系，如下所示:

TABLE RECORDS
id
name

TABLE SEARCHTAGS 
id
recordid
name

我希望能够根据记录的搜索标签来选择记录。例如，我希望能够选择所有具有搜索标签的记录:

(1 OR 2 OR 5) AND (6 OR 7) AND (10)

使用上述数据结构，我不确定如何构造 SQL 来完成此操作。

有什么建议吗？

谢谢!

Answer 1

您可能想尝试以下方法:

SELECT    r.id, r.name
FROM      records r
WHERE     EXISTS (SELECT NULL FROM searchtags WHERE recordid = r.id AND id IN (1, 2, 5)) AND 
          EXISTS (SELECT NULL FROM searchtags WHERE recordid = r.id AND id IN (6, 7)) AND 
          EXISTS (SELECT NULL FROM searchtags WHERE recordid = r.id AND id IN (10));

测试用例:注意只有记录1和4满足查询条件。

CREATE TABLE records (id int, name varchar(10));
CREATE TABLE searchtags (id int, recordid int);

INSERT INTO records VALUES (1, 'a');
INSERT INTO records VALUES (2, 'b');
INSERT INTO records VALUES (3, 'c');
INSERT INTO records VALUES (4, 'd');

INSERT INTO searchtags VALUES (1,  1);
INSERT INTO searchtags VALUES (2,  1);
INSERT INTO searchtags VALUES (6,  1);
INSERT INTO searchtags VALUES (10, 1);
INSERT INTO searchtags VALUES (1,  2);
INSERT INTO searchtags VALUES (2,  2);
INSERT INTO searchtags VALUES (3,  2);
INSERT INTO searchtags VALUES (1,  3);
INSERT INTO searchtags VALUES (10, 3);
INSERT INTO searchtags VALUES (5,  4);
INSERT INTO searchtags VALUES (7,  4);
INSERT INTO searchtags VALUES (10, 4);

结果:

+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    4 | d    |
+------+------+
2 rows in set (0.01 sec)