mysql - 复杂的mysql选择查询

Question

数据:

1. 表格shapes:shape_id, shape_name
2. 表shape_forms:shape_form_id、shape_id、shape_form
3. shape_form 可以是:0 - 圆形、1 - 正方形、2 - 三角形 - 每个形状的数量是无限的

我需要 2 个查询来选择

1. 在 shape_forms 中同时包含圆形和三角形或仅包含圆形但不包含正方形或仅包含三角形的所有形状
2. 在 shape_forms 中只包含三角形的所有形状

请给我一些解决此任务的提示!我被限制不对 shape_forms 使用“分组依据”，但如果没有合适的解决方案

Answer 1

1.

select s.shape_id
from shapes s
inner join shape_forms sf on sf.shape_id = s.shape_id
group by s.shape_id
having 
(
   sum(shape_form = 1) = 0
   and sum(shape_form in (0,2)) >= 2
)
or sum(shape_form <> 0) = 0

2.

select s.shape_id
from shapes s
inner join shape_forms sf on sf.shape_id = s.shape_id
group by s.shape_id
having sum(shape_form <> 2) = 0