比如某一张表XXX:
有这五字段,
StockID,UpdateTime,PriceNew,Amount,Volume
其中每一分钟内有多条数据
SH000001 2010-11-18 13:48:56.000 2851.03 90588990000.00 89467584
SH000001 2010-11-18 13:48:50.000 2850.56 90559630000.00 89445536
SH000001 2010-11-18 13:48:47.000 2850.76 90543180000.00 89431680
SH000001 2010-11-18 13:48:41.000 2851.07 90501330000.00 89365208
SH000001 2010-11-18 13:48:35.000 2850.57 90460530000.00 89266928
SH000001 2010-11-18 13:48:32.000 2851.03 90449600000.00 89232232
SH000001 2010-11-18 13:48:26.000 2850.76 90412240000.00 89202816
我想获取每一分钟内PriceNew的
(开盘价),最高价,最低价,收盘价,
sql语句怎样写呀
有这五字段,
StockID,UpdateTime,PriceNew,Amount,Volume
其中每一分钟内有多条数据
SH000001 2010-11-18 13:48:56.000 2851.03 90588990000.00 89467584
SH000001 2010-11-18 13:48:50.000 2850.56 90559630000.00 89445536
SH000001 2010-11-18 13:48:47.000 2850.76 90543180000.00 89431680
SH000001 2010-11-18 13:48:41.000 2851.07 90501330000.00 89365208
SH000001 2010-11-18 13:48:35.000 2850.57 90460530000.00 89266928
SH000001 2010-11-18 13:48:32.000 2851.03 90449600000.00 89232232
SH000001 2010-11-18 13:48:26.000 2850.76 90412240000.00 89202816
我想获取每一分钟内PriceNew的
(开盘价),最高价,最低价,收盘价,
sql语句怎样写呀
貌似LZ是搞证券的!
go
set nocount on
if OBJECT_ID(N'A') is not null drop table A
go
--创建测试表
create Table A
(
StockID nvarchar(20),
UpdateTime datetime,
PriceNew float,
Amount float,
Volume int
)
go
--插入测试数据
insert into A
select 'SH000001', '2010-11-18 13:48:56.000', 2851.03, 90588990000.00, 89467584 union all
select 'SH000001', '2010-11-18 13:48:50.000', 2850.56, 90559630000.00, 89445536 union all
select 'SH000001', '2010-11-18 13:48:47.000', 2850.76, 90543180000.00, 89431680 union all
select 'SH000001', '2010-11-18 13:48:41.000', 2851.07, 90501330000.00, 89365208 union all
select 'SH000001', '2010-11-18 13:48:35.000', 2850.57, 90460530000.00, 89266928 union all
select 'SH000001', '2010-11-18 13:48:32.000', 2851.03, 90449600000.00, 89232232 union all
select 'SH000001', '2010-11-18 13:48:26.000', 2850.76, 90412240000.00, 89202816
go
--SQL语句
with cte as
(
select *,ROW_NUMBER()over(order by UpdateTime) as RN from A
)
select
(select top 1 PriceNew from cte order by RN) as 开盘价,
Max(PriceNew) as 最高价,
Min(PriceNew) as 最低价,(select top 1 PriceNew from cte order by RN desc) as 收盘价
from cte
--测试结果
/*
开盘价 最高价 最低价 收盘价
---------------------- ---------------------- ---------------------- ----------------------
2850.76 2851.07 2850.56 2851.03*/