sql-server - 基于列在两个表上相交

我正在尝试在两个表上执行类似于基于列的相交的操作。
这些表是:

LogTag : 一个日志可以有零个或多个标签

MatchingRule :匹配规则由定义规则

的一个或多个标签组成

一个日志可以有零个或多个与之匹配的规则。我将传入 MatchingRuleID 并期望返回与该规则匹配的所有日志。

预期结果: 匹配 LogID 的结果集。例如。传入 MatchingRuleID = 30 应该返回 LogID 101。MatchingRuleID = 31 应该返回 LogID 101 & 100。

此外，LogTag 表可能有数百万行，因此首选高效查询。

问题: 如何找到所有与指定规则定义匹配的 LogID ？



架构:

CREATE TABLE dbo.Tag
(
    TagID INT,
    TagName NVARCHAR(50)
)
INSERT INTO dbo.Tag (TagID, TagName)
VALUES (1, 'tag1'), (2, 'tag2'), (3, 'tag3')

CREATE TABLE dbo.LogTag
(
    LogID INT,
    TagID INT
)
INSERT INTO dbo.LogTag (LogID, TagID)
VALUES (100, 1), (101, 1), (101, 2), (101, 3), (101, 4), (102, 2), (102, 3)

CREATE TABLE dbo.MatchingRule
(
    MatchingRuleID INT,
    TagID INT
)
INSERT INTO dbo.MatchingRule (MatchingRuleID, TagID)
VALUES (30, 1), (30, 2), (30, 3), (31, 1)

最佳答案

在表上拥有适当的聚集索引很重要。我在 #log_tag 的注释中添加了一个替代索引，这可能会提高大型集的性能。由于我没有合适的样本进行测试，因此您必须验证哪个是最好的。

CREATE TABLE #tag(tag_id INT PRIMARY KEY,tag_name NVARCHAR(50));
INSERT INTO #tag (tag_id,tag_name)VALUES
    (1,'tag1'),(2,'tag2'),(3,'tag3');

-- Try this key for large sets: PRIMARY KEY(tag_id,log_id));
CREATE TABLE #log_tag(log_id INT,tag_id INT,PRIMARY KEY(log_id,tag_id))
INSERT INTO #log_tag (log_id,tag_id)VALUES
    (100,1),(101,1),(101,2),(101,3),(101,4),(102,2),(102,3);

CREATE TABLE #matching_rule(matching_rule_id INT,tag_id INT,PRIMARY KEY(matching_rule_id,tag_id));
INSERT INTO #matching_rule(matching_rule_id,tag_id)VALUES
    (30,1),(30,2),(30,3),(31,1);

DECLARE @matching_rule_id INT=31;

;WITH required_tags AS (
    SELECT tag_id
    FROM #matching_rule
    WHERE matching_rule_id=@matching_rule_id
)
SELECT lt.log_id
FROM required_tags AS rt
     INNER JOIN #log_tag AS lt ON
         lt.tag_id=rt.tag_id
GROUP BY lt.log_id
HAVING COUNT(*)=(SELECT COUNT(*) FROM required_tags);

DROP TABLE #log_tag;
DROP TABLE #matching_rule;
DROP TABLE #tag;

结果是 预期结果 中 30 和 31 的结果。

脚本中使用的索引的执行计划:

关于sql-server - 基于列在两个表上相交，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35374836/