求sql语句。谢谢先

Question

如何找出将上述股票数据中每一分钟段中的，price字段中最高价，最低价，开盘价，收盘价。。例如：2011-12-06 11：00分钟段，有12条数据，找出
最高价是2011-12-06 11：00：29的价格2322.26，
最低价是2011-12-06 11：00：59的价格2321.425
开盘价是2011-12-06 11：00：04的价格2322.045，
收盘价是2011-12-06 11：00：59的价格2321.425。

Answer 1

表达形式：
每一分钟段中的，price字段中最高价一条sql语句
每一分钟段中的，price字段中最低价一条sql语句
每一分钟段中的，price字段中开盘价一条sql语句
每一分钟段中的，price字段中收盘价一条sql语句

Answer 2

补发图片http://hi.csdn.net/attachment/201112/6/1396106_1323153449AJ2j.jpg

Answer 3

select max(价格) 最高价,min(价格) 最低价,
       开盘价=(select top 1 价格 from tb 
where convert(varchar(16),[date],120)=convert(varchar(16),a.[date],120) order by [date]),
收盘价=(select top 1 价格 from tb 
where convert(varchar(16),[date],120)=convert(varchar(16),a.[date],120) order by [date] desc)
from tb a group by convert(varchar(16),a.[date],120)

Answer 4

select convert(varchar(10),dt,120)dt,max(price)最高价,min(price)最低价,
(select price from tb b where convert(varchar(10),dt,120)=convert(varchar(10),a.dt,120) and not exists(select 1 from where convert(varchar(10),dt,120)=convert(varchar(10),b.dt,120) and dt<a.dt)) as 开盘价,
(select price from tb b where convert(varchar(10),dt,120)=convert(varchar(10),a.dt,120) and not exists(select 1 from where convert(varchar(10),dt,120)=convert(varchar(10),b.dt,120) and dt>a.dt)) as 收盘价
from tb a

Answer 5

select  
开盘价=(select top 1 [price] from [StockInfo].[dbo].[2011-12-06_trade] where code='sh000001' and convert(varchar(16),[pushtime],120)=convert(varchar(16),[pushtime],120) order by pushtime),max(Price) 最高价,
min(Price) 最低价,收盘价=(select top 1 [Price] from [StockInfo].[dbo].[2011-12-06_trade] where code='sh000001' and convert(varchar(16),[pushtime],120)=convert(varchar(16),[pushtime],120) order by pushtime desc) from [StockInfo].[dbo].[2011-12-06_trade] where code='sh000001' group by convert(varchar(16),pushtime,120) 
如何列出code 和成交量 还是有pushtime呢

Answer 6

select max(价格) 最高价,min(价格) 最低价,
       开盘价=(select top 1 价格 from tb 
where convert(varchar(16),[date],120)=convert(varchar(16),a.[date],120) order by [date]),
收盘价=(select top 1 价格 from tb 
where convert(varchar(16),[date],120)=convert(varchar(16),a.[date],120) order by [date] desc),min(code),sum(et),max(pushtime)
from tb a group by convert(varchar(16),a.[date],120)

Answer 7

