mysql行列转置报表_mysql 实现行列转置

写在前面

处理工作数据时，有时要编制数据表格。编制表格时，有时需把时间数据设为列标题。这种情况下，必须进行行列的调换。本文以 MySQL 数据库为例，说明其如何实现行列的转换操作。

测试步骤建表语句

CREATE TABLE test_stats (
主键编号为整型，支持自动增长，是首要的索引字段，其值唯一标识每条记录。
    location VARCHAR(255),
    test_name VARCHAR(255),
    test_type VARCHAR(255),
		dev_id varchar(255),
    countDay DATE,
    test_value DOUBLE,
    test_count DOUBLE
);

插入数据

向test_stats表中插入数据，包括地点信息，测试项目名称，测试类别，设备编号，测试天数，测试数值，以及测试次数
VALUES 
河南省鹤壁市测试结果为1，记录日期是2024年7月1日，该数值为5，状态为1。
河南省鹤壁市测试结果为2，记录日期是2024年7月2日，相关数据有6项，编号为2，
河南省鹤壁市记录一次，具体为2024年7月1日，数值为2.0，评级为3。
河南省鹤壁市出现了两次，具体日期是2024年7月2日，相关数值为3.0和4。

查询结果

* from ;

行列转置

设置会话中的 group_concat_max_len 参数值为一千万。
       SET @sql_value = NULL;
       SET @sql_count = NULL;
        SELECT
            COALESCE(
             GROUP_CONCAT(DISTINCT
            CONCAT(
计算当统计天数等于空字符串时的值
                countDay,
当测试值存在时使用该值,否则使用零,将其命名为`
按照年份月日格式转换计数天数，结果为，
                '`'
            )
        ),
        '0 AS `no_data`'
            ) INTO @sql_value
        FROM test_stats
在日期范围之内，具体为从2024年7月1日到2024年7月2日，统计天数符合这个条件
	
		  SELECT
           COALESCE(
           GROUP_CONCAT(DISTINCT
           CONCAT(
                'SUM(CASE WHEN countDay = ''',
               countDay,
当测试数量存在时使用该数量，否则返回零结果作为别名
                DATE_FORMAT(countDay, '%Y-%m-%d'),
               '`'
            )
       ),
       '0 AS `no_data`'
            ) INTO @sql_count
        FROM test_stats
        WHERE countDay BETWEEN '2024-07-01' AND '2024-07-02';
		
		
		
		 -- 构建故障时长的最终SQL
设定一个变量赋值为选取设备编号和测试值作为指标类型，再加上之前变量的值，后面接上
                      SUM(test_value) AS `合计`
                      FROM test_stats
在日期范围之内，具体时间从2024年7月1日到2024年7月2日
                      GROUP BY dev_id');
				  
将查询结果包含设备编号，将指标类型命名为测试数量，接着追加原有查询语句，最后完成整个表达式的拼接
                     SUM(test_count) AS `合计`
                      FROM test_stats
在日期范围之内，具体起始日为2024年7月1日，终止日为2024年7月2日，这个时间段内的记录需要筛选出来
                    GROUP BY dev_id');
				  
					  
	 -- 使用UNION ALL合并两个查询
将变量赋值为字符串拼接结果,第一个字符串包含参数值,第二个字符串包含参数计数,按照设备ID和指标类型排序,使用UNION ALL连接,整体构成SQL查询语句
       -- 准备并执行最终的动态SQL
       PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

执行结果

写在最后

最后，那个查询语句运行后，按照时间进行归类，把各项数据汇总起来，用日期做标题，还加上了总计这一项。那个查询，也可以换成其他类似形式，这样展示出来会让人更容易看懂。

这段关于行列转换的SQL语句，看似是一个存储过程，实际上能够直接放置在 里面。SQL查询中提供的时间参数（例如：2024-07-01），可以当作 中的条件输入。 的调用能够直接得到一个键值对集合。借助这个集合，相关的统计信息便可以获取了。

不过，制作报表通常需要特定的工具，例如润乾报表等。如果需求不复杂，直接运用mysql的sql语句也能解决，这是一种简便途径。