mysql 竖列变横列 count group

原创

mob649e815f0f18 2024-07-17 05:58:38 ©著作权

©著作权归作者所有：来自51CTO博客作者mob649e815f0f18的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL 竖列变横列及分组统计

在数据库操作中，我们经常需要对数据进行各种统计和转换，以适应不同的业务需求。本文将介绍如何在MySQL中将竖列数据转换为横列，并进行分组统计。

问题背景

假设我们有一个员工表employees，包含员工的ID、姓名和部门ID：

| id | name  | department_id |
|----|-------|---------------|
| 1  | Alice | 1            |
| 2  | Bob   | 2            |
| 3  | Cindy | 1            |
| 4  | Dave  | 2            |

我们的目标是将部门ID作为列，员工数量作为值，得到如下结果：

| department_id | count |
|---------------|-------|
| 1             | 2     |
| 2             | 2     |

解决方案

1. 使用`GROUP BY`和`COUNT()`

首先，我们可以使用GROUP BY和COUNT()函数来实现分组统计：

SELECT department_id, COUNT(*) AS count
FROM employees
GROUP BY department_id;

这将返回每个部门的员工数量。

2. 竖列转横列

要将结果转换为横列，我们可以使用CASE语句和GROUP_CONCAT函数：

SELECT 
  GROUP_CONCAT(department_id SEPARATOR ', ') AS departments,
  GROUP_CONCAT(COUNT(*) SEPARATOR ', ') AS counts
FROM (
  SELECT department_id, COUNT(*) AS count
  FROM employees
  GROUP BY department_id
) AS subquery;

这将返回部门ID和员工数量的列表，但仍然是竖列形式。

3. 使用PIVOT操作

在MySQL 8.0及以上版本，我们可以使用PIVOT操作来实现竖列转横列：

SELECT *
FROM (
  SELECT department_id, COUNT(*) AS count
  FROM employees
  GROUP BY department_id
) AS src
PIVOT (
  MAX(count)
  FOR department_id IN (1, 2)
) AS pvt;

这将返回部门ID作为列，员工数量作为值的横列结果。

甘特图展示

以下是使用Mermaid语法展示的甘特图，描述了将竖列数据转换为横列的步骤：

gantt
    dateFormat  YYYY-MM-DD
    title 转换过程
    section 步骤1: 分组统计
    分组统计 :done,    des1, 2023-01-01,2023-01-02
    section 步骤2: 使用CASE和GROUP_CONCAT
    竖列转横列 :active,  des2, after des1
    section 步骤3: 使用PIVOT操作
    使用PIVOT :         des3, after des2