mysql透视表编写方法

mysql数据库，有以下数据表（table1），当然是巨量的，我需要生成后边的结果，生成一个新表（table2）。

日期 名称 值 值2
2022/3/25 A 48.62 z
2022/3/25 B 37.14 q
2022/3/25 C 7.97 w
2022/3/25 D 18.64 r
2022/3/25 F 23.57 t
2022/3/26 A 6.92 z
2022/3/26 B 45.5 q
2022/3/26 C 12.58 w
2022/3/26 D 29.04 r
2022/3/26 F 18.35 t
2022/3/27 A 53.5 z
2022/3/27 B 23.65 q
2022/3/27 C 4.02 w
2022/3/27 D 4.03 r
2022/3/27 F 5.34 t
2022/3/28 A 5.02 z
2022/3/28 B 5.57 q
2022/3/28 C 5.68 w
2022/3/28 D 7.43 r
2022/3/28 F 6.12 t

我需要以下结果，生成另一个表（table2）：

名称 值2 2022/3/25 2022/3/26 2022/3/27 2022/3/28
A z 48.62 6.92 53.5 5.02
B q 37.14 45.5 23.65 5.57
C w 7.97 12.58 4.02 5.68
D r 18.64 29.04 4.03 7.43
F t 23.57 18.35 5.34 6.12

谢谢！

因为是数据量较大的，不能够罗列日期等，需要用取值方式，自动取值，自动生成。

/*建表*/ 
create table T
(
 riqi DATE ,
 mc varchar(10),
 z numeric(18,2),
 z2 VARCHAR(10)
)
/*添加测试数据*/
insert into T  values('2022/3/25', 'A', 48.62 ,'z');
insert into T  values('2022/3/25', 'B', 37.14 ,'q');
insert into T  values('2022/3/25', 'C', 7.97 ,'w');
insert into T  values('2022/3/25', 'D', 18.64 ,'r');
insert into T  values('2022/3/25', 'F', 23.57 ,'t');
insert into T  values('2022/3/26', 'A', 6.92 ,'z');
insert into T  values('2022/3/26', 'B', 45.5 ,'q');
insert into T  values('2022/3/26', 'C', 12.58 ,'w');
insert into T  values('2022/3/26', 'D', 29.04 ,'r');
insert into T  values('2022/3/26', 'F', 18.35 ,'t');
insert into T  values('2022/3/27', 'A', 53.5 ,'z');
insert into T  values('2022/3/27', 'B', 23.65 ,'q');
insert into T  values('2022/3/27', 'C', 4.02 ,'w');
insert into T  values('2022/3/27', 'D', 4.03 ,'r');
insert into T  values('2022/3/27', 'F', 5.34 ,'t');
insert into T  values('2022/3/28', 'A', 5.02 ,'z');
insert into T  values('2022/3/28', 'B', 5.57 ,'q');
insert into T  values('2022/3/28', 'C', 5.68 ,'w');
insert into T  values('2022/3/28', 'D', 7.43 ,'r');
insert into T  values('2022/3/28', 'F', 6.12 ,'t');

/*拼接行转列SQL*/
set @sql = '';

