求每一分钟内的,最高价, 最低价, 开盘价, 收盘价, 代码, 成交量的和结果为:
/*
,最高价, 最低价, 开盘价, 收盘价, 代码, 市场
'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
/*
,最高价, 最低价, 开盘价, 收盘价, 代码, 市场
'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(char(16),pushtime,120),MAX(amount) as 最高价,MIN(amount) as 最低价
,sum(case when pushTime=MIN(pushTime) then amount else 0) as 开盘价 end
,sum(case when pushTime=max(pushTime) then amount else 0) as 收盘价 end
from tb group by convert(char(16),pushtime,120)
')' 附近有语法错误。
select
convert(char(16),pushtime,120),
MAX(amount) as 最高价,
MIN(amount) as 最低价,
sum(case when pushTime=MIN(pushTime) then amount else 0) as 开盘价 end ,
sum(case when pushTime=max(pushTime) then amount else 0) as 收盘价 end from [2011-12-06_trade] group by convert(char(16),pushtime,120)
(select price from tb b where code=a.code 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 price from tb b where code=a.code 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
select convert(varchar(16),pushtime,120)时间,code as 代码,
(select 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 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
(select price from [StockInfo].[dbo].[2011-12-06_trade] b where code=a.code and convert(varchar(16),pushtime,120)=convert(varchar(16),a.pushtime,120) and not exists(select 1 from [StockInfo].[dbo].[2011-12-06_trade] where code=a.code and convert(varchar(16),pushtime,120)=convert(varchar(16),a.pushtime,120) and pushtime<b.pushtime)) as 开盘价,
(select price from [StockInfo].[dbo].[2011-12-06_trade] b where code=a.code and convert(varchar(16),pushtime,120)=convert(varchar(16),a.pushtime,120) and not exists(select 1 from [StockInfo].[dbo].[2011-12-06_trade] 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 [StockInfo].[dbo].[2011-12-06_trade] where code=a.code and convert(varchar(16),pushtime,120)=convert(varchar(16),a.pushtime,120)) as 最高价,
(select min(price) from [StockInfo].[dbo].[2011-12-06_trade] where code=a.code and convert(varchar(16),pushtime,120)=convert(varchar(16),a.pushtime,120)) as 最低价
from [StockInfo].[dbo].[2011-12-06_trade] a
select convert(varchar(16),pushtime,120)时间,code as 代码,
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
convert(char(16),pushtime,120),
MAX(amount) as 最高价,
MIN(amount) as 最低价,
sum(case when pushTime=MIN(pushTime) then amount else 0 end) as 开盘价,
sum(case when pushTime=max(pushTime) then amount else 0 end) as 收盘价
from
[2011-12-06_trade]
group by
convert(char(16),pushtime,120)
你的数据有问题,某个时间有两个交易记录了.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
select convert(varchar(16),pushtime,120) '时间' ,min(Price) '最低价' ,max(Price) '最高价' ,'开盘价'=(select top 1 [price] from [Test].[dbo].[trade] where code='sh000001' and convert(varchar(16),[pushtime],120)=convert(varchar(16),tr.[pushtime],120)
order by pushtime) ,'收盘价'=(select top 1 [Price] from [Test].[dbo].[trade] where code='sh000001' and convert(varchar(16),[pushtime],120)=convert(varchar(16),tr.[pushtime],120) order by pushtime desc) ,min(code) '代码' ,sum(volume) '市场'
from [Test].[dbo].[trade] tr where code='sh000001' group by convert(varchar(16),pushtime,120) order by '时间'
/*
时间, 最低价, 最高价, 开盘价, 收盘价, 代码, 市场
2011-12-06 10:59 2322.519 2322.958 2322.958 2322.602 sh000001 73023080
2011-12-06 11:00 2321.425 2322.260 2322.045 2321.425 sh000001 219297240
2011-12-06 11:01 2320.914 2321.979 2320.914 2321.819 sh000001 109648620
*/