MySql:选择具有所有值的项目

本文介绍了MySql:选择具有所有值的项目的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有3列的Mysql表:id，company_id和tag_id. 它用于将公司和标签链接在一起. 表架构:

I have Mysql table with 3 columns: id, company_id and tag_id. It's used to link companies and tags together. Table schema:

CREATE TABLE tbl_company_tag_link ( id BIGINT NOT NULL AUTO_INCREMENT, company_id BIGINT NOT NULL, tag_id BIGINT NOT NULL, PRIMARY KEY(id) );

任何公司都可以与任意数量的标签链接. 我需要选择附有所有指定标签的公司. 例如，我需要具有tag_id = 1,2,3(全部都是！)的company_id. 我遇到的查询很丑:

Any company can be linked with any number of tags. I need to select companies, that have ALL of the specified tags attached. For example, I need company_id that have tag_id = 1,2,3 (all of them!) The query that I came to is ugly:

SELECT company_id, GROUP_CONCAT(tag_id) as group_concat_tag_id FROM tbl_company_tag_link WHERE tag_id IN (1,2,3) GROUP BY company_id HAVING group_concat_tag_id = "1,2,3"

我需要编写查询的帮助，很快.

I need help with writing query, that would be fast.

我已经使用自己的架构和查询创建了sqlfiddle以进行快速测试: sqlfiddle. com/#！9/2416f/2

I've created sqlfiddle with my schema and query for fast testing: sqlfiddle/#!9/2416f/2

为时已晚，发现了具有详细答案的相同问题:需要有关SQL查询的帮助，以查找使用所有指定标签标记的内容

too late, found the same question with detailed answers: Need help with sql query to find things tagged with all specified tags

推荐答案

您可以尝试以下操作:

SELECT company_id FROM tbl_company_tag_link WHERE tag_id IN (1,2,3) GROUP BY company_id HAVING COUNT(DISTINCT tag_id ) = 3

SQL FIDDLE DEMO

SQL FIDDLE DEMO