select GROUP_CONCAT('max(case when riqi=''',riqi,''' then z else null end) as `',  riqi,'`')  into @sql   from 
(
    select riqi  from T group by riqi
) a ;
set  @sql =CONCAT('select mc,z2,',@sql,' from t group  by mc,z2');
 
 
/*执行SQL,得到结果*/ 
PREPARE stmt FROM @sql;       
EXECUTE stmt ;        
deallocate prepare stmt;      

/*删除表*/
drop  table T 

代码如下，望采纳

    @Test
    public void testCreateTable(){
        String sql = "select date from table1 group by date";

        ArrayList<String> columns = sqlUtil.getOneColumns(sql, new Object[]{});//此处查询数据库中的日期列表，用于动态判断需要在table2中插入列名使用，sqlUtil是我项目中的工具类，此处换成你自己查询的逻辑即可，不要一味模仿，注意修改

        String URL = "jdbc:mysql://localhost:3306/test";//修改为自己的库
        String USERNAME = "root";//修改为自己的数据库账号
        final String PASSWORD = "xxxx";//修改为自己的数据库密码

        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            //table2:动态创建table2的sql语句
            String table2 = "CREATE TABLE table2 (" +
                    "  id int(11) NOT NULL AUTO_INCREMENT, " +
                    "  name varchar(100) DEFAULT NULL, " +
                    "  value2 varchar(100) DEFAULT NULL, ";

            String mutiColumn = "";

            for (int i = 0; i < columns.size() ; i++) {
                mutiColumn = mutiColumn + "`"+ columns.get(i)+ "`" + " varchar(20) DEFAULT NULL,";
            }

            table2 = table2 + "" +mutiColumn  + ""+ " PRIMARY KEY (id) ) ";
            System.out.println("sql语句："+ table2);

            Statement statement = connection.createStatement();
            statement.execute(table2);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //此处书写将表table1数据插入table2表的逻辑，由于不麻烦，可自行实现。功能难点在于动态生成table2的列名
    }

生成表的效果图：

这个不会，学习了

1、如果数据量很大，那日期按天算应该很多，那table2会有多少个字段？虽然MySQL可以支持上千的字段数，但始终是有上限的，要看是否超出字段上限
2、确实要这么做，得使用存储过程或手工分步执行：
2.1、先找出table1中最小日期和最大日期：

   SELECT max( 日期 )  day_max, min( 日期 ) day_min, datediff( max( 日期 ), min( 日期 ) ) + 1 AS cols FROM table1;

　其中的cols 即为table2 日期部分的字段数，day_min为动态开始字段，day_max为动态结束字段，根据这些字段数创建表：table2
2.2、添加数据

    INSERT INTO table2 ( 名称, 值2, "2022-03-25", "2022-03-26", "2022-03-27", "2022-03-28" …… )
        SELECT 名称, 值2
              , sum( CASE WHEN 日期 = '2022-03-25' THEN 值 END ) AS "2022-03-25"
              , sum( CASE WHEN 日期 = '2022-03-26' THEN 值 END ) AS "2022-03-26"
              , sum( CASE WHEN 日期 = '2022-03-27' THEN 值 END ) AS "2022-03-27"
              , sum( CASE WHEN 日期 = '2022-03-28' THEN 值 END ) AS "2022-03-28"
              …… 
          FROM table2
         GROUP BY 名称, 值2;

其中的省略号部分则为简单的复制粘贴，然后修改日期即可……当然，如果你会通过EXCEL公式组装SQL，那就简单多了，如下图所示：

拖拉后复制图中H列到SQL语句中即可

2.3、样例数据输出结果：

3、如果是个临时性的工作，手工执行一次即可，如果是个长期性的工作，编写个存储过程，参照上述过程使用循环动态拼接SQL来执行比较方便
3.1、动态生成表和拼接SQL语句需要一个字段数（日期字段）的循环，可使用下面语句的结果作为游标SQL：

    SELECT @day_min AS cols, @day_min := date_add( @day_min, INTERVAL 1 DAY )
      FROM table1 a, ( SELECT @day_max := max( 日期 ), @day_min := min( 日期 ), @cols := datediff( max( 日期 ), min( 日期 ) ) + 1 FROM table1 ) b
     WHERE @day_min <= @day_max;

　其中的cols字段即为动态日期列名

您好，我已经看到结果了，我是小白，我需要将结果生成另一个表。请您再教一下，非常感谢！

mysql> create table T
(
riqi DATE ,
mc varchar(10),
z numeric(18,2),
z2 VARCHAR(10)
)
-> ;
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql>
mysql>
mysql> insert into T values('2022/3/25', 'A', 48.62 ,'z');
insert into T values('2022/3/25', 'B', 37.14 ,'q');
insert into T values('2022/3/25', 'C', 7.97 ,'w');
insert into T values('2022/3/25', 'D', 18.64 ,'r');
insert into T values('2022/3/25', 'F', 23.57 ,'t');
insert into T values('2022/3/26', 'A', 6.92 ,'z');
insert into T values('2022/3/26', 'B', 45.5 ,'q');
insert into T values('2022/3/26', 'C', 12.58 ,'w');
insert into T values('2022/3/26', 'D', 29.04 ,'r');
insert into T values('2022/3/26', 'F', 18.35 ,'t');
insert into T values('2022/3/27', 'A', 53.5 ,'z');
insert into T values('2022/3/27', 'B', 23.65 ,'q');
insert into T values('2022/3/27', 'C', 4.02 ,'w');
insert into T values('2022/3/27', 'D', 4.03 ,'r');
insert into T values('2022/3/27', 'F', 5.34 ,'t');
insert into T values('2022/3/28', 'A', 5.02 ,'z');
insert into T values('2022/3/28', 'B', 5.57 ,'q');
insert into T values('2022/3/28', 'C', 5.68 ,'w');
insert into T values('2022/3/28', 'D', 7.43 ,'r');
insert into T values('2022/3/28', 'F', 6.12 ,'t');
Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

mysql> set @sql = '';

select GROUP_CONCAT('max(case when riqi=''',riqi,''' then z else null end) as ', riqi,'') into @sql from
(
select riqi from T group by riqi
) a ;
set @sql =CONCAT('select mc,z2,',@sql,' from t group by mc,z2');
Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM @sql;
EXECUTE stmt ;
deallocate prepare stmt;
Query OK, 0 rows affected (0.01 sec)
Statement prepared

+----+----+------------+------------+------------+------------+
| mc | z2 | 2022-03-25 | 2022-03-26 | 2022-03-27 | 2022-03-28 |
+----+----+------------+------------+------------+------------+
| A | z | 48.62 | 6.92 | 53.50 | 5.02 |
| B | q | 37.14 | 45.50 | 23.65 | 5.57 |
| C | w | 7.97 | 12.58 | 4.02 | 5.68 |
| D | r | 18.64 | 29.04 | 4.03 | 7.43 |
| F | t | 23.57 | 18.35 | 5.34 | 6.12 |
+----+----+------------+------------+------------+------------+
5 rows in set (0.04 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>