表
select * from test 结果如下:name city_name scan_start_time s_hour value
CDRNC01 常市 2011-5-2 17:00 17 2822680.5
CDRNC01 常市 2011-5-2 9:00 9 1805635
CDRNC01 常市 2011-5-1 12:00 12 2209981.125
CDRNC01 常市 2011-5-1 17:00 17 2925668.75
CDRNC01 常市 2011-5-1 3:00 3 249131
CSRNC05 天区 2011-5-1 0 204877610
CSRNC05 天区 2011-5-2 19:00 19 17483989.82
CSRNC05 天区 2011-5-2 21:00 21 20235228.57
CSRNC06 开区 2011-5-1 14:00 14 7548153.242
CSRNC06 开区 2011-5-1 18:00 18 6533271.611
CSRNC06 开区 2011-5-1 2:00 2 2497404.404
CSRNC06 开区 2011-5-1 4:00 4 1080070.781
CSRNC06 开区 2011-5-2 16:00 16 7427590.879
CSRNC06 开区 2011-5-2 17:00 17 8141454.531实现效果(每个城市下面value值最大的记录):
CDRNC01 常市 2011-5-1 12 2209981.125
CSRNC05 天区 2011-5-1 0 204877610
CSRNC06 开区 2011-5-1 14 7548153.242
CSRNC06 开福区 2011-5-2 17 8141454.531
或
CDRNC01 常市 2011-5-1 12:00 12 2209981.125
CSRNC05 天区 2011-5-1 0 204877610
CSRNC06 开区 2011-5-1 14:00 14 7548153.242
CSRNC06 开福区 2011-5-2 17:00 17 8141454.531现在知道一种sql 如下:
select a.* from testa,(select city_name,max(value) from test group by city_name,day(scan_start_time)) b where a.city_name=b.city_name and a.value=b.value大家 都出下意见,看还有什么好的sql。。
select * from test 结果如下:name city_name scan_start_time s_hour value
CDRNC01 常市 2011-5-2 17:00 17 2822680.5
CDRNC01 常市 2011-5-2 9:00 9 1805635
CDRNC01 常市 2011-5-1 12:00 12 2209981.125
CDRNC01 常市 2011-5-1 17:00 17 2925668.75
CDRNC01 常市 2011-5-1 3:00 3 249131
CSRNC05 天区 2011-5-1 0 204877610
CSRNC05 天区 2011-5-2 19:00 19 17483989.82
CSRNC05 天区 2011-5-2 21:00 21 20235228.57
CSRNC06 开区 2011-5-1 14:00 14 7548153.242
CSRNC06 开区 2011-5-1 18:00 18 6533271.611
CSRNC06 开区 2011-5-1 2:00 2 2497404.404
CSRNC06 开区 2011-5-1 4:00 4 1080070.781
CSRNC06 开区 2011-5-2 16:00 16 7427590.879
CSRNC06 开区 2011-5-2 17:00 17 8141454.531实现效果(每个城市下面value值最大的记录):
CDRNC01 常市 2011-5-1 12 2209981.125
CSRNC05 天区 2011-5-1 0 204877610
CSRNC06 开区 2011-5-1 14 7548153.242
CSRNC06 开福区 2011-5-2 17 8141454.531
或
CDRNC01 常市 2011-5-1 12:00 12 2209981.125
CSRNC05 天区 2011-5-1 0 204877610
CSRNC06 开区 2011-5-1 14:00 14 7548153.242
CSRNC06 开福区 2011-5-2 17:00 17 8141454.531现在知道一种sql 如下:
select a.* from testa,(select city_name,max(value) from test group by city_name,day(scan_start_time)) b where a.city_name=b.city_name and a.value=b.value大家 都出下意见,看还有什么好的sql。。
select name,city_name,scan_start_time,s_hour,value dense_rank() over(partition by city_name order by value desc)
FROM testa
WHERE (SELECT COUNT ( * )
FROM test
WHERE city_name = a.city_name
AND scan_start_time=a.scan_start_time
AND testvalue > a.testvalue) < 1
ORDER BY city_name, scan_start_time DESC
还有这个,但貌似2种刷出来的结果都不对.....
select *
from (select t.*,
row_number() over(partition by city_name, day(scan_start_time) order by value desc) as line
from test t)
where line = 1;
(select name,city_name,scan_start_time,s_hour,value dense_rank() over(partition by city_name order by city_name,value desc) rn from test)
where rn = 1;