create table atd.test_1207_1 (price decimal(20,10), pushtime timestamp);
insert into atd.test_1207_1 values
(125.11,'2011-12-06 10:59:01')
,(123.121,'2011-12-06 10:59:10')
,(122.321,'2011-12-06 10:59:21')
,(143.234,'2011-12-06 10:59:35')
,(145.54,'2011-12-06 10:59:44')
,(134.768,'2011-12-06 10:59:50')
,(132.645,'2011-12-06 10:59:59'),(225.11,'2011-12-06 11:00:01')
,(223.121,'2011-12-06 11:00:10')
,(222.321,'2011-12-06 11:00:21')
,(243.234,'2011-12-06 11:00:35')
,(245.54,'2011-12-06 11:00:44')
,(234.768,'2011-12-06 11:00:50')
,(232.645,'2011-12-06 11:00:59'),(325.11,'2011-12-06 11:01:01')
,(323.121,'2011-12-06 11:01:10')
,(322.321,'2011-12-06 11:01:21')
,(343.234,'2011-12-06 11:01:35')
,(345.54,'2011-12-06 11:01:44')
,(334.768,'2011-12-06 11:01:50')
,(332.645,'2011-12-06 11:01:59')
;select pushtime,price from (select pushtime,price,row_number()over(partition by substr(char(timestamp(pushtime)),1,16) order by price desc)id from atd.test_1207_1)d where d.id=1
union all
select pushtime,price from (select pushtime,price,row_number()over(partition by substr(char(timestamp(pushtime)),1,16) order by price)id from atd.test_1207_1)d where d.id=1
union all
select pushtime,price from (select pushtime,price,row_number()over(partition by substr(char(timestamp(pushtime)),1,16) order by pushtime desc)id from atd.test_1207_1)d where d.id=1
union all
select pushtime,price from (select pushtime,price,row_number()over(partition by substr(char(timestamp(pushtime)),1,16) order by pushtime)id from atd.test_1207_1)d where d.id=1
;

Answer 8

我发现所有的开盘价都是一样的2322.958 所有的收盘价也都是一样的 2321.297
select  开盘价=(select top 1 [price] from [StockInfo].[dbo].[2011-12-06_trade] where code='sh000001' and convert(varchar(16),[pushtime],120)=convert(varchar(16),[pushtime],120) order by pushtime),max(Price) 最高价,
min(Price) 最低价,min(code) 代码,sum(volume) 市场,max(pushtime) 时间,收盘价=(select top 1 [Price] from [StockInfo].[dbo].[2011-12-06_trade] where code='sh000001' and convert(varchar(16),[pushtime],120)=convert(varchar(16),[pushtime],120) order by pushtime desc)  from [StockInfo].[dbo].[2011-12-06_trade] where code='sh000001' group by convert(varchar(16),pushtime,120) order by  时间  desc

Answer 9

