比如数据库里面有这样的一张表table
地区 日期 0点数据 1点数据 2点数据 3点数据 ....
regid start_time a0 a1 a2 a3 ....查询多天的数据,得到这些天在这个点的最大的数据
select regid, max(a0), max(a1), max(a2) ...
from table
where to_char(start_time, 'yyyy-mm-dd hh24') in ('2009-12-01', '2009-12-02', '2009-12-03')
group by regid 然后在该行的下面一行union上这么一行数据
对应这个最大值是在哪一天的最终显示目的:reg1 12 32 42 64 ...
reg1 2009-12-03 2009-12-02 2009-12-02 2009-12-04 ...
reg2 42 71 62 65 ...
reg2 2009-12-01 2009-12-01 2009-12-02 2009-12-02 ...
reg3 12 56 72 44 ...
reg3 2009-12-02 2009-12-04 2009-12-02 2009-12-01 ...
.......不用存储过程,只能用sql语句,如何实现
地区 日期 0点数据 1点数据 2点数据 3点数据 ....
regid start_time a0 a1 a2 a3 ....查询多天的数据,得到这些天在这个点的最大的数据
select regid, max(a0), max(a1), max(a2) ...
from table
where to_char(start_time, 'yyyy-mm-dd hh24') in ('2009-12-01', '2009-12-02', '2009-12-03')
group by regid 然后在该行的下面一行union上这么一行数据
对应这个最大值是在哪一天的最终显示目的:reg1 12 32 42 64 ...
reg1 2009-12-03 2009-12-02 2009-12-02 2009-12-04 ...
reg2 42 71 62 65 ...
reg2 2009-12-01 2009-12-01 2009-12-02 2009-12-02 ...
reg3 12 56 72 44 ...
reg3 2009-12-02 2009-12-04 2009-12-02 2009-12-01 ...
.......不用存储过程,只能用sql语句,如何实现
解决方案 »
- 巨大的挑战-oracle 语句优化 数据量在300万左右 现执行要1小时-汗
- HQL 单表自关联问题
- 求一条关于时间条件的语句
- 文件实时复制到本地,并导入oracle数据库
- 《续二》各位高手帮我分析几道题,据说是某个公司的笔试题目。(感激!回复既有分数)
- oracle的客户端如何安装?急!
- 别用Oracle了 (欢迎拍砖)
- 我使用的是oracle8i中文版,但是PLSQL中的结果却出现乱码,请问如何解决?
- ORACLE在LINUX下怎么安装 急!! 谢谢!!!
- 要实现下面显示,用oracle或者是SQL Server 怎么写
- oracle分页如何提高效率!
- 到底要怎么跟蹤oracle執行的語句啊?
现在是想查找多天(上面是查的12月13号到12月15号的数据),找出这些天每个24小时最大的数据,并union上这个最大值是出现在哪一天
select regid,to_char(max(a0))a0,to_char(max(a1))a1,to_char(max(a2))a2,1 flag from table1
where start_time>=date'2009-12-1' and start_time<date'2009-12-4'
group by regid
union all
select regid,to_char(min(start_time)keep(dense_rank last order by a0),'yyyy-mm-dd'),
to_char(min(start_time)keep(dense_rank last order by a1),'yyyy-mm-dd'),
to_char(min(start_time)keep(dense_rank last order by a2),'yyyy-mm-dd'),
2
from table1
where start_time>=date'2009-12-1' and start_time<date'2009-12-4'
group by regid)
order by regid,flag
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
create table hz_24_test (
regid varchar(20) not null,
start_time date not null,
scan0 number(32, 3),
scan1 number(32, 3),
scan2 number(32, 3),
scan3 number(32, 3),
scan4 number(32, 3),
scan5 number(32, 3),
scan6 number(32, 3),
scan7 number(32, 3),
scan8 number(32, 3),
scan9 number(32, 3),
scan10 number(32, 3),
scan11 number(32, 3),
scan12 number(32, 3),
scan13 number(32, 3),
scan14 number(32, 3),
scan15 number(32, 3),
scan16 number(32, 3),
scan17 number(32, 3),
scan18 number(32, 3),
scan19 number(32, 3),
scan20 number(32, 3),
scan21 number(32, 3),
scan22 number(32, 3),
scan23 number(32, 3)
);
create unique index hz_24_test_idx
on hz_24_test(regid, start_time)
insert into hz_24_test values ('reg1', to_date('2009-12-12', 'yyyy-mm-dd'), 45, 54, 63, 63, 34, 63, 78, 33, 68, 23, 12, 87, 43 ,32, 67, 54, 32, 53, 57, 76, 38, 78, 87, 56);
insert into hz_24_test values ('reg2', to_date('2009-12-12', 'yyyy-mm-dd'), 15, 64, 65, 43, 32, 13, 73, 23, 65, 27, 18, 67, 53 ,72, 64, 53, 52, 33, 27, 36, 34, 28, 37, 46);
insert into hz_24_test values ('reg3', to_date('2009-12-12', 'yyyy-mm-dd'), 42, 53, 53, 33, 37, 53, 48, 83, 62, 33, 52, 47, 93 ,23, 64, 26, 43, 89, 90, 98, 76, 54, 34, 36);insert into hz_24_test values ('reg1', to_date('2009-12-13', 'yyyy-mm-dd'), 23, 65, 27, 18, 67, 63, 78, 33, 52, 47, 93, 34, 43 ,32, 67, 54, 32, 53, 57, 76, 38, 78, 87, 56);
insert into hz_24_test values ('reg2', to_date('2009-12-13', 'yyyy-mm-dd'), 54, 64, 65, 43, 32, 83, 62, 33, 52, 47, 18, 43, 89, 90, 98, 54, 23, 27, 36, 34, 28, 37, 46, 32);
insert into hz_24_test values ('reg3', to_date('2009-12-13', 'yyyy-mm-dd'), 73, 23, 65, 27, 53, 48, 83, 62, 33, 52, 47, 93, 23, 18, 43, 89, 90, 90, 28, 37, 46, 34, 36, 56)
select
regid, max(scan0), max(scan1), max(scan3), max(scan4), max(scan5), max(scan6), max(scan7), max(scan8), max(scan9), max(scan10), max(scan11), max(scan12), max(scan13), max(scan14), max(scan16), max(scan17), max(scan18), max(scan19), max(scan20), max(scan21), max(scan22), max(scan16), max(scan23)
from
hz_24_test
where
start_time in (to_date('2009-12-12', 'yyyy-mm-dd'), to_date('2009-12-13', 'yyyy-mm-dd'))
group by
regid
order by
regid在对应行的下一行union上这个点的最大数据是出自哪一天的
(
select regid ,substr(scan0,1,2),substr(scan1,1,2),substr(scan8,1,2) from
(
select regid,
max(scan0+to_number(to_char(a.start_time,'yyyymmdd'))/100000000) as scan0,
max(scan1+to_number(to_char(a.start_time,'yyyymmdd'))/100000000) as scan1,
max(scan8+to_number(to_char(a.start_time,'yyyymmdd'))/100000000) as scan8
from hz_24_test a
group by regid
)
union all
select regid ,substr(scan0,4),substr(scan1,4),substr(scan8,4) from
(
select regid,
max(scan0+to_number(to_char(a.start_time,'yyyymmdd'))/100000000) as scan0,
max(scan1+to_number(to_char(a.start_time,'yyyymmdd'))/100000000) as scan1,
max(scan8+to_number(to_char(a.start_time,'yyyymmdd'))/100000000) as scan8
from hz_24_test a
group by regid
)
) order by regid
REGID SUBSTR(SCAN0,1,2) SUBSTR(SCAN1,1,2) SUBSTR(SCAN8,1,2)
1 reg1 45 65 68
2 reg1 20091212 20091213 20091212
3 reg2 54 64 65
4 reg2 20091213 20091213 20091212
5 reg3 73 53 62
6 reg3 20091213 20091212 20091212