写在前面

在工作中， 有时候需要开发报表。 有时候报表需要将时间做为表头。 这个就需要用到行列转置。 本文以 mysql 为例。 看看，它的行列转置应该如何做。

测试步骤

建表语句

CREATE TABLE test_stats (

    id INT AUTO_INCREMENT PRIMARY KEY,

    location VARCHAR(255),

    test_name VARCHAR(255),

    test_type VARCHAR(255),

		dev_id varchar(255),

    countDay DATE,

    test_value DOUBLE,

    test_count DOUBLE

);

插入数据

INSERT INTO test_stats (location, test_name, test_type,dev_id, countDay, test_value, test_count)

VALUES 

    ('河南', '鹤壁', '测试值', '1','2024-07-01', 5, 1),

    ('河南', '鹤壁', '测试值', '2','2024-07-02', 6, 2),

    ('河南', '鹤壁', '次数', '1','2024-07-01', 2.0, 3),

    ('河南', '鹤壁', '次数', '2','2024-07-02', 3.0, 4);

查询结果

select * from test_stats;

行列转置

SET SESSION group_concat_max_len = 10000000;

       SET @sql_value = NULL;

       SET @sql_count = NULL;



        SELECT
            COALESCE(
             GROUP_CONCAT(DISTINCT
            CONCAT(
                'SUM(CASE WHEN countDay = ''',
                countDay,
                ''' THEN test_value ELSE 0 END) AS `',
                DATE_FORMAT(countDay, '%Y-%m-%d'),
                '`'
            )
        ),
        '0 AS `no_data`'
            ) INTO @sql_value
        FROM test_stats
        WHERE countDay BETWEEN '2024-07-01' AND '2024-07-02';

	
		  SELECT

           COALESCE(

           GROUP_CONCAT(DISTINCT

           CONCAT(

                'SUM(CASE WHEN countDay = ''',

               countDay,

                ''' THEN test_count ELSE 0 END) AS `',

                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

        SET @sql_value = CONCAT('SELECT dev_id, ''test_value'' AS metric_type, ', @sql_value, ',

                      SUM(test_value) AS `合计`

                      FROM test_stats

                      WHERE countDay BETWEEN ''2024-07-01'' AND ''2024-07-02''

                      GROUP BY dev_id');

				  

		SET @sql_count = CONCAT('SELECT dev_id, ''test_count'' AS metric_type, ', @sql_count, ',

                     SUM(test_count) AS `合计`
                      FROM test_stats

                     WHERE countDay BETWEEN ''2024-07-01'' AND ''2024-07-02''

                    GROUP BY dev_id');

				  
					  

	 -- 使用UNION ALL合并两个查询

       SET @sql = CONCAT('(', @sql_value, ') UNION ALL (', @sql_count, ') ORDER BY dev_id, metric_type');



       -- 准备并执行最终的动态SQL

       PREPARE stmt FROM @sql;

        EXECUTE stmt;

        DEALLOCATE PREPARE stmt;

执行结果

写在最后

最终，上边的 sql 执行，以 dev_id 进行分组， 将相关指标进行统计， 以日期作为表头，并且增加了一个合计功能。 上边的 dev_id 也可以换成 dev_name 等， 看起来效果会更直观。

上边的这段行列转置的sql, 看起来像一个存储过程， 不过， 它是可以直接放到 mybatis 中的 <select>标签中的。 sql中传入的 时间值(比如： 2024-07-01),可以作为 mybatis 中 传入条件。 mybatis 的 接口可以直接返回一个 map 即可。 这样就可以获取到相关的统计数据了。

不过，做报表一般都是专门的一些报表工具， 比如： 润乾报表等。 对于简单的需求， 能直接用 mysql 的 sql语句搞定的， 也是一种简单的方法。