比如数据库里面有这样的一张表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语句,如何实现
现在是想查找多天(上面是查的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