oracle查询问题（牛人请进，庸人看热闹也行。欢迎各种类型人士参观）
表A
date co2
2012-07-01 01 4
2012-07-02 02 3
......直到23点
2012-07-02 01 5
要求达到把每天的数据变成一行，查询每月数据（即把列变行，交叉表）
例如
2012-07-01 01的co2的值 2012-07-01 02的co2的值 ....2012-07-01 23
4 3 //显示7月1号每小时的co2的值
//显示7月2号每小时co2的值
现在问题来了我能查询7月1号到4号的4条记录，可是到5号就不行了。sql太长了。
所以请高手写个少点的牛X的。
感谢各位。
给点启发。。思路
[最优解释]
下班回家，帮你顶。
[其他解释]
这个可以。
select date,max(col2)
from
(select date,case substr(col2,-2,2)
when '01' then col2
when '02' then col2
....
when '23' then col2
else 0 end as col2
from t)
group by date;
[其他解释]
with test as (
select '2012-10-1' as str1, '11' as str2 from dual
union all
select '2012-10-2' as str1, '12' as str2 from dual
union all
select '2012-10-3' as str1, '13' as str2 from dual
union all
select '2012-10-4' as str1, '14' as str2 from dual
union all
select '2012-10-5' as str1, '15' as str2 from dual
union all
select '2012-11-1' as str1, '12' as str2 from dual
union all
select '2012-11-2' as str1, '22' as str2 from dual
union all
select '2012-11-3' as str1, '32' as str2 from dual
union all
select '2012-11-4' as str1, '42' as str2 from dual
union all
select '2012-11-5' as str1, '52' as str2 from dual
)
select ym, d1, d2, d3, d4, d5
from (SELECT rn,
ym,
str2 as d1,
lead(str2, 1) over(partition by ym order by dd) as d2,
lead(str2, 2) over(partition by ym order by dd) as d3,
lead(str2, 3) over(partition by ym order by dd) as d4,
lead(str2, 4) over(partition by ym order by dd) as d5
from (select row_number() over(partition by to_char(to_date(str1, 'yyyy-mm-dd'), 'yyyy-mm') order by to_char(to_date(str1, 'yyyy-mm-dd'), 'dd')) as rn,
to_char(to_date(str1, 'yyyy-mm-dd'), 'yyyy-mm') as ym,
to_char(to_date(str1, 'yyyy-mm-dd'), 'dd') as dd,
str2
from test))
where rn = 1
不知道符合不符合要求
[其他解释]
with t as (
select to_date('2012-07-01 01','yyyy-mm-dd hh24') col1, 4 col2 from dual union all
select to_date('2012-07-01 02','yyyy-mm-dd hh24') col1, 3 col2 from dual union all
select to_date('2012-07-01 03','yyyy-mm-dd hh24') col1, 2 col2 from dual union all
select to_date('2012-07-02 01','yyyy-mm-dd hh24') col1, 4 col2 from dual union all
select to_date('2012-07-02 02','yyyy-mm-dd hh24') col1, 2 col2 from dual union all
select to_date('2012-07-02 03','yyyy-mm-dd hh24') col1, 3 col2 from dual union all
select to_date('2012-07-03 01','yyyy-mm-dd hh24') col1, 4 col2 from dual union all
select to_date('2012-07-03 02','yyyy-mm-dd hh24') col1, 5 col2 from dual union all
select to_date('2012-07-03 03','yyyy-mm-dd hh24') col1, 6 col2 from dual)
select to_char(col1,'yyyy-mm') col1, wmsys.wm_concat(col2) from t group by to_char(col1,'yyyy-mm');
[其他解释]
原来是时间
with test as (
select '2012-10-1 01' as str1, '11' as str2 from dual
union all
select '2012-10-1 02' as str1, '12' as str2 from dual
union all
select '2012-10-1 03' as str1, '13' as str2 from dual
union all
select '2012-10-1 04' as str1, '14' as str2 from dual
union all
select '2012-10-1 05' as str1, '15' as str2 from dual
union all
select '2012-10-2 01' as str1, '12' as str2 from dual
union all
select '2012-10-2 02' as str1, '22' as str2 from dual
union all
select '2012-10-2 03' as str1, '32' as str2 from dual
union all
select '2012-10-2 04' as str1, '42' as str2 from dual
union all
select '2012-10-2 05' as str1, '52' as str2 from dual
)
select ymd, d1, d2, d3, d4, d5
from (SELECT rn,
ymd,
str2 as d1,
lead(str2, 1) over(partition by ymd order by hh) as d2,
lead(str2, 2) over(partition by ymd order by hh) as d3,
lead(str2, 3) over(partition by ymd order by hh) as d4,
lead(str2, 4) over(partition by ymd order by hh) as d5
from (select row_number() over(partition by to_char(to_date(str1, 'yyyy-mm-dd hh'), 'yyyy-mm-dd') order by to_char(to_date(str1, 'yyyy-mm-dd hh'), 'hh')) as rn,
to_char(to_date(str1, 'yyyy-mm-dd hh'), 'yyyy-mm-dd') as ymd,
to_char(to_date(str1, 'yyyy-mm-dd hh'), 'hh') as hh,
str2
from test))
where rn = 1
12012-10-011112131415
22012-10-021222324252
[其他解释]
楼主你自己写的语句发出来看看，为什么能查询7月1号到4号的4条记录，可是到5号就不行了
[其他解释]
5号为什么不行？
[其他解释]
楼上的人写的很厉害！佩服
[其他解释]
谢谢，大家的提醒，问题已解决了。是时间的排序有问题，
还有不能用uinon，否则我那程序不是有写600多行了。那更不行