求sql语句。谢谢先

[qaqaqa]

如何找出将上述股票数据中每一分钟段中的，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。

[qaqaqa]

6666666666666666666666

[TBNoO]

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

[Quote=引用 20 楼 cjavaer 的回复:]
14楼的sql不符合要求？？ 只是数据环境不同
[/Quote]

[qaqaqa]

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呢

[TBNoO]

我解出来了，呵呵！

[Quote=引用 24 楼 qaqaqa 的回复:]
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),

……
[/Quote]

[快溜]

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(market),max(pushtime)
from tb a group by convert(varchar(16),a.[date],120)

[JS]

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
;

[JS]

我只建了两列 其他列加入即可 如有问题追加

[qaqaqa]

我发现所有的开盘价都是一样的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

[qaqaqa]

开盘价 最高价 最低价 代码 市场 时间 收盘价
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

[JS]

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

[qaqaqa]

请帮忙修改一下。。。谢谢

[JS]

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

[JS]

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

[octwind]

[Quote=引用 13 楼 ssp2009 的回复:]
SQL code
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……
[/Quote]

[TBNoO]

--本人使用了求得的代码结果为:
/*
                   ,最高价,   最低价,   开盘价,   收盘价,     代码,     市场
'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

*/

[-晴天]

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

[TBNoO]

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

[Quote=引用 30 楼 qaqaqa 的回复:]
badyflf
请给出sql ，谢谢你了
[/Quote]

[JS]

[Quote=引用 25 楼 badyflf 的回复:]
除了语法不通用,你写出的语句也是不正确的.使用union,你查找出来只是一个字段,LZ要求的是多个字段展示


引用 20 楼 cjavaer 的回复:
14楼的sql不符合要求？？ 只是数据环境不同
[/Quote]

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

[JS]

[Quote=引用 25 楼 badyflf 的回复:]
除了语法不通用,你写出的语句也是不正确的.使用union,你查找出来只是一个字段,LZ要求的是多个字段展示


引用 20 楼 cjavaer 的回复:
14楼的sql不符合要求？？ 只是数据环境不同
[/Quote]


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

[qaqaqa]

badyflf
请给出sql ，谢谢你了