sql - 如何查找具有完全包含的标签子集的行

问题描述

我试图找到一个查询，它将返回我的对象记录，就其标签而言，这些对象包含另一种类型的对象。

澄清一下：我有三种类型的对象：数据、项目和标签。数据和项目都可以有零个或多个标签。我正在尝试构建一个查询，给定“数据”记录的 ID，将返回其标签完全包含在“数据”标签中的所有项目记录。

因此，给定带有标签的数据项[foo, bar, baz]，查询将产生带有标签的项目[foo]，或[foo, baz]，或[foo, bar, baz]，但不是带有标签的项目[foo, bar, baz, quux]（太多），也不是带有标签的项目[foo, blah]（blah数据记录中没有）。

我无法理解此类查询所需的连接。

这些表data带有连接表data_tags、item连接表item_tags和tag。我想我需要看看这样的东西（伪代码，有点）：

SELECT DISTINCT item_tags.item_id
  FROM data_tags CROSS JOIN item_tags
 WHERE item_tags.tag_id = data_tags.tag_id
   AND data_tags.data_id = ?

...但我认为这将返回与中的标签有任何重叠的所有items ，并且我只想要在数据记录中具有所有标签的项目。data

所以假设我有一个data记录：

+----+------+
| id | name | 
+----+------+
|  1 | Test |
+----+------+

带有标签data_tags：

+----+---------+--------+
| id | data_id | tag_id |
+----+---------+--------+
|  1 |       1 |      1 | // Foo
|  2 |       1 |      2 | // Bar
|  3 |       1 |      3 | // Baz
+----+---------+--------+

还有两项（item_tags为简洁起见未显示）：

+----+--------+
| id | name   |
+----+--------+
|  1 | Item A | // Tags: Foo, Bar
|  2 | Item B | // Tags: Foo, Quux
+----+--------+

查询应该返回Item A但不是Item B。这似乎是一个相当简单的问题，但我无法理解它。有什么帮助吗？

下面包括 DDL 语句：

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `data_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `data_id` (`data_id`),
  KEY `tag_id` (`tag_id`),
  CONSTRAINT `data_tags_ibfk_1` FOREIGN KEY (`data_id`) REFERENCES `data` (`id`),
  CONSTRAINT `data_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `item` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `item_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `item_id` (`item_id`),
  KEY `tag_id` (`tag_id`),
  CONSTRAINT `item_tags_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `item` (`id`),
  CONSTRAINT `item_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tag` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

标签： sqljoin

SELECT i.name FROM items i JOIN item_tags it ON it.item_id = i.id LEFT JOIN data_tags dt ON dt.tag_id = it.tag_id LEFT JOIN data d ON d.id = dt.data_id AND d.id = 1 GROUP BY i.name HAVING COUNT(it.tag_id) = COUNT(dt.tag_id)

sql - 如何查找具有完全包含的标签子集的行

问题描述

解决方案

推荐阅读