如何为具有多个标记的标记系统创建sql查询

Question

我有3个表，不知道如何创建一系列sql查询(或者如果可能的话，只创建一个复杂的查询(。表格为：

CREATE TABLE IF NOT EXISTS excerpt(
excerptID INT UNSIGNED NOT NULL AUTO_INCREMENT, 
author VARCHAR(45) NOT NULL, 
title VARCHAR(255) NOT NULL, 
text VARCHAR(1000) NOT NULL,
comments VARCHAR(1000) NOT NULL,
PRIMARY KEY (excerptID)
) ENGINE=INNODB CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS tag(
tagID INT UNSIGNED NOT NULL AUTO_INCREMENT,
description VARCHAR(45) NOT NULL , 
PRIMARY KEY (tagID)
) ENGINE=INNODB CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS tagmap (
excerptID INT UNSIGNED NOT NULL, 
tagID INT UNSIGNED NOT NULL, 
PRIMARY KEY (excerptID, tagID),
CONSTRAINT excerptFK FOREIGN KEY (excerptID) REFERENCES excerpt (excerptID)
ON DELETE CASCADE 
ON UPDATE CASCADE,
CONSTRAINT tagFK FOREIGN KEY (tagID) REFERENCES tag (tagID)
ON DELETE CASCADE 
ON UPDATE CASCADE
) ENGINE=INNODB;

基于description参数，我需要返回与绑定到该描述的所有摘录相关的所有标签描述。例如，如果我有四个摘录

Excerpt(1, aaaa , aaaa, aaaa, aaaa) bound via tagmap to tags (A, B, C)
Excerpt(2, bbbb, bbbb, bbbb, bbbb) bound via tagmap tags (A, D)
Excerpt(3, cccc, cccc, cccc, cccc) bound via tagmap to tag (B)
Excerpt(4, dddd, ddddd, dddd, dddd) bound via tagmap to tags (B, D)

并且我的参数是"；B"；，我想返回摘录ID和描述的列表：

[Tag [excerptID=1, description=A], Tag [excerptID=1, description=B], Tag [excerptID=1, description=C], Tag [excerptID=3, description=B], Tag [excerptID=4, description=B], Tag [excerptID=4, description=D]]

到目前为止，我有这个

String SQL = "SELECT e.excerptID, t.description from Excerpt AS e LEFT JOIN tagmap AS m ON m.excerptID = e.excerptID LEFT JOIN tag AS t ON t.tagID = m.tagID WHERE t.description = ?";

但这只会返回这个

[Tag [excerptID=1, description=B], Tag [excerptID=3, description=B], Tag [excerptID=4, description=B]]

我想不出如何继续下去。

Answer 1

基于description参数，我需要返回与绑定到该描述的所有摘录相关的所有标记描述。例如，如果我有四个摘录

我相信这符合您的要求：

select t.*
from tags t
where exists (select 1
from tagmap tm1 join
tagmap tm2
on tm1.excerptid = tm2.excerptid join
tags t2
on tm2.tagid = t.tagid
where tm1.tagid = t.tagid and
t2.description = ?
);

这看起来很复杂——也许是因为它确实如此。但子查询基本上是将所有成对的标记附加到同一个实体上。一个标签(tm2上的标签(与您关心的描述相关联。这就是where子句中的t2.description = ?。

另一个绑定到外部表中的标记。这就是关联子句tm1.tagid = t.tagid。

另一种表达方式是这个查询(基本上是子查询(：

select distinct tm1.tagid
from tagmap tm1 join
tagmap tm2
on tm1.excerptid = tm2.excerptid join
tags t2
on tm2.tagid = t.tagid
where t2.description = ?

返回实体上的所有标记，该实体也具有您要查找的标记。