示例GROUP_CONCAT

Question

我有一个有两个列(case和subcase)的表和以下值：

case subcase
1    0
2    1
3    1
4    0
5    0
6    4

我希望有一份只有子案例的案件清单，例如：

case  list_of_subcase
1     2,3
4     6
5     0

没有列出案例2、3和6，因为它们是子案例。

案例5没有子案例，但不是子案例本身。

如何写正确的选择？有什么帮助吗？

Answer 1

我们是否有过度思考的危险.

DROP TABLE my_table;

CREATE TABLE my_table
(my_case INT NOT NULL
,subcase INT NULL
);

INSERT INTO my_table VALUES
(1    ,NULL),
(2    ,1),
(3    ,1),
(4    ,NULL),
(5    ,NULL),
(6    ,4);

SELECT x.my_case
     , GROUP_CONCAT(y.my_case) subcases 
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.subcase = x.my_case 
 WHERE x.subcase IS NULL 
 GROUP 
    BY x.my_case;
+---------+----------+
| my_case | subcases |
+---------+----------+
|       1 | 2,3      |
|       4 | 6        |
|       5 | NULL     |
+---------+----------+

Answer 2

这里的挑战是如何正确地进行过滤--保留"5"，而不是其他子案例。

select (case when c.subcase = 0 then c.case else c.subcase) as subcase, 
       group_concat(c.case)
from cases c 
where c.subcase = 0 or
      (c.subcase = 0 and 
       not exists (select 1 from cases c2 where c2.subcase = c.case)
      )
group by (case when c.subcase = 0 then c.case else c.subcase end);

编辑：

用union all来表达这一点可能会更容易

select c.subcase, group_concat(c.case)
from cases c 
where c.subcase = 0 
group by c.subcase
union all
select c.case, 0
from cases c
group by c.case
having sum(c.subcase <> 0) = 0;