具有逻辑操作的高性能SQL标签搜索查询

Question

如何在SQL中实现布尔标记搜索?
这个问题是我能找到的最接近的了，但是还有一些。

我所知道的唯一真正的解决方案是通过后端代码生成这样的查询并将其放入SQL，但我认为它很慢，我也想知道是否有任何其他方法可以做到这一点(例如有一个主查询而不是多个)。

也有可能使用IN或类似的解决方案:
如何查询基于多个'标签'在SQL ?

我不能使用典型的GROUP BY HAVING COUNT解决方案，因为它不能在具有标签列表的上下文中操作，正如该用户指出的那样:

我应该指定大多数现有的解决方案不工作，因为我正在寻找能够进行更复杂查询的东西，如括号分组和嵌套操作数。

Schema是"http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/

SELECT id AS post_id
FROM posts
WHERE EXISTS (SELECT name FROM tags WHERE post IS post_id AND name IS 'random')
AND NOT (
EXISTS (SELECT name FROM tags WHERE post IS post_id AND name IS 'query') AND
EXISTS (SELECT name FROM tags WHERE post IS post_id AND name IS '1')
)
AND EXISTS (SELECT name FROM tags WHERE post IS post_id AND name IS '2') 
AND EXISTS (SELECT name FROM tags WHERE post IS post_id AND name IS '3')
AND EXISTS (SELECT name FROM tags WHERE post IS post_id AND name IS 'racecar')

Answer 1

AGROUP BY HAVING COUNT将工作-它将是快速的，多功能的。一些例子:

CREATE TABLE tags(
post_id INT,
name VARCHAR(50),
UNIQUE KEY (post_id, name)
);
INSERT INTO tags(post_id, name) VALUES
(1, 'foo'),
(1, 'bar'),
(2, 'foo'),
(3, 'bar'),
(4, 'baz'),
(5, 'foo'),
(5, 'bar'),
(5, 'meh');
-- posts tagged foo AND bar
-- returns 1, 5
SELECT post_id
FROM tags
GROUP BY post_id
HAVING COUNT(CASE WHEN name IN ('foo', 'bar') THEN 1 END) = 2;
-- posts tagged foo OR bar
-- returns 1, 2, 3, 5
SELECT post_id
FROM tags
GROUP BY post_id
HAVING COUNT(CASE WHEN name IN ('foo', 'bar') THEN 1 END) > 0;
-- posts tagged (foo AND bar) OR (baz)
-- returns 1, 4, 5
SELECT post_id
FROM tags
GROUP BY post_id
HAVING COUNT(CASE WHEN name IN ('foo', 'bar') THEN 1 END) = 2
OR     COUNT(CASE WHEN name IN ('baz') THEN 1 END) = 1;
-- posts tagged (foo AND bar) AND (no other tags)
-- returns 1
SELECT post_id
FROM tags
GROUP BY post_id
HAVING COUNT(CASE WHEN name IN ('foo', 'bar') THEN 1 END) = 2
AND    COUNT(*) = 2;
-- posts tagged (foo OR bar) AND NOT (meh)
-- returns 1, 2, 3
SELECT post_id
FROM tags
GROUP BY post_id
HAVING COUNT(CASE WHEN name IN ('foo', 'bar') THEN 1 END) > 0
AND    COUNT(CASE WHEN name IN ('meh') THEN 1 END) = 0;

DB<>提琴演示

---

将表达式(如tag1 AND tag2 OR tag3)转换为相应的HAVING COUNT在我的回答中没有涉及，但五个示例应该足够了。

Answer 2

Schema prep

CREATE TABLE posts (
ID INT PRIMARY KEY IDENTITY(1,1),
subj nvarchar(50)
)
GO
CREATE TABLE tags (
post INT,
name nvarchar(50)
)
GO

数据准备

INSERT INTO posts (subj) VALUES ('post1')
INSERT INTO posts (subj) VALUES ('post2')
INSERT INTO posts (subj) VALUES ('post3')
INSERT INTO tags VALUES (1, 'food')
INSERT INTO tags VALUES (1, 'spicy')
INSERT INTO tags VALUES (2, 'spicy')
INSERT INTO tags VALUES (2, 'recipe')
INSERT INTO tags VALUES (3, 'food')
INSERT INTO tags VALUES (3, 'spicy')
INSERT INTO tags VALUES (3, 'sweet')

查询

;WITH Aggregated_Tags AS (
SELECT
post,
STRING_AGG(name, ',') AS name
FROM tags
GROUP BY post
)
SELECT post
FROM Aggregated_Tags
WHERE 
(name LIKE '%food%' AND name LIKE '%spicy%' AND name NOT LIKE '%sweet%')
OR (name LIKE '%recipe%')
GROUP BY post

如果我理解正确的话，你正在寻找这样的东西。这里的关键是聚合每个帖子的标记，以避免生成多个选择查询。这个解决方案不完整，但我相信这是一个好的开始。