查询具有匹配明细记录的主记录 [英] Query for master records that have matching detail records

问题描述

目前我有以下表格结构.

Currently I'm having the following table structure.

主表文档:

<头>

ID	文件名
1	document1.pdf
2	document2.pdf
3	document3.pdf

明细表关键字:

<头>

ID	文档ID	关键字
1	1	关键字A
2	1	关键字B
3	1	关键字C
4	2	关键字B
5	3	关键字A
6	3	关键字D

创建此代码的代码:

CREATE TABLE Documents (
    ID int IDENTITY(1,1) PRIMARY KEY,
    Filename nvarchar(255) NOT NULL
);

CREATE TABLE Keywords (
    ID int IDENTITY(1,1) PRIMARY KEY,
    DocumentID int NOT NULL,
    Keyword nvarchar(255) NOT NULL
);

INSERT INTO Documents(Filename) VALUES 
  ('document1.pdf'), ('document2.pdf'), ('document3.pdf');

INSERT INTO Keywords(DocumentID, Keyword) VALUES 
  (1, 'KeywordA'),
  (1, 'KeywordB'),
  (1, 'KeywordC'),
  (2, 'KeywordB'),
  (3, 'KeywordA'),
  (3, 'KeywordD');

用于此的 SQL Fiddle.

我正在寻找一种方法来获取与某个关键字匹配的所有文档.

I'm looking for a way to get all documents matching a certain keyword.

这可能是例如使用以下 T-SQL 查询编写:

This could be e.g. written with the following T-SQL query:

SELECT Documents.* 
FROM Documents 
WHERE Documents.ID IN
(
  SELECT Keywords.DocumentID 
  FROM Keywords 
  WHERE Keywords.Keyword = 'KeywordA'
)

这成功了.

我目前遇到的问题是，我想查找与多个关键字匹配的所有文档，并结合逻辑 AND.

What I'm currently stuck with is when I want to find all documents that match multiple keyword, combined with logical AND.

例如查找包含三个详细记录的文档，关键字为 A、B 和 C.

E.g. find a document that has three detail records with keyword A, B and C.

我认为以下可能有效，但我根本不知道这是否高效或优雅:

I think the following might work, but I don't know whether this performant or elegant at all:

SELECT Documents.* 
FROM Documents 
WHERE Documents.ID IN
(
  SELECT Keywords.DocumentID 
  FROM Keywords
  WHERE 
    Keywords.Keyword = 'KeywordA' OR 
    Keywords.Keyword = 'KeywordB' 
  GROUP BY Keywords.DocumentID HAVING COUNT(*) = 2 
)

SQL 小提琴.

如何编写(高性能)SQL 查询来查找所有关联多个关键字的文档.

How to write a (performant) SQL query to find all documents that have multiple keywords associated.

如果更简单，一个具有恒定关键字数量(例如 3 个)的解决方案就足够了.

If it is easier, a solution with a constant number of keywords (e.g. 3) would be sufficient.

推荐答案

希望下面的查询能帮到你

I hope the following query can help you

SELECT D.ID
FROM Documents D
JOIN Keywords K ON K.DocumentID = D.ID
WHERE K.Keyword IN ('KeywordA', 'KeywordB', 'KeywordC')  
GROUP BY D.ID
HAVING COUNT(DISTINCT K.Keyword) = 3 

演示

这篇关于查询具有匹配明细记录的主记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！