mysql - 如何按条件分组

Question

我有一个 design_category_table

+-----------+----------+------------+
| Id        | IdDesign | IdCategory |
+-----------+----------+------------+
| 1         | 1        | 1          |
| 2         | 1        | 2          |
| 3         | 2        | 1          |
| 4         | 2        | 2          |
| 5         | 2        | 3          |
| 6         | 3        | 1          |
| 7         | 3        | 2          |
| 8         | 3        | 3          |
| 9         | 3        | 4          |
+-----------+----------+------------+

1) 我想按 IdDesign 进行分组，IdCategory 的条件列表包含 (1 AND 2 AND 3)

结果将只显示2行

+-----------+----------+------------+
| Id        | IdDesign | IdCategory |
+-----------+----------+------------+
|3          | 2        | 1          |
|6          | 3        | 1          |
+-----------+----------+------------+

2) 我想按 IdDesign 进行分组，且 IdCategory 的条件列表等于(1 AND 2 AND 3)

结果将仅显示 1 行

+-----------+----------+------------+
| Id        | IdDesign | IdCategory |
+-----------+----------+------------+
|3          | 2        | 1          |
+-----------+----------+------------+

请帮助我...!

Answer 1

您可以尝试以下 -

DEMO

select min(Id) as Id,IdDesign,min(IdCategory)
from tablename
where IdCategory in (1,2,3)
group by IdDesign
having count(distinct IdCategory)>=3

输出:

Id  IdDesign    IdCategory
3     2          1
6     3          1

select min(id) as id, min(IdDesign) as IdDesign,IdCategory  
from
(  
select min(Id) as Id,IdDesign,min(IdCategory)
    from tablename
    where IdCategory in (1,2,3)
    group by IdDesign
    having count(distinct IdCategory)>=3
)A group by IdCategory