android - 如何嵌套GROUP_CONCAT？

Question

我有这张表:

GROUP_CONCAT(DISTINCT mytable.gloss) AS gloss
...
GROUP BY mytable.entry

返回:

如何这样得到结果——按entry和sense分组，用分号';'分割符号？'

Answer 1

首先，按sense分组:

SELECT entry,
       sense,
       GROUP_CONCAT(DISTINCT gloss)
FROM mytable
GROUP BY entry,
         sense

entry sense gloss
----- ----- ------------
1     1     Orange,Red
1     2     Blue
2     3     Green
2     4     Yellow,Ivory
3     5     Grey

然后对该结果运行另一个GROUP BY:

SELECT entry,
       MIN(sense) AS sense,
       GROUP_CONCAT(gloss, ';') AS gloss
FROM (SELECT entry,
             sense,
             GROUP_CONCAT(DISTINCT gloss) AS gloss
      FROM mytable
      GROUP BY entry,
               sense)
GROUP BY entry

entry sense gloss
----- ----- ------------------
1     1     Orange,Red;Blue
2     3     Green;Yellow,Ivory
3     5     Grey