SQL行轉列、列轉行

SQL行轉列、列轉行java

這個主題仍是比較常見的，行轉列主要適用於對數據做聚合統計，如統計某類目的商品在某個時間區間的銷售狀況。列轉行問題一樣也很常見。mysql

1、整理測試數據sql

create table wyc_test(
	id int(32) not null auto_increment,
	name varchar(80) default null,
	date date default null,
	scount int(32),
	primary key (id)
);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (1,'小說','2013-09-01',10000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (2,'微信','2013-09-01',20000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (3,'小說','2013-09-02',30000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (4,'微信','2013-09-02',35000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (5,'小說','2013-09-03',31000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (6,'微信','2013-09-03',36000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (7,'小說','2013-09-04',35000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (8,'微信','2013-09-04',38000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (9,'小說','2013-09-01',80000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (10,'微信','2013-09-01',70000);

2、行轉列微信

主要思路是分組後使用case進行條件判斷處理函數

#行轉列
select 
    a.date,
    sum(case a.name
        when '小說' then a.scount
        else 0
    end) 'sum_小說',
    max(case a.name
        when '小說' then a.scount
        else 0
    end) 'max_小說',
    sum(case a.name
        when '微信' then a.scount
        else 0
    end) '微信',
    max(case a.name
        when '小說' then a.scount
        else 0
    end) 'max_微信'
from
    wyc_test a
group by date;

結果：測試

3、列轉行code

主要思路也是分組後使用caseblog

#列轉行
select 
    a.date,
    concat('小說:',
            cast(sum(case a.name
                    when '小說' then a.scount
                    else 0
                end)
                as char),
            '微信',
            cast(sum(case a.name
                    when '微信' then a.scount
                    else 0
                end)
                as char)) as 'str'
from
    wyc_test a
group by a.date;
#列轉行
#1.使用mysql提供的函數分組
select a.date,group_concat(a.name,'總量：', a.scount) from wyc_test a group by a.date,a.name;
#2.使用mysql提供的函數分組
select a.date,a.name, group_concat(a.name, '總量：', a.scount) from wyc_test a group by a.date,a.name;
#3.普通group結合字符串拼接
SELECT 
    a.date,
    concat('小說總量：',
            cast(sum(case a.name
                    when '小說' then a.scount
                    else 0
                end)
                as char)) as '小說',
    concat('微信總量：',
            cast(sum(case a.name
                    when '微信' then a.scount
                    else 0
                end)
                as char)) as '微信'
from
    wyc_test a
group by a.date;

結果：
rem

4、列轉行詳解
 
1.一、初始測試數據
        表結構：TEST_TB_GRADE2
Sql代碼 
create table TEST_TB_GRADE2 
( 
  ID         NUMBER(10) not null, 
  USER_NAME  VARCHAR2(20 CHAR), 
  CN_SCORE   FLOAT, 
  MATH_SCORE FLOAT, 
  EN_SCORE   FLOAT 
) 
 
        初始數據以下圖：字符串

       
 
1.二、 若是須要實現以下的查詢效果圖：

                      
 
這就是最多見的列轉行，主要原理是利用SQL裏面的union，具體的sql語句以下：
Sql代碼 
select user_name, '語文' COURSE , CN_SCORE as SCORE from test_tb_grade2  
union select user_name, '數學' COURSE, MATH_SCORE as SCORE from test_tb_grade2  
union select user_name, '英語' COURSE, EN_SCORE as SCORE from test_tb_grade2  
order by user_name,COURSE  
 
 也能夠利用【 insert all into ... select 】來實現，首先須要先建一個表TEST_TB_GRADE3：
Sql代碼 
create table TEST_TB_GRADE3   
    (  
      USER_NAME VARCHAR2(20 CHAR),   
      COURSE    VARCHAR2(20 CHAR),   
      SCORE     FLOAT   
    )   
 再執行下面的sql：
 
Sql代碼 
insert all 
into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '語文', CN_SCORE) 
into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '數學', MATH_SCORE) 
into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '英語', EN_SCORE) 
select user_name, CN_SCORE, MATH_SCORE, EN_SCORE from test_tb_grade2; 
commit; 

 別忘記commit操做，而後再查詢TEST_TB_GRADE3，發現表中的數據就是列轉成行了。