Mysql获取行只包含具有特定值的行

Lets say, my tables has the following rows:

可以说,我的表有以下几行:

  id | prop1 | prop2 
______________________
  1    foo      a
  2    bar      a
  3    foo      b
  4    bar      b
  5    foo      c

Now in this case, i want to get rows grouped by prop1 but ignore the groups that don't contain a row with prop2=c

现在在这种情况下,我希望得到按prop1分组的行,但忽略不包含prop2 = c的行的组

So the intended result is:

所以预期的结果是:

1 foo a
3 foo b
5 foo c

Note, now there is only one group in the example but i want to be able to get all of them. How can i achieve this approach?

注意,现在示例中只有一个组,但我希望能够获得所有这些组。我怎样才能实现这种方法?

4 个解决方案

#1

You can use exists clause to remove all the rows if it does not have prop2 = c using below query.

如果使用下面的查询没有prop2 = c,则可以使用exists子句删除所有行。

select * 
from your_table t1
where 
exists (select 1 from your_table t2 where t1.prop1 = t2.prop1
        and t2.prop2 = 'c')

Explaination: Exists caluse will return true if it finds c for that group else false.

解释:存在caluse如果找到该组的c,则返回true,否则返回false。

#2

Your expected result is not grouped by prop1. If it was grouped by prop1 then you would get only one record in the result.

您的预期结果未按prop1分组。如果它按prop1分组,那么结果中只能得到一条记录。

To achieve the above, we can write a simple SELECT with sub-query, e.g.:

为了实现上述目的,我们可以用子查询编写一个简单的SELECT,例如:

SELECT *
FROM table
WHERE prop1 IN (
 SELECT prop1 FROM table WHERE prop2 = 'c'
);

#3

The following query fetches all record with prop2 = 'c' (T1) and joins them on all records (T2) that have an equal prop1:

以下查询使用prop2 ='c'(T1)获取所有记录,并将它们连接到具有相等prop1的所有记录(T2):

select T2.*
from    TABLE T1
join TABLE T2 ON T1.prop1=T2.prop1
WHERE T1.prop2 = 'c'
GROUP BY id

#4

SELECT * FROM table WHERE prop2 != "c" GROUP BY prop1

This line is gonna delete att rows with C and the group everything else on prop1

这行将删除带有C的行和在prop1上的其他所有组

#1