当前位置：首页 > 站长知识 > 数据库 > 正文内容

MySQL动态列转行

2024-11-29数据库46

在实际的数据库查询中，有时候我们需要将表中的动态列（即列数不固定）转换为行，以便更好地进行数据分析和展示。在MySQL中，可以通过使用一些技巧和函数来实现动态列转行的功能。本文将介绍怎么实现MySQL动态列转行。

初始表

首先，假设我们有一个表格 users，其中需要动态的列 create_time，我们希望将该列转换为行。下面是一个示例表格的结构：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 创建用户表
DROP TABLE IF EXISTS users;
CREATE TABLE users
(
    id          INT PRIMARY KEY auto_increment COMMENT '主键',
    username    VARCHAR(30) NOT NULL COMMENT '用户名',
    password    VARCHAR(30) NOT NULL COMMENT '密码',
    nickname    VARCHAR(30) COMMENT '昵称',
    phone       VARCHAR(11) COMMENT '电话号码',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    is_deleted  INT      DEFAULT 0 COMMENT '逻辑删除(1:已删除，0:未删除)'
) COMMENT '用户信息表';
 
# 插入数据
INSERT INTO users (username, password, nickname, phone, create_time)
VALUES ('admin', 'admin', '张三', '18955554444', '2023-05-01 22:48:11'),
       ('root', 'root', '李四', '17755624235', '2023-05-02 22:48:11'),
       ('lisi', 'lisi', '王五', '15989654123', '2023-05-03 22:48:11'),
       ('lucky', 'lucky', '赵六', '19956852548', '2023-05-04 22:48:11'),
       ('admin2', 'admin', '张三', '18955554444', '2023-05-01 22:48:11'),
       ('root2', 'root', '李四', '17755624235', '2023-05-02 22:48:11'),
       ('lisi2', 'lisi', '王五', '15989654123', '2023-05-01 22:48:11'),
       ('lucky2', 'lucky', '赵六', '19956852548', '2023-05-01 22:48:11');

想要的效果：

通过 格式化日期+计数函数+分组 实现

1
select DATE_FORMAT(create_time,'%Y/%m/%d') as create_date, count(*) as sum from users group by create_date

执行结果：

显然这并不是我们想要的效果。 这时候就需要用到动态列转行了。

通过 格式日期+求和函数 实现

1
2
3
4
5
6
7
SELECT
    SUM( DATE_FORMAT( create_time, '%Y/%m/%d' ) = '2023/05/01' ) AS '2023/05/01',
    SUM( DATE_FORMAT( create_time, '%Y/%m/%d' ) = '2023/05/02' ) AS '2023/05/02',
    SUM( DATE_FORMAT( create_time, '%Y/%m/%d' ) = '2023/05/03' ) AS '2023/05/03',
    SUM( DATE_FORMAT( create_time, '%Y/%m/%d' ) = '2023/05/04' ) AS '2023/05/04'
FROM
    users

执行结果：

这样就达到我们要的效果了。但是有局限性，如果在加一个日期就需要改SQL。

通过 存储过程+分组合并函数+SQL拼接 实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
# 设置结束分隔符
DELIMITER $$
# 判断定义的存储过程是否存在，存在删除，以防存储过程存在报错
DROP PROCEDURE IF EXISTS pro$$
# 创建存储过程
CREATE PROCEDURE pro () BEGIN
  # 定义一个变量
  SET @SQL = NULL;
    # 把查询的日期赋给变量
    # 这里需要将日期格式化一下, 我要的格式是yyyy/MM/dd,
    # 而数据给我们的是yyyy-MM-dd HH:mm:ss
    SELECT
        GROUP_CONCAT( DISTINCT CONCAT( 'SUM(DATE_FORMAT(create_time, \'%Y/%m/%d\') = ''',
        DATE_FORMAT( create_time, '%Y/%m/%d' ), ''') AS ''',         
        DATE_FORMAT( create_time, '%Y/%m/%d' ), '''' ) ) INTO @SQL
    FROM users;
    # 注意：如果运行时报错可以执行
    # SELECT @SQL;
    # 检查拼接的SQL是否正确
    # 拼接sql
    SET @SQL = concat( 'select ', @SQL, ' from users' );
    # 预处理语句
    PREPARE stmt FROM   @SQL;
    # 执行
    EXECUTE stmt;
    # 销毁
    DEALLOCATE PREPARE stmt;
# 结束
END $$
 
 # 调用存储过程
CALL pro ();

为了方便测试在插入一笔数据

1
2
INSERT INTO users (username, password, nickname, phone, create_time)
VALUES ('test', 'test', 'test', '18955554844', '2023-05-08 22:48:11');

执行结果：

通过动态的SQL拼接这种方法可以帮助我们更好地处理动态列的数据，方便进行后续的数据分析和展示。这样就满足我们的场景了。


相关文章

mysql服务无法启动怎么解决2024-11-27
SQL中的INNER JOIN用法详解2024-11-29
SQL Server怎么导入Excel数据2024-11-29
SQL Server怎么创建用户只能访问指定数据库2024-11-29
sql 快速删除数百万行数据的方法2024-11-29
SQL Server中数据类型转换的原理和使用方法2024-11-29
SQL Server中如何查询最近一条记录2024-11-29
SQL Server语句日期格式查找的方法2024-11-29
SQL Server数据库简单的事务日志备份恢复2024-11-29

本站不销售产品、不代购、不提供技术支持，仅分享信息，请遵纪守法、文明上网。

Copyright © 2015-2024 国外主机测评. 闽ICP备2024075899号-2