精华内容
下载资源
问答
  • mysql动态行转列

    2016-11-04 14:13:22
    mysql动态行转列的例子
  • MySQL动态行转列

    2020-08-23 18:53:39
    MySQL动态行转列 通过max行转列不是动态的, 你还是要知道所有列名才可以 root@localhost 17:46:56 [fanboshi]> select * from t3; +----+---------+------+-----------+ | id | title | env | progress | +----+-...

    MySQL动态行转列

    通过max行转列不是动态的, 你还是要知道所有列名才可以

    root@localhost 17:46:56 [fanboshi]> select * from t3;
    +----+---------+------+-----------+
    | id | title   | env  | progress  |
    +----+---------+------+-----------+
    |  1 | 工单1   |    1 | 完成      |
    |  2 | 工单1   |    2 | 完成      |
    |  3 | 工单1   |    3 | 待审核    |
    |  4 | 工单2   |    1 | 待审核    |
    +----+---------+------+-----------+
    4 rows in set (0.00 sec)
    
    root@localhost 17:48:20 [fanboshi]> select title,max(if(env=1,progress,-1)) 'RC',max(if(env=2,progress,-1)) 'Stage',max(if(env=3,progress,-1)) 'Prod' from t3 group by title;
    +---------+-----------+--------+-----------+
    | title   | RC        | Stage  | Prod      |
    +---------+-----------+--------+-----------+
    | 工单1   | 完成      | 完成   | 待审核    |
    | 工单2   | 待审核    | -1     | -1        |
    +---------+-----------+--------+-----------+
    2 rows in set (0.00 sec)
    

    oracle有pivot函数可以解决这个问题,MySQL并没有这样的函数, 一种退而求其次的办法是使用JSON_OBJECTAGG

    > select title,JSON_OBJECTAGG(env,progress) val from t3 group by title;
    +---------+--------------------------------------------------+
    | title   | val                                              |
    +---------+--------------------------------------------------+
    | 工单1   | {"1": "完成", "2": "完成", "3": "待审核"}        |
    | 工单2   | {"1": "待审核"}                                  |
    +---------+--------------------------------------------------+
    

    然后再程序循环val中的key,value自己做处理

    使用prepare statement可以实现, 本例需要构造一个env值与env名字的对应表

    root@localhost 17:42:07 [fanboshi]> select * from t_env;
    +----+--------+----------+
    | id | env_id | env_name |
    +----+--------+----------+
    |  1 |      1 | RC       |
    |  2 |      2 | Stage    |
    |  3 |      3 | Prod     |
    +----+--------+----------+
    3 rows in set (0.00 sec)
    

    然后

    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(IF(pa.env = ''',
          env,
          ''', pa.progress, -1)) AS ',
          "'",e.env_name,"'"
        )
      ) INTO @sql
    FROM t3,(select @sql:= NULL) tmp, t_env e where env = e.env_id;
    
    root@localhost 17:44:40 [fanboshi]> select @sql;
    +---------------------------------------------------------------------------------------------------------------------------------------------------------+
    | @sql                                                                                                                                                    |
    +---------------------------------------------------------------------------------------------------------------------------------------------------------+
    | MAX(IF(pa.env = '1', pa.progress, NULL)) AS 'RC',MAX(IF(pa.env = '2', pa.progress, NULL)) AS 'Stage',MAX(IF(pa.env = '3', pa.progress, NULL)) AS 'Prod' |
    +---------------------------------------------------------------------------------------------------------------------------------------------------------+
    
    
    select @sql:=CONCAT('SELECT pa.title
                        , ', @sql, ' 
                       FROM t3 pa
                       GROUP BY pa.title');
                       
    +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | @sql:=CONCAT('SELECT pa.title
                        , ', @sql, ' 
                       FROM t3 pa
                       GROUP BY pa.title')                                                                                                                        |
    +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | SELECT pa.title
                        , MAX(IF(pa.env = '1', pa.progress, -1)) AS 'RC',MAX(IF(pa.env = '2', pa.progress, -1)) AS 'Stage',MAX(IF(pa.env = '3', pa.progress, -1)) AS 'Prod' 
                       FROM t3 pa
                       GROUP BY pa.title |
    +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    其实这里已经把拼接好的sql查出来了, 程序直接拿着个结果去执行应该也是可以的, 可能不用后面在使用prepare statement了
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    +---------+-----------+--------+-----------+
    | title   | RC        | Stage  | Prod      |
    +---------+-----------+--------+-----------+
    | 工单1   | 完成      | 完成   | 待审核    |
    | 工单2   | 待审核    | -1     | -1        |
    +---------+-----------+--------+-----------+
    DEALLOCATE PREPARE stmt;
    

    那么基于上面的思路, 我寻思还用得着prepare statement吗, 直接拼个sql不就完事了吗, 用SQL造SQL

    SELECT 
        CONCAT('SELECT pa.title, ',
                GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(pa.env = \'',
                            env,
                            '\', pa.progress, -1)) AS ',
                            '\'',
                            e.env_name,
                            '\'')),
                ' FROM t3 pa group by pa.title') final_sql
    FROM
        t3,
        t_env e
    WHERE
        env = e.env_id;
    
    +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | final_sql                                                                                                                                                                                       |
    +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | SELECT pa.title, MAX(IF(pa.env = '1', pa.progress, -1)) AS 'RC',MAX(IF(pa.env = '2', pa.progress, -1)) AS 'Stage',MAX(IF(pa.env = '3', pa.progress, -1)) AS 'Prod' FROM t3 pa group by pa.title |
    +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    > SELECT pa.title, MAX(IF(pa.env = '1', pa.progress, -1)) AS 'RC',MAX(IF(pa.env = '2', pa.progress, -1)) AS 'Stage',MAX(IF(pa.env = '3', pa.progress, -1)) AS 'Prod' FROM t3 pa group by pa.title;
    +---------+-----------+--------+-----------+
    | title   | RC        | Stage  | Prod      |
    +---------+-----------+--------+-----------+
    | 工单1   | 完成      | 完成   | 待审核    |
    | 工单2   | 待审核    | -1     | -1        |
    +---------+-----------+--------+-----------+
    2 rows in set (0.00 sec)
    
    
    

    参考:

    https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns

    展开全文
  • mysql 动态行转列

    2019-04-27 22:20:00
    mysql 动态行转列 表结果:create table user( id int , username varchar(10), create_time datetime, type int ) insert into user (`id`,`username`,`create_time`,`type`) values...
    表结果:
    create
    table user( id int , username varchar(10), create_time datetime, type int ) insert into user (`id`,`username`,`create_time`,`type`) values ('1','张三','2019-04-25 14:36:41','1'), ('1','张三','2019-04-25 14:37:10','2'), ('1','张三','2019-04-25 14:37:26','1'), ('1','张三','2019-04-27 14:38:08','1'), ('1','张三','2019-04-26 14:36:02','1'), ('1','张三','2019-04-26 14:36:19','2'), ('1','李四','2019-04-25 14:39:50','1'), ('1','李四','2019-04-26 14:39:49','1'), ('1','李四','2019-04-26 14:39:48','1'), ('1','李四','2019-04-27 14:39:48','1'), ('1','李四','2019-04-27 14:39:44','1'), ('1','王五','2019-04-25 14:36:41','1');

    数据表:

    静态用 case when then 

    mysql  只能,动态用存储 ,

    SQL Server中行列转换用 Pivot UnPivot

    结果:

    sql:

    SET @str=''; 
    set @sql_temp=''; 
    SELECT @str:=CONCAT(@str,'sum(IF(left(create_time,10)=\'',create_time,'\'',',1,0)',') AS ' ,'''',create_time,'''',',') as aa into @sql_temp FROM (
    SELECT left(create_time,10) as create_time  FROM `user` group by left(create_time,10)) A order by length(aa) desc limit 1; 
    SET @result_sql=CONCAT('SELECT  user.username, ',LEFT(@sql_temp,char_length(@sql_temp)-1),'  FROM user GROUP BY username' ); 
    PREPARE stmt FROM @result_sql; 
    EXECUTE stmt ; 
    deallocate prepare stmt

     

    posted on 2019-04-27 22:20 zhouixi 阅读(...) 评论(...) 编辑 收藏

    转载于:https://www.cnblogs.com/1-Admin/p/10780745.html

    展开全文
  • MySql动态行转列

    2019-11-20 14:27:29
    动态行转列就需要涉及到存储过程。 需要统计每个班下了多少套秋装和冬装,但由于有的学校可能还下了夏装,每个学校下的款式不一样,所以这个列是动态的 所以建一个视图 如图: 需要把这个行转为列 如下存储...

    静态行转列简单一点。动态行转列就需要涉及到存储过程。

    需要统计每个班下了多少套秋装和冬装,但由于有的学校可能还下了夏装,每个学校下的款式不一样,所以这个列是动态的

    所以建一个视图

    如图:

    需要把这个行转为列

    如下存储函数

    BEGIN
    SET @sql = NULL;
    SET @ssq = NULL;
    SELECT 
        GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(sx.sku_id = \'',
                    sx.sku_id,
                    '\', sx.num, 0)) AS \'',
                    sx.sku_name,
                    '\' '))
    INTO @sql FROM
        forskuview sx WHERE sx.main_id=order_id;
     
    SET @ssq= CONCAT(' SELECT sx.GradeName as "年级",sx.ClassName as "班级",',@sql,' from forskuview sx where sx.main_id=\'',order_id,'\' GROUP BY sx.GradeName,sx.ClassName order by (sx.GradeID+0),(sx.ClassName+0)');
    
    PREPARE stmt FROM @ssq;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    END

    运行结果

    展开全文
  • Mysql动态行转列

    2021-04-13 18:11:16
    拼接的过程就是要动态找出数据里的所有,然后拼成SQL SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'Max(IF(a.project = ''', a.project, ''', a.score, 0)) AS ''', a.project, '''' ) ) ...

    初始数据

    初始数据

    期望数据

    期望数据

    实现原理

    动态拼接执行的SQL语句,先把要执行的语句拼在变量里,之后执行。
    拼接的过程就是要动态找出数据里的所有列,然后拼成SQL

    SET @sql = NULL;
    SELECT
     GROUP_CONCAT(DISTINCT
      CONCAT(
       'Max(IF(a.project = ''',
       a.project,
       ''', a.score, 0)) AS ''',
       a.project, ''''
      )
     ) INTO @sql
    FROM student_score a;
     
    SET @sql = CONCAT('Select name,', @sql, 
                ' From student_score a Group by a.name' );
    PREPARE stmt FROM @sql;  -- 动态生成脚本
    EXECUTE stmt;            -- 动态执行脚本
    DEALLOCATE PREPARE stmt;
    
    
    -- 查看拼接的 SQL
    select @sql;
    -- 拼接完成的语句(实际执行的脚本)
    Select name,
    Max(IF(a.project = '语文', a.score, 0)) AS '语文',
    Max(IF(a.project = '数学', a.score, 0)) AS '数学',
    Max(IF(a.project = '英语', a.score, 0)) AS '英语',
    Max(IF(a.project = '物理', a.score, 0)) AS '物理' 
    From student_score a Group by a.name;
    
    
    展开全文
  • MYSQL动态行转列

    2018-11-02 23:42:45
    自:https://blog.csdn.net/kongxingxing/article/details/53168506  DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `id` int(11) NOT NULL AUTO_INCREMENT, `class` varchar(255) DEFAULT NULL, `s...
  • MySQL 动态行转列

    千次阅读 2015-06-29 10:43:04
    CREATE TABLE `tbl01` (  `id` INT(11) DEFAULT NULL,  `kemu` VARCHAR(20) COLLATE utf8_unicode_ci DEFAULT NULL ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci INSERT INTO tbl01(id, k
  • table_schema=$schema_nameandtable_name=$table_nameinto$table_rows;--Checkwhetherthetableexistsornotdroptableifexiststest.temp;createtableifnotexiststest.temp(`1`varchar(255)no...
  • Pivot Table Using MySQL - A Complete Guide | WebDevZoomhttp://webdevzoom.com/pivot-table-using-mysql/Cross-Tabulation (Pivot Tables) with MySQL - CodeProjecthttps://...

空空如也

空空如也

1 2 3 4 5 ... 13
收藏数 250
精华内容 100
关键字:

mysql动态行转列

mysql 订阅