Mysql查询以查找一列满足多个条件的ID [英] Mysql query to find ID where multiple condition meet for one column

问题描述

如果您能帮助我创建此查询，我将不胜感激.我尝试了几种方法都没有运气.由于我的问题对我来说有点难以表达，我将举一个简单的例子来说明我想要做什么.我的数据在结构上类似于以下内容:

I would appreciate any help creating this query. I have tried several methods without luck. As my question is somewhat difficult for me to phrase, I will make a simple example of what I want to do. I have data that is structurally similar to the following:

ID  TYPE  COLOR
1   A     Blue
1   B     Red
1   C     Green
2   C     Blue 
2   B     Green 

如果我有一个带有两种特定颜色的 ID，我想创建一个查询来返回类型.例如，我想找到所有具有蓝色和红色的 ID.然后查询将返回:

I want to create a query to return the types if I have an ID with two specific colors. For example I would like to find all the ID that have both Blue and Red. The query would then return:

1, A , B 

A 和 B 的返回顺序无关紧要.数据集很大，我希望很多 ID 都符合这两个条件(可能是 50,000 左右).我应该注意，类型与颜色无关，因此问题不同于 这个其他 stackoverflow 问题,以及这个.

The order that A and B is returned is unimportant. The data set is large, I expect many ID's to match both conditions( maybe 50,000 or so). I should note that the type does not correlate to color thus making the problem different from this other stackoverflow question,as well as this one.

我想我需要做某种子查询.但真的不知道如何继续，谢谢.

I think I need to do a subquery of some sort. But really don't know how to proceed, Thank you.

推荐答案

SELECT ID, TYPE FROM types NATURAL JOIN (
  SELECT ID FROM types GROUP BY ID HAVING SUM(COLOR='Red') AND SUM(COLOR='Blue')
) t WHERE COLOR IN ('Red', 'Blue')

在 sqlfiddle 上查看.

或者，如果您乐于将类型连接成一个分隔的字符串，您可以一次性提取所需的数据:

Alternatively, if you're happy to have the types concatenated into a delimited string, you can extract the desired data in a single pass:

SELECT   ID, GROUP_CONCAT(TYPE)
FROM     types
WHERE    COLOR IN ('Red', 'Blue')
GROUP BY ID
HAVING   COUNT(*) = 2

在 sqlfiddle 上查看.

请注意，如果您的表可能包含多个具有相同 (ID, COLOR) 对的记录，您应该将 COUNT(*) 替换为更昂贵的 COUNT(DISTINCT COLOR).

Note that, if your table might contain multiple records with the same (ID, COLOR) pair, you should replace COUNT(*) with the more expensive COUNT(DISTINCT COLOR).

这篇关于Mysql查询以查找一列满足多个条件的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！