有一个表,每5秒写一次数据,要求统计一分钟内的 平均值,再统计每个小时内的前三个最大值,统计一分钟内的 平均值我能写出来查询语句是sql="select convert(char(16),[riqi],120)as riqi,avg(v)as v , avg(w)as w From ribao where riqi between '2010-12-16 00:00:00' and'2010-12-16 23:59:59' group by convert(char(16),[riqi],120)"但现在要在这个语句上增加统计每个小时内的前三个最大值的功能,我写的查询语句是'Sql = "select top 3 v ,w convert(char(13),[riqi],120) from (select convert(char(16),[riqi],120)as riqi,avg(v)as v , avg(w)as w From ribao where riqi between '2010-12-16 00:00:00' and'2010-12-16 23:59:59' group by convert(char(16),[riqi],120) ) a where convert(char(13),[riqi],120) between '2010-12-16 00:00:00' and'2010-12-16 23:59:59' group by convert(char(13),[riqi],120) order by v,w desc" 就出问题了,总是出现分组错误.请问语句该怎么写啊 ,我要得到的效果如下所示
v w
最大 第二大 第三大 最大 第二大 第三大
0:00-1:00
1:00-2:00
2:00-3:00
3:00-4:00
4:00-5:00
5:00-6:00
6:00-7:00
7:00-8:00
每分钟查询出来的平均值如下面的数据这个是11分的:
2010-12-16 11:43 0.00 0.00
2010-12-16 11:44 0.00 0.00
2010-12-16 11:45 0.00 0.00
2010-12-16 11:46 0.00 0.00
2010-12-16 11:47 29.25 29.25
2010-12-16 11:48 86.25 86.25
2010-12-16 11:49 143.50 143.50
2010-12-16 11:50 100.00 100.00
2010-12-16 11:51 56.50 56.50
2010-12-16 11:52 113.50 113.50
2010-12-16 11:53 170.92 170.92
2010-12-16 11:54 27.17 27.17
2010-12-16 11:55 84.42 84.42
2010-12-16 11:56 141.50 141.50
2010-12-16 11:57 98.25 98.25
2010-12-16 11:58 55.17 55.17
2010-12-16 11:59 99.33 99.33
这个是12分的:
2010-12-16 12:00 0.00 0.00
2010-12-16 12:01 140.08 140.08
2010-12-16 12:02 113.00 113.00
2010-12-16 12:03 53.00 53.00
2010-12-16 12:04 110.42 110.42
2010-12-16 12:05 167.42 167.42
2010-12-16 12:06 40.75 40.75
2010-12-16 12:07 81.25 81.25
2010-12-16 12:08 138.75 138.75
2010-12-16 12:09 98.00 98.00
2010-12-16 12:10 56.17 56.17
2010-12-16 12:11 110.82 110.82
2010-12-16 12:12 167.38 167.38
2010-12-16 12:13 43.73 43.73
2010-12-16 12:14 79.58 79.58
2010-12-16 12:15 102.00 102.00
2010-12-16 12:16 102.00 102.00
2010-12-16 12:17 102.00 102.00
2010-12-16 12:18 102.00 102.00
2010-12-16 12:19 102.00 102.00
按小时分组查询出一天内每个小时的前三个最大数据,谢谢
v w
最大 第二大 第三大 最大 第二大 第三大
0:00-1:00
1:00-2:00
2:00-3:00
3:00-4:00
4:00-5:00
5:00-6:00
6:00-7:00
7:00-8:00
每分钟查询出来的平均值如下面的数据这个是11分的:
2010-12-16 11:43 0.00 0.00
2010-12-16 11:44 0.00 0.00
2010-12-16 11:45 0.00 0.00
2010-12-16 11:46 0.00 0.00
2010-12-16 11:47 29.25 29.25
2010-12-16 11:48 86.25 86.25
2010-12-16 11:49 143.50 143.50
2010-12-16 11:50 100.00 100.00
2010-12-16 11:51 56.50 56.50
2010-12-16 11:52 113.50 113.50
2010-12-16 11:53 170.92 170.92
2010-12-16 11:54 27.17 27.17
2010-12-16 11:55 84.42 84.42
2010-12-16 11:56 141.50 141.50
2010-12-16 11:57 98.25 98.25
2010-12-16 11:58 55.17 55.17
2010-12-16 11:59 99.33 99.33
这个是12分的:
2010-12-16 12:00 0.00 0.00
2010-12-16 12:01 140.08 140.08
2010-12-16 12:02 113.00 113.00
2010-12-16 12:03 53.00 53.00
2010-12-16 12:04 110.42 110.42
2010-12-16 12:05 167.42 167.42
2010-12-16 12:06 40.75 40.75
2010-12-16 12:07 81.25 81.25
2010-12-16 12:08 138.75 138.75
2010-12-16 12:09 98.00 98.00
2010-12-16 12:10 56.17 56.17
2010-12-16 12:11 110.82 110.82
2010-12-16 12:12 167.38 167.38
2010-12-16 12:13 43.73 43.73
2010-12-16 12:14 79.58 79.58
2010-12-16 12:15 102.00 102.00
2010-12-16 12:16 102.00 102.00
2010-12-16 12:17 102.00 102.00
2010-12-16 12:18 102.00 102.00
2010-12-16 12:19 102.00 102.00
按小时分组查询出一天内每个小时的前三个最大数据,谢谢
insert into ribao select '2010-12-16 11:43',0.00,0.00
insert into ribao select '2010-12-16 11:44',0.00,0.00
insert into ribao select '2010-12-16 11:45',0.00,0.00
insert into ribao select '2010-12-16 11:46',0.00,0.00
insert into ribao select '2010-12-16 11:47',29.25,29.25
insert into ribao select '2010-12-16 11:48',86.25,86.25
insert into ribao select '2010-12-16 11:49',143.50,143.50
insert into ribao select '2010-12-16 11:50',100.00,100.00
insert into ribao select '2010-12-16 11:51',56.50,56.50
insert into ribao select '2010-12-16 11:52',113.50,113.50
insert into ribao select '2010-12-16 11:53',170.92,170.92
insert into ribao select '2010-12-16 11:54',27.17,27.17
insert into ribao select '2010-12-16 11:55',84.42,84.42
insert into ribao select '2010-12-16 11:56',141.50,141.50
insert into ribao select '2010-12-16 11:57',98.25,98.25
insert into ribao select '2010-12-16 11:58',55.17,55.17
insert into ribao select '2010-12-16 11:59',99.33,99.33
insert into ribao select '2010-12-16 12:00',0.00,0.00
insert into ribao select '2010-12-16 12:01',140.08,140.08
insert into ribao select '2010-12-16 12:02',113.00,113.00
insert into ribao select '2010-12-16 12:03',53.00,53.00
insert into ribao select '2010-12-16 12:04',110.42,110.42
insert into ribao select '2010-12-16 12:05',167.42,167.42
insert into ribao select '2010-12-16 12:06',40.75,40.75
insert into ribao select '2010-12-16 12:07',81.25,81.25
insert into ribao select '2010-12-16 12:08',138.75,138.75
insert into ribao select '2010-12-16 12:09',98.00,98.00
insert into ribao select '2010-12-16 12:10',56.17,56.17
insert into ribao select '2010-12-16 12:11',110.82,110.82
insert into ribao select '2010-12-16 12:12',167.38,167.38
insert into ribao select '2010-12-16 12:13',43.73,43.73
insert into ribao select '2010-12-16 12:14',79.58,79.58
insert into ribao select '2010-12-16 12:15',102.00,102.00
insert into ribao select '2010-12-16 12:16',102.00,102.00
insert into ribao select '2010-12-16 12:17',102.00,102.00
insert into ribao select '2010-12-16 12:18',102.00,102.00
insert into ribao select '2010-12-16 12:19',102.00,102.00
go
select a.riqi,a.v,b.w from(
select * from(
select rank()over(partition by convert(varchar(13),riqi,120) order by v desc) as rk,convert(varchar(13),riqi,120) as riqi,v,w from ribao
)t where rk<=3)a inner join(
select * from(
select rank()over(partition by convert(varchar(13),riqi,120) order by w desc) as rk,convert(varchar(13),riqi,120) as riqi,v,w from ribao
)t where rk<=3)b on a.rk=b.rk and a.riqi=b.riqi
go
drop table ribao
/*
riqi v w
------------- --------------------------------------- ---------------------------------------
2010-12-16 11 170.92 170.92
2010-12-16 11 143.50 143.50
2010-12-16 11 141.50 141.50
2010-12-16 12 167.42 167.42
2010-12-16 12 167.38 167.38
2010-12-16 12 140.08 140.08(6 行受影响)
*/
select riqi , v , w from
(
select m.* , px = (select count(1) from
(
select convert(char(16),[riqi],120) as riqi,avg(v) as v , avg(w) as w From ribao where riqi between '2010-12-16 00:00:00' and'2010-12-16 23:59:59' group by convert(char(16),[riqi],120)
) n where left(m.riqi,13) = left(n.riqi,13) and (n.v > m.v or (n.v = m.v and n.w > m.w) or (n.v = m.v and n.w = m.w and n.riqi < m.riqi))) + 1 from
(
select convert(char(16),[riqi],120) as riqi,avg(v) as v , avg(w) as w From ribao where riqi between '2010-12-16 00:00:00' and'2010-12-16 23:59:59' group by convert(char(16),[riqi],120)
) m
) t
where px <= 3--sql 2005
select riqi , v , m from
(
select m.* , px = row_number() over(partition by left(m.riqi,13) order by m.v desc , m.w desc , m.riqi asc) from
(
select convert(char(16),[riqi],120) as riqi,avg(v) as v , avg(w) as w From ribao where riqi between '2010-12-16 00:00:00' and'2010-12-16 23:59:59' group by convert(char(16),[riqi],120)
) m
) t
where px <= 3
create table ribao(riqi datetime,v decimal(18,2),w decimal(18,2))
insert into ribao select '2010-12-16 11:43',0.00,0.00
insert into ribao select '2010-12-16 11:44',0.00,0.00
insert into ribao select '2010-12-16 11:45',0.00,0.00
insert into ribao select '2010-12-16 11:46',0.00,0.00
insert into ribao select '2010-12-16 11:47',29.25,29.25
insert into ribao select '2010-12-16 11:48',86.25,86.25
insert into ribao select '2010-12-16 11:49',143.50,143.50
insert into ribao select '2010-12-16 11:50',100.00,100.00
insert into ribao select '2010-12-16 11:51',56.50,56.50
insert into ribao select '2010-12-16 11:52',113.50,113.50
insert into ribao select '2010-12-16 11:53',170.92,170.92
insert into ribao select '2010-12-16 11:54',27.17,27.17
insert into ribao select '2010-12-16 11:55',84.42,84.42
insert into ribao select '2010-12-16 11:56',141.50,141.50
insert into ribao select '2010-12-16 11:57',98.25,98.25
insert into ribao select '2010-12-16 11:58',55.17,55.17
insert into ribao select '2010-12-16 11:59',99.33,99.33
insert into ribao select '2010-12-16 12:00',0.00,0.00
insert into ribao select '2010-12-16 12:01',140.08,140.08
insert into ribao select '2010-12-16 12:02',113.00,113.00
insert into ribao select '2010-12-16 12:03',53.00,53.00
insert into ribao select '2010-12-16 12:04',110.42,110.42
insert into ribao select '2010-12-16 12:05',167.42,167.42
insert into ribao select '2010-12-16 12:06',40.75,40.75
insert into ribao select '2010-12-16 12:07',81.25,81.25
insert into ribao select '2010-12-16 12:08',138.75,138.75
insert into ribao select '2010-12-16 12:09',98.00,98.00
insert into ribao select '2010-12-16 12:10',56.17,56.17
insert into ribao select '2010-12-16 12:11',110.82,110.82
insert into ribao select '2010-12-16 12:12',167.38,167.38
insert into ribao select '2010-12-16 12:13',43.73,43.73
insert into ribao select '2010-12-16 12:14',79.58,79.58
insert into ribao select '2010-12-16 12:15',102.00,102.00
insert into ribao select '2010-12-16 12:16',102.00,102.00
insert into ribao select '2010-12-16 12:17',102.00,102.00
insert into ribao select '2010-12-16 12:18',102.00,102.00
insert into ribao select '2010-12-16 12:19',102.00,102.00
go--sql 2000
select riqi , v , w from
(
select m.* , px = (select count(1) from
(
select convert(char(16),[riqi],120) as riqi,avg(v) as v , avg(w) as w From ribao where riqi between '2010-12-16 00:00:00' and'2010-12-16 23:59:59' group by convert(char(16),[riqi],120)
) n where left(m.riqi,13) = left(n.riqi,13) and (n.v > m.v or (n.v = m.v and n.w > m.w) or (n.v = m.v and n.w = m.w and n.riqi < m.riqi))) + 1 from
(
select convert(char(16),[riqi],120) as riqi,avg(v) as v , avg(w) as w From ribao where riqi between '2010-12-16 00:00:00' and'2010-12-16 23:59:59' group by convert(char(16),[riqi],120)
) m
) t
where px <= 3
order by riqi , v desc, w desc/*
riqi v w
---------------- ---------------------------------------- ----------------------------------------
2010-12-16 11:49 143.500000 143.500000
2010-12-16 11:53 170.920000 170.920000
2010-12-16 11:56 141.500000 141.500000
2010-12-16 12:01 140.080000 140.080000
2010-12-16 12:05 167.420000 167.420000
2010-12-16 12:12 167.380000 167.380000（所影响的行数为 6 行）
*/drop table ribao--sql 2005
create table ribao(riqi datetime,v decimal(18,2),w decimal(18,2))
insert into ribao select '2010-12-16 11:43',0.00,0.00
insert into ribao select '2010-12-16 11:44',0.00,0.00
insert into ribao select '2010-12-16 11:45',0.00,0.00
insert into ribao select '2010-12-16 11:46',0.00,0.00
insert into ribao select '2010-12-16 11:47',29.25,29.25
insert into ribao select '2010-12-16 11:48',86.25,86.25
insert into ribao select '2010-12-16 11:49',143.50,143.50
insert into ribao select '2010-12-16 11:50',100.00,100.00
insert into ribao select '2010-12-16 11:51',56.50,56.50
insert into ribao select '2010-12-16 11:52',113.50,113.50
insert into ribao select '2010-12-16 11:53',170.92,170.92
insert into ribao select '2010-12-16 11:54',27.17,27.17
insert into ribao select '2010-12-16 11:55',84.42,84.42
insert into ribao select '2010-12-16 11:56',141.50,141.50
insert into ribao select '2010-12-16 11:57',98.25,98.25
insert into ribao select '2010-12-16 11:58',55.17,55.17
insert into ribao select '2010-12-16 11:59',99.33,99.33
insert into ribao select '2010-12-16 12:00',0.00,0.00
insert into ribao select '2010-12-16 12:01',140.08,140.08
insert into ribao select '2010-12-16 12:02',113.00,113.00
insert into ribao select '2010-12-16 12:03',53.00,53.00
insert into ribao select '2010-12-16 12:04',110.42,110.42
insert into ribao select '2010-12-16 12:05',167.42,167.42
insert into ribao select '2010-12-16 12:06',40.75,40.75
insert into ribao select '2010-12-16 12:07',81.25,81.25
insert into ribao select '2010-12-16 12:08',138.75,138.75
insert into ribao select '2010-12-16 12:09',98.00,98.00
insert into ribao select '2010-12-16 12:10',56.17,56.17
insert into ribao select '2010-12-16 12:11',110.82,110.82
insert into ribao select '2010-12-16 12:12',167.38,167.38
insert into ribao select '2010-12-16 12:13',43.73,43.73
insert into ribao select '2010-12-16 12:14',79.58,79.58
insert into ribao select '2010-12-16 12:15',102.00,102.00
insert into ribao select '2010-12-16 12:16',102.00,102.00
insert into ribao select '2010-12-16 12:17',102.00,102.00
insert into ribao select '2010-12-16 12:18',102.00,102.00
insert into ribao select '2010-12-16 12:19',102.00,102.00
go--sql 2005
select riqi , v , w from
(
select m.* , px = row_number() over(partition by left(m.riqi,13) order by m.v desc , m.w desc , m.riqi asc) from
(
select convert(char(16),[riqi],120) as riqi,avg(v) as v , avg(w) as w From ribao where riqi between '2010-12-16 00:00:00' and'2010-12-16 23:59:59' group by convert(char(16),[riqi],120)
) m
) t
where px <= 3
order by riqi , v desc, w desc/*
riqi v w
---------------- --------------------------------------- ---------------------------------------
2010-12-16 11:49 143.500000 143.500000
2010-12-16 11:53 170.920000 170.920000
2010-12-16 11:56 141.500000 141.500000
2010-12-16 12:01 140.080000 140.080000
2010-12-16 12:05 167.420000 167.420000
2010-12-16 12:12 167.380000 167.380000(6 行受影响)
*/drop table ribao
;with t as(
select datepart(hh,riqi) h,v,w,
dense_rank() over (partition by datepart(hh,riqi) order by v desc) v_rn,
dense_rank() over (partition by datepart(hh,riqi) order by w desc) w_rn
from ribao
where riqi between '2010-12-16 00:00:00' and'2010-12-16 23:59:59'
)
select h,
max(case when v_rn=1 then v end) [v1],
max(case when v_rn=2 then v end) [v2],
max(case when v_rn=3 then v end) [v3],
max(case when w_rn=1 then w end) [w1],
max(case when w_rn=2 then w end) [w2],
max(case when w_rn=3 then w end) [w3]
from t where v_rn<=3 or w_rn<=3
group by h;