MySQL 竖表转横表及联合查询

引言

在数据库管理中，竖表转横表的操作常用于数据格式调整，特别是在数据分析和报告制作中非常有用。本文将通过具体示例介绍如何在 MySQL 中实现竖表转横表的操作，并结合联合查询来获取更复杂的数据。

竖表与横表

  • 竖表：通常一列表示某个属性，另一列表示属性的值。例如，存储用户的活动记录：

    用户ID 活动 日期
    1 登陆 2023-01-01
    1 注销 2023-01-02
    2 登陆 2023-01-03

  • 横表：将活动列转换为多个列。结果如下：

    用户ID 登陆 注销
    1 2023-01-01 2023-01-02
    2 2023-01-03 NULL

使用 MySQL 进行竖表转横表

在 MySQL 中，可以使用 GROUP_CONCAT、CASE 和 GROUP BY 等语句来实现竖表转横表。我们可以使用下列语法示例：

SELECT
    用户ID,
    MAX(CASE WHEN 活动 = '登陆' THEN 日期 END) AS 登陆,
    MAX(CASE WHEN 活动 = '注销' THEN 日期 END) AS 注销
FROM
    活动记录
GROUP BY
    用户ID;

解析：

  1. CASE：用来选择不同活动的日期。
  2. MAX：确保为每个用户选择唯一的日期。
  3. GROUP BY：按用户ID分组。

联合查询

有时，我们需要结合其他表中的数据进行联合查询。例如，如果有一个用户表，存储用户的基本信息，我们可以通过联合查询获取用户的活动记录和基本信息：

SELECT
    u.用户ID,
    u.姓名,
    MAX(CASE WHEN a.活动 = '登陆' THEN a.日期 END) AS 登陆,
    MAX(CASE WHEN a.活动 = '注销' THEN a.日期 END) AS 注销
FROM
    用户 u
LEFT JOIN
    活动记录 a ON u.用户ID = a.用户ID
GROUP BY
    u.用户ID;

解析：

  1. LEFT JOIN：包含所有用户，即使他们没有活动记录。
  2. 联合查询的目的是扩展数据视图，使其包含用户基本信息和活动记录。

旅行图

在数据处理的旅程中，我们的步骤如下：

journey
    title 数据处理旅程
    section 数据收集
      收集活动记录: 5: 用户
      收集用户信息: 5: 用户
    section 数据转换
      竖表转横表: 3: 数据库
      联合查询: 4: 数据库
    section 数据分析
      数据可视化: 5: 数据分析工具

类图

在我们的应用场景中，考虑用户和活动记录的类来理解数据模型：

classDiagram
    class 用户 {
        +用户ID: int
        +姓名: string
        +年龄: int
    }
    
    class 活动记录 {
        +用户ID: int
        +活动: string
        +日期: date
    }
    
    用户 "1" -- "0..*" 活动记录 : 产生

结尾

本文介绍了如何在 MySQL 中实现竖表转横表的操作，并通过联合查询整合多表数据。通过实例展示，读者可以了解到如何利用 SQL 的强大功能进行数据处理。希望这些知识能够帮助你更高效地进行数据库管理和数据分析！