mysql数据库sql行转列高效率的方式

mysql数据库sql行转列高效率的方式

大家好我是小星，今天带来的是mysql数据库sql行转列高效率的方式

在 MySQL 数据库中，可以通过 SQL 查询实现“行转列”，即将多行数据转换为列。这种转换通常称为“Pivot”操作

初始化表和数据

CREATE TABLE sales (
    id INT,
    year INT,
    amount DECIMAL(10, 2)
);

INSERT INTO sales (id, year, amount) VALUES
(1, 2021, 100.00),
(2, 2021, 150.00),
(1, 2022, 200.00),
(2, 2022, 250.00),
(1, 2023, 300.00),
(2, 2023, 350.00);

1. 使用group by (效率高 ，只查询了一次表)

   SELECT
       id,
       MAX(CASE WHEN year = 2021 THEN amount END) AS `2021`,
       MAX(CASE WHEN year = 2022 THEN amount END) AS `2022`,
       MAX(CASE WHEN year = 2023 THEN amount END) AS `2023`
   FROM sales
   GROUP BY id;
   

   此方法在postgresql数据库同样使用。

2. 使用动态 SQL

   1. 获取所有年份（获取所有目标）

      SET @sql = NULL;
      SELECT
        GROUP_CONCAT(DISTINCT
          CONCAT(
            'MAX(CASE WHEN year = ', year, ' THEN amount END) AS `', year, '`'
          )
        ) INTO @sql
      FROM sales;
      

      select @sql;
      运行结果：MAX(CASE WHEN year = 2021 THEN amount END) AS `2021`,MAX(CASE WHEN year = 2022 THEN amount END) AS `2022`,MAX(CASE WHEN year = 2023 THEN amount END) AS `2023`
      

   2. 拼接为查询语句

      SET @selectSql = CONCAT('SELECT id, ', @sql, ' FROM sales GROUP BY id');
      

      select @selectSql；
      运行结果：SELECT id, MAX(CASE WHEN year = 2021 THEN amount END) AS `2021`,MAX(CASE WHEN year = 2022 THEN amount END) AS `2022`,MAX(CASE WHEN year = 2023 THEN amount END) AS `2023` FROM sales GROUP BY id
      

   3. 执行语句

      PREPARE stmt FROM @selectSql ; -- 准备动态 SQL 语句。
      EXECUTE stmt; -- 执行已准备好的 SQL 语句。
      DEALLOCATE PREPARE stmt;-- 释放资源。