解决方案 »
- 求助:mysql数字字符是否在一个数字字符串中的问题?
- oracle列转行的问题
- 如何更简便的找出最新值
- ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务
- oracle的ORA-24812 问题谁遇到过,高分求解决办法。
- 帮忙看看生成临时表的存贮的的参数问题
- 高分求助-只有控制文件和数据文件能否恢复数据库
- 请问Oracle中的存储过程的实际内容是放在哪个表中或什么地方啊?
- 请教一个SQL语句,一个表的列数据怎么转换为字段名出来?
- 带输出游标参数的存储过程调用两个带输出游标参数的存储过程,怎么写
- 【求助】derby在建表时如何插入当前时间
- oracle关于版本号比较的问题
oracle 行转列
max(decode())
max(case when )
pivot
With B As
(select 1 as kid,'1001' as code,3 as win,1 as lose,'2014年12月1日' as time from dual union all
select 2 as kid,'1001' as code,4 as win,0 as lose,'2014年10月1日' as time from dual union all
select 3 as kid,'1001' as code,5 as win,2 as lose,'2013年12月1日' as time from dual union all
select 4 as kid,'1002' as code,3 as win,0 as lose,'2014年12月1日' as time from dual union all
select 5 as kid,'1003' as code,1 as win,3 as lose,'2014年11月1日' as time from dual union all
select 6 as kid,'1003' as code,0 as win,5 as lose,'2013年12月1日' as time from dual union all
select 7 as kid,'1004' as code,5 as win,1 as lose,'2014年12月1日' as time from dual union all
select 8 as kid,'1005' as code,3 as win,2 as lose,'2014年12月1日' as time from dual union all
select 9 as kid,'1005' as code,6 as win,2 as lose,'2013年12月1日' as time from dual union all
select 10 as kid,'1005' as code,NULL as win,NULL as lose,NULL as time from dual)
Select m.name,m.age,m.gender
,sum(Decode(substr(m.time,1,4),'2013',m.win,0))As win2013
,sum(Decode(substr(m.time,1,4),'2013',m.lose,0)) As lose2013
,sum(Decode(substr(m.time,1,4),'2014',m.win,0)) As win2014
,sum(Decode(substr(m.time,1,4),'2014',m.lose,0)) As lose2014
,m.code
From
(Select t.*,b.win,b.lose From
(Select A.name,A.age,A.gender,A.code,
to_char(max(to_date(B.time,'YYYY"年"MM"月"DD"日"')),'YYYY"年"MM"月"DD"日"')As time
From (select 1 as id, '张三' as name, 22 as age, '男' as gender, '1001' as code from dual union all
select 2 as id, '李四' as name, 23 as age, '女' as gender, '1002' as code from dual union all
select 3 as id, '王五' as name, 19 as age, '男' as gender, '1003' as code from dual union all
select 4 as id, '赵六' as name, 17 as age, '男' as gender, '1004' as code from dual union all
select 5 as id, '刘二' as name, 25 as age, '男' as gender, '1005' as code from dual)A,B
Where B.time Is Not Null
And A.code=B.code
Group By A.name,A.age,A.gender,A.code,Substr(B.time,1,4) ) t,B
Where t.time=to_char(to_date(B.time,'YYYY"年"MM"月"DD"日"'),'YYYY"年"MM"月"DD"日"')
And t.code=B.code)m
Group By m.name,m.age,m.gender,m.code
Order By m.code;