开盘价 最高价 最低价 代码 市场 时间 收盘价
2322.958 2321.848 2321.046 sh000001 477180890 2011-12-06 14:52:54.000 2321.297
2322.958 2321.437 2320.446 sh000001 517973510 2011-12-06 14:51:59.000 2321.297
2322.958 2321.868 2320.772 sh000001 515571360 2011-12-06 14:50:59.000 2321.297
2322.958 2322.098 2321.123 sh000001 513040570 2011-12-06 14:49:59.000 2321.297
2322.958 2322.178 2321.026 sh000001 510377560 2011-12-06 14:48:59.000 2321.297
2322.958 2322.649 2321.915 sh000001 507536470 2011-12-06 14:47:59.000 2321.297
2322.958 2323.799 2322.849 sh000001 505218170 2011-12-06 14:46:59.000 2321.297
2322.958 2323.923 2323.059 sh000001 503221140 2011-12-06 14:45:59.000 2321.297
2322.958 2324.672 2323.623 sh000001 501182340 2011-12-06 14:44:59.000 2321.297
2322.958 2324.613 2323.401 sh000001 499080890 2011-12-06 14:43:59.000 2321.297
2322.958 2325.106 2323.507 sh000001 497002960 2011-12-06 14:42:59.000 2321.297
2322.958 2325.526 2324.202 sh000001 494758970 2011-12-06 14:41:59.000 2321.297
2322.958 2325.685 2325.016 sh000001 410463540 2011-12-06 14:40:59.000 2321.297
2322.958 2325.659 2324.742 sh000001 490739240 2011-12-06 14:39:59.000 2321.297
2322.958 2326.231 2325.074 sh000001 488815690 2011-12-06 14:38:59.000 2321.297
2322.958 2326.471 2325.198 sh000001 446558090 2011-12-06 14:37:59.000 2321.297
2322.958 2326.849 2325.756 sh000001 485363090 2011-12-06 14:36:59.000 2321.297
2322.958 2326.786 2325.27 sh000001 483493550 2011-12-06 14:35:59.000 2321.297
2322.958 2327.052 2325.886 sh000001 481529920 2011-12-06 14:34:59.000 2321.297
2322.958 2327.886 2326.755 sh000001 439576730 2011-12-06 14:33:59.000 2321.297
2322.958 2327.885 2327.064 sh000001 477994790 2011-12-06 14:32:59.000 2321.297
2322.958 2328.248 2327.036 sh000001 476414010 2011-12-06 14:31:59.000 2321.297
2322.958 2328.003 2326.648 sh000001 474849990 2011-12-06 14:30:59.000 2321.297
2322.958 2327.256 2326.474 sh000001 473369310 2011-12-06 14:29:59.000 2321.297
2322.958 2327.712 2326.473 sh000001 471789630 2011-12-06 14:28:59.000 2321.297
2322.958 2328.695 2327.245 sh000001 430745160 2011-12-06 14:27:54.000 2321.297
2322.958 2329.086 2327.767 sh000001 468041430 2011-12-06 14:26:59.000 2321.297
2322.958 2330.581 2327.61 sh000001 503955800 2011-12-06 14:25:59.000 2321.297
2322.958 2331.491 2330.335 sh000001 424265260 2011-12-06 14:24:54.000 2321.297
2322.958 2331.378 2330.613 sh000001 460733940 2011-12-06 14:23:59.000 2321.297
2322.958 2331.388 2330.361 sh000001 458497050 2011-12-06 14:22:59.000 2321.297
2322.958 2331.611 2330.408 sh000001 456009610 2011-12-06 14:21:59.000 2321.297
2322.958 2331.09 2330.019 sh000001 453140720 2011-12-06 14:20:59.000 2321.297
2322.958 2331.417 2330.453 sh000001 449604390 2011-12-06 14:19:59.000 2321.297
2322.958 2330.584 2329.168 sh000001 445748160 2011-12-06 14:18:59.000 2321.297
2322.958 2329.437 2327.511 sh000001 442163470 2011-12-06 14:17:59.000 2321.297
2322.958 2327.663 2326.344 sh000001 439061980 2011-12-06 14:16:59.000 2321.297
2322.958 2326.595 2324.739 sh000001 364163960 2011-12-06 14:15:59.000 2321.297
2322.958 2314.773 2313.602 sh000001 255320740 2011-12-06 13:19:49.000 2321.297
2322.958 2314.722 2313.762 sh000001 305143190 2011-12-06 13:18:59.000 2321.297
2322.958 2315.049 2313.44 sh000001 303595020 2011-12-06 13:17:59.000 2321.297
2322.958 2314.748 2312.159 sh000001 301364880 2011-12-06 13:16:59.000 2321.297
2322.958 2313.49 2311.596 sh000001 299481320 2011-12-06 13:15:59.000 2321.297
2322.958 2313.396 2312.256 sh000001 297963470 2011-12-06 13:14:59.000 2321.297
2322.958 2314.101 2311.987 sh000001 296472700 2011-12-06 13:13:59.000 2321.297
2322.958 2313.407 2312.759 sh000001 295203320 2011-12-06 13:12:59.000 2321.297
2322.958 2314.634 2312.978 sh000001 293856290 2011-12-06 13:11:59.000 2321.297
2322.958 2314.075 2313.095 sh000001 292567540 2011-12-06 13:10:59.000 2321.297
2322.958 2315.132 2313.608 sh000001 290907890 2011-12-06 13:09:59.000 2321.297
2322.958 2315.207 2312.783 sh000001 288922190 2011-12-06 13:08:59.000 2321.297
2322.958 2314.262 2313.387 sh000001 215463390 2011-12-06 13:07:54.000 2321.297
2322.958 2315.225 2313.729 sh000001 238395400 2011-12-06 13:06:59.000 2321.297
2322.958 2315.079 2313.839 sh000001 284937210 2011-12-06 13:05:59.000 2321.297
2322.958 2314.94 2313.814 sh000001 283464650 2011-12-06 13:04:59.000 2321.297
2322.958 2315.508 2313.706 sh000001 281982400 2011-12-06 13:03:59.000 2321.297
2322.958 2315.688 2313.529 sh000001 280727240 2011-12-06 13:02:59.000 2321.297
2322.958 2314.51 2314.087 sh000001 279440330 2011-12-06 13:01:59.000 2321.297
2322.958 2315.843 2314.218 sh000001 278272810 2011-12-06 13:00:59.000 2321.297
2322.958 2315.14 2315.124 sh000001 45749410 2011-12-06 11:30:09.000 2321.297
2322.958 2316.628 2315.277 sh000001 273712210 2011-12-06 11:29:59.000 2321.297
2322.958 2316.707 2313.602 sh000001 271242280 2011-12-06 11:28:59.000 2321.297
2322.958 2314.49 2312.997 sh000001 267959570 2011-12-06 11:27:59.000 2321.297
2322.958 2315.918 2314.486 sh000001 264776390 2011-12-06 11:26:59.000 2321.297
2322.958 2317.541 2315.617 sh000001 261583060 2011-12-06 11:25:59.000 2321.297
2322.958 2319.326 2317.74 sh000001 259613270 2011-12-06 11:24:59.000 2321.297
2322.958 2320.089 2317.906 sh000001 258202290 2011-12-06 11:23:59.000 2321.297
2322.958 2320.7 2318.546 sh000001 256725790 2011-12-06 11:22:59.000 2321.297
2322.958 2320.334 2318.534 sh000001 255359510 2011-12-06 11:21:59.000 2321.297
2322.958 2321.122 2320.311 sh000001 253975520 2011-12-06 11:20:59.000 2321.297
2322.958 2321.538 2319.885 sh000001 231141170 2011-12-06 11:19:59.000 2321.297
2322.958 2320.198 2318.851 sh000001 250482390 2011-12-06 11:18:59.000 2321.297
2322.958 2320.353 2319.223 sh000001 248905370 2011-12-06 11:17:59.000 2321.297
2322.958 2320.116 2319.511 sh000001 247407960 2011-12-06 11:16:59.000 2321.297
2322.958 2320.529 2319.485 sh000001 245971350 2011-12-06 11:15:59.000 2321.297
2322.958 2320.866 2318.616 sh000001 243740780 2011-12-06 11:14:59.000 2321.297
2322.958 2320.154 2318.289 sh000001 220676550 2011-12-06 11:13:59.000 2321.297
2322.958 2320.737 2319.119 sh000001 238221600 2011-12-06 11:12:59.000 2321.297
2322.958 2321.293 2320.167 sh000001 216705270 2011-12-06 11:11:59.000 2321.297
2322.958 2321.955 2321.044 sh000001 234945500 2011-12-06 11:10:59.000 2321.297
2322.958 2323.193 2321.905 sh000001 233317080 2011-12-06 11:09:59.000 2321.297
2322.958 2323.337 2322.625 sh000001 231775780 2011-12-06 11:08:59.000 2321.297
2322.958 2323.533 2322.761 sh000001 211207110 2011-12-06 11:07:59.000 2321.297
2322.958 2323.647 2322.572 sh000001 209718840 2011-12-06 11:06:54.000 2321.297
2322.958 2323.203 2321.668 sh000001 227067220 2011-12-06 11:05:59.000 2321.297
2322.958 2321.468 2320.774 sh000001 225643010 2011-12-06 11:04:59.000 2321.297
2322.958 2321.972 2321.24 sh000001 224368410 2011-12-06 11:03:59.000 2321.297
2322.958 2322.325 2320.68 sh000001 222982650 2011-12-06 11:02:59.000 2321.297
2322.958 2322.392 2320.914 sh000001 221539010 2011-12-06 11:01:59.000 2321.297
2322.958 2322.26 2321.425 sh000001 220036520 2011-12-06 11:00:59.000 2321.297
2322.958 2322.958 2322.519 sh000001 73023080 2011-12-06 10:59:59.000 2321.297

