MySql:选择具有所有值的项目 [英] MySql: Select items what have all the values
问题描述
我有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以进行快速测试: http://sqlfiddle. com/#!9/2416f/2
I've created sqlfiddle with my schema and query for fast testing: http://sqlfiddle.com/#!9/2416f/2
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
这篇关于MySql:选择具有所有值的项目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!