MYSQL在一列中搜索多个条件

Question

table1
-------------------------------
| id | color     | shape      |
|------------------------------
|  1 | green     | triangle   |
|  2 | green     | square     |
|  3 | blue      | rectangle  |
|  4 | white     | sphere     |
|  5 | yellow    | triangle   |
-------------------------------

我想得到一个结果，其中行在一列中有多个条件聚焦。这是我的密码。

SELECT * FROM table1 WHERE shape = 'triangle' and shape = 'square';

不过，结果在列颜色中应该具有相同的值。有可能得到如下结果吗？

-------------------------------
| id | color     | shape      |
|------------------------------
|  1 | green     | triangle   |
|  2 | green     | square     |
-------------------------------

Answer 1

一个选项使用not exists:

select t.*
from mytable t
where 
shape in ('triangle', 'square')
and exists (
select 1
from mytable t1
where 
t1.shape in ('triangle, 'square')
and t1.color = t.color
and t1.shape <> t.shape
)

如果你运行的是MySQL 8.0，你也可以使用窗口函数。假设(color, shape)元组中没有重复：

select id, color, shape
from (
select t.*, count(*) over(partition by color) cnt
from mytable t
where shape in ('triangle', 'square')
) t
where cnt > 1