Mysql 列转行统计查询、行转列统计、行合计查询

最新推荐文章于 2024-02-01 17:18:00 发布

林中轩

最新推荐文章于 2024-02-01 17:18:00 发布

阅读量5.1k

点赞数

分类专栏：数据库

数据库专栏收录该内容

16 篇文章 0 订阅

订阅专栏

Mysql 列转行统计查询、行转列统计、行合计查询

 
      
  
     
 
           -- ---------------------------- 
          
 
           -- Table structure for `TabName` 
          
 
           -- ---------------------------- 
          
 
           DROP 
            TABLE 
           IF EXISTS `TabName`; 
          
 
           CREATE 
            TABLE 
           `TabName` ( 
          
 
              
           `Id`  
           int 
           (11)  
           NOT 
           NULL 
           AUTO_INCREMENT, 
          
 
              
           ` 
           Name 
           `  
           varchar 
           (20)  
           DEFAULT 
           NULL 
           , 
          
 
              
           ` 
           Date 
           `  
           date 
           DEFAULT 
           NULL 
           , 
          
 
              
           `Scount`  
           int 
           (11)  
           DEFAULT 
           NULL 
           , 
          
 
              
           PRIMARY 
           KEY 
           (`Id`) 
          
 
           ) ENGINE=InnoDB AUTO_INCREMENT=9  
           DEFAULT 
           CHARSET=utf8; 
          

              
          
 
           -- ---------------------------- 
          
 
           -- Records of TabName 
          
 
           -- ---------------------------- 
          
 
           INSERT 
            INTO 
           `TabName`  
           VALUES 
           ( 
           '1' 
           ,  
           '小说' 
           ,  
           '2013-09-01' 
           ,  
           '10000' 
           ); 
          
 
           INSERT 
            INTO 
           `TabName`  
           VALUES 
           ( 
           '2' 
           ,  
           '微信' 
           ,  
           '2013-09-01' 
           ,  
           '20000' 
           ); 
          
 
           INSERT 
            INTO 
           `TabName`  
           VALUES 
           ( 
           '3' 
           ,  
           '小说' 
           ,  
           '2013-09-02' 
           ,  
           '30000' 
           ); 
          
 
           INSERT 
            INTO 
           `TabName`  
           VALUES 
           ( 
           '4' 
           ,  
           '微信' 
           ,  
           '2013-09-02' 
           ,  
           '35000' 
           ); 
          
 
           INSERT 
            INTO 
           `TabName`  
           VALUES 
           ( 
           '5' 
           ,  
           '小说' 
           ,  
           '2013-09-03' 
           ,  
           '31000' 
           ); 
          
 
           INSERT 
            INTO 
           `TabName`  
           VALUES 
           ( 
           '6' 
           ,  
           '微信' 
           ,  
           '2013-09-03' 
           ,  
           '36000' 
           ); 
          
 
           INSERT 
            INTO 
           `TabName`  
           VALUES 
           ( 
           '7' 
           ,  
           '小说' 
           ,  
           '2013-09-04' 
           ,  
           '35000' 
           ); 
          
 
           INSERT 
            INTO 
           `TabName`  
           VALUES 
           ( 
           '8' 
           ,  
           '微信' 
           ,  
           '2013-09-04' 
           ,  
           '38000' 
           ); 
          

              
          

              
          
 
           -- ------------------------ 
          
 
           -- 查看数据 
          
 
           -- ------------------------ 
          
 
           SELECT  
            *  
           from 
           TabName ; 
          

 
           -- ------------------------ 
          
 
           -- 列转行统计数据 
          
 
           -- ------------------------ 
          
 
           SELECT 
            Date 
           , 
          
 
           MAX 
           ( 
           CASE 
           NAME 
           WHEN 
            '小说' 
           THEN 
           Scount  
           ELSE 
           0  
           END 
           ) 小说, 
          
 
           MAX 
           ( 
           CASE 
           NAME 
           WHEN 
            '微信' 
           THEN 
           Scount  
           ELSE 
           0  
           END 
           ) 微信  
          
 
           FROM 
            TabName   
          
 
           GROUP 
            BY 
           Date  
           <br><br> 
          

 
           -- ------------------------ 
          
           -- 行转列统计数据 
          
           -- ------------------------

 
           <br> 
           select 
          
 
                
           Date 
           , group_concat( 
           NAME 
           , 
           '总量:' 
           ,Scount)  
           as 
           b_str  
           from   
           TabName  
          
 
             
           group 
           by 
           Date 
          