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,发现表中的数据就是列转成行了。