MYSQL 将两列分组压缩为一列分组并分割

Question

我正在尝试将 2 列排序查询压缩为具有分割列的 1 列排序查询。如果它有一个合适的名称，我将不胜感激知道它是什么!

例子:

ocean
name     | type    | colour
fishy    | salmon  | red
splishy  | salmon  | red
splashy  | salmon  | pink
sploshy  | salmon  | pink
floaty   | whale   | blue
humprhey | whale   | grey
wilson   | whale   | grey

正在寻找(我不确定这叫做什么):

type  | count | colour breakdown
salmon| 4     | 2 red, 2 pink
whale | 3     | 1 blue, 2 grey

优先使用最少的字符串函数。

---

不确定如何继续:

(按一列排序)

SELECT o.type, count(*)
FROM ocean o
GROUP BY `type`

type  | count
salmon| 4
whale | 3

(按两列排序)

SELECT o.type, o.colour, count(*)
FROM ocean o
GROUP BY `type`, `colour`

type  | colour     | count
salmon| red        | 2
salmon| pink       | 2
whale | blue       | 1
whale | grey       | 2

Answer 1

丑陋的，未经测试的，但可能只是做你想做的:

SELECT type, SUM(color_count),
   GROUP_CONCAT(CONCAT(color_count, ' ', colour) SEPARATOR ', ') AS colour_breakdown
FROM (
    SELECT type, colour, COUNT(colour) AS colour_count
    FROM ocean
    GROUP BY type, colour
) AS subt
GROUP BY type