Answer 10

你查的那条sql没有分组  当然查的是一样的啊

Answer 11

14楼的sql不符合要求？？ 只是数据环境不同  

Answer 12

我是在db2的环境下作的  sqlserver里的函数我不太清楚  可能substr函数要换成substring  timestamp可能也要换一下   逻辑是么有问题的  你不会一点sql基础都不知道吧

Answer 13

select  开盘价=(select top 1 [price] from [StockInfo].[dbo].[2011-12-06_trade] where code='sh000001' and convert(varchar(16),[pushtime],120)=convert(varchar(16),[pushtime],120)  order by pushtime),max(Price) 最高价,min(Price) 最低价,收盘价=(select top 1 [Price] from [StockInfo].[dbo].[2011-12-06_trade] where code='sh000001' and convert(varchar(16),[pushtime],120)=convert(varchar(16),[pushtime],120)  order by pushtime desc),  min(code) 代码,sum(volume) 成交量,max(pushtime) 时间from [StockInfo].[dbo].[2011-12-06_trade] where code='sh000001' group by convert(varchar(16),pushtime,120) order by  时间  desc
怎么分组呢...我对这样复杂的sql..真的很头疼

Answer 14

除了语法不通用,你写出的语句也是不正确的.使用union,你查找出来只是一个字段,LZ要求的是多个字段展示

