Oracle SQL如何将分组统计的员工类型结果拼接为字符串

表结构与数据 #

现有表 employee_tb，结构及数据如下：

table_name: employee_tb

    employee_id             employee_type
         1                        A
         2                        A
         3                        B
         4                        C
         5                        C
         6                        C

现有分组统计SQL #

已执行以下分组统计语句：

select employee_type, count(*) as employee_type_count
from employee_tb
GROUP BY employee_type;  

统计结果 #

得到如下结果集：

employee_type        employee_type_count
      A                       2
      B                       1
      C                       3

需求 #

将上述统计结果转换为 A: 2, B: 1, C: 3 格式的单个字符串。

完全可行，不同数据库有对应的字符串聚合函数可以实现该需求，以下是主流数据库的具体实现：

1. MySQL（5.7+） #

使用 GROUP_CONCAT 聚合函数拼接结果：

SELECT GROUP_CONCAT(CONCAT(employee_type, ': ', employee_type_count) SEPARATOR ', ') AS result
FROM (
    SELECT employee_type, COUNT(*) AS employee_type_count
    FROM employee_tb
    GROUP BY employee_type
) AS temp;

2. PostgreSQL #

使用 STRING_AGG 函数完成拼接：

SELECT STRING_AGG(CONCAT(employee_type, ': ', employee_type_count), ', ') AS result
FROM (
    SELECT employee_type, COUNT(*) AS employee_type_count
    FROM employee_tb
    GROUP BY employee_type
) AS temp;

3. SQL Server #

• SQL Server 2017+：直接使用 STRING_AGG

SELECT STRING_AGG(CONCAT(employee_type, ': ', employee_type_count), ', ') AS result
FROM (
    SELECT employee_type, COUNT(*) AS employee_type_count
    FROM employee_tb
    GROUP BY employee_type
) AS temp;

• SQL Server 2016及更早版本：使用 FOR XML PATH 方法

SELECT STUFF(
    (SELECT ', ' + CONCAT(employee_type, ': ', employee_type_count)
     FROM (
         SELECT employee_type, COUNT(*) AS employee_type_count
         FROM employee_tb
         GROUP BY employee_type
     ) AS temp
     FOR XML PATH('')), 1, 2, ''
) AS result;

4. Oracle #

使用 LISTAGG 函数：

SELECT LISTAGG(CONCAT(employee_type, ': ', employee_type_count), ', ') WITHIN GROUP (ORDER BY employee_type) AS result
FROM (
    SELECT employee_type, COUNT(*) AS employee_type_count
    FROM employee_tb
    GROUP BY employee_type
) AS temp;

内容的提问来源于stack exchange，提问作者Easy Money