Answer 15

--本人使用了求得的代码结果为:
/*
                   ,最高价,   最低价,   开盘价,   收盘价,     代码,     市场
'2011-12-06 10:59',2322.958, 2322.519,2322.958,2322.602,'sh000001',73023080
'2011-12-06 11:00',2322.260, 2321.425,2322.045,2321.425,'sh000001',219297240
'2011-12-06 11:01',2321.979, 2321.613,2321.914,2321.819,'sh000001',109648620*/

Answer 16

我不是说了嘛  我的表模型只添加了两个字段  想要查别的字段可以添加列嘛   逻辑又没有改变  怎么会不对呢   还有函数不通用可以转换成sqlserver里面相应的函数啊   这点变通都不会？？？

Answer 17

我上面不是说了嘛  数据环境不一样  我的建的表模型只有两个列（没有其他列不影响查询逻辑）  在正式环境下面加上要查的列不就行了嘛  逻辑又没有变化   还有函数不通用那可以转换成sqlserver里面的相应的函数啊   这点变通都不会？？？

Answer 18

badyflf
请给出sql ，谢谢你了

Answer 19

select convert(varchar(16),pushtime,120)时间,code as 代码,
(select top 1 price from tb b where code=a.code and convert(varchar(16),pushtime,120)=convert(varchar(16),a.pushtime,120) and not exists(select 1 from tb where code=a.code and convert(varchar(16),pushtime,120)=convert(varchar(16),a.pushtime,120) and pushtime<b.pushtime)) as 开盘价,
(select top 1 price from tb b where code=a.code and convert(varchar(16),pushtime,120)=convert(varchar(16),a.pushtime,120) and not exists(select 1 from tb where code=a.code and convert(varchar(16),pushtime,120)=convert(varchar(16),a.pushtime,120) and pushtime>b.pushtime)) as 收盘价,
(select max(price) from tb where code=a.code and convert(varchar(16),pushtime,120)=convert(varchar(16),a.pushtime,120)) as 最高价,
(select min(price) from tb where code=a.code and convert(varchar(16),pushtime,120)=convert(varchar(16),a.pushtime,120)) as 最低价
from tb a

Answer 20

http://topic.csdn.net/u/20111208/13/9e874b8a-f850-43e8-b79d-34c41c54f5c1.html?seed=1297941398&r=76856347#r_76856347
这里已给你回复,LZ同志.