是不是这样?---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2014-02-08 16:42:12 -- Version: -- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) -- Dec 28 2012 20:23:12 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) -- ---------------------------------------------------------------- --> 测试数据:[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([股票代码] varchar(8),[日期] datetime,[收盘价] numeric(4,2),[成交量] int) insert [huang] select 'SH601318','20131212',30.11,100000000 union all select 'SH601318','20131213',30.55,100500000 union all select 'SZ002322','20131212',12.34,300000000 union all select 'SH601318','20131216',30.55,100006600 --------------开始查询-------------------------- IF OBJECT_ID('tempdb..#t','u')IS NOT NULL DROP TABLE #t select [股票代码],[日期],[收盘价],[成交量] ,(SELECT COUNT(1) FROM huang b WHERE a.[股票代码]=b.[股票代码] AND a.[日期]>b.[日期])+1 id INTO #t from [huang] aSELECT a.[股票代码],a.[日期],a.[收盘价],a.[成交量],(a.[收盘价]-b.[收盘价])/b.[收盘价][涨幅] FROM #t a LEFT JOIN #t b ON a.[股票代码]=b.[股票代码] AND a.id=b.id+1 ----------------结果---------------------------- /* 股票代码 日期 收盘价 成交量 涨幅 -------- ----------------------- --------------------------------------- ----------- --------------------------------------- SH601318 2013-12-12 00:00:00.000 30.11 100000000 NULL SH601318 2013-12-13 00:00:00.000 30.55 100500000 0.0146130 SZ002322 2013-12-12 00:00:00.000 12.34 300000000 NULL SH601318 2013-12-16 00:00:00.000 30.55 100006600 0.0000000 */
你怕有性能问题的话用这个,加个索引,一般没啥问题IF OBJECT_ID('tempdb..#t','u')IS NOT NULL DROP TABLE #t select [股票代码],[日期],[收盘价],[成交量] ,(SELECT COUNT(1) FROM huang b WHERE a.[股票代码]=b.[股票代码] AND a.[日期]>b.[日期])+1 id INTO #t from [huang] a CREATE INDEX IX_test ON #t ([股票代码],id) SELECT a.[股票代码],a.[日期],a.[收盘价],a.[成交量],(a.[收盘价]-b.[收盘价])/b.[收盘价][涨幅] FROM #t a LEFT JOIN #t b ON a.[股票代码]=b.[股票代码] AND a.id=b.id+1
(SH601318,20131212,30.11,100000000,null)
(SH601318,20131213,30.55,100500000,null)
(SZ002322,20131212,12.34,300000000,null)
(SH601318,20131216,30.55,100006600,null)
目的是将记录中最后一列null计算出来。最后一列计算的规则是:股票代码相同的情况下,涨幅=(当天收盘价-前一交易日收盘价)/前一交易日收盘价。(收盘价在第三列)
股票可能有停牌不交易,也可能周末不交易,所以前一交易日不一定是日历上的前一天。
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-02-08 16:42:12
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([股票代码] varchar(8),[日期] datetime,[收盘价] numeric(4,2),[成交量] int)
insert [huang]
select 'SH601318','20131212',30.11,100000000 union all
select 'SH601318','20131213',30.55,100500000 union all
select 'SZ002322','20131212',12.34,300000000 union all
select 'SH601318','20131216',30.55,100006600
--------------开始查询--------------------------
IF OBJECT_ID('tempdb..#t','u')IS NOT NULL
DROP TABLE #t
select [股票代码],[日期],[收盘价],[成交量] ,(SELECT COUNT(1) FROM huang b WHERE a.[股票代码]=b.[股票代码] AND a.[日期]>b.[日期])+1 id INTO #t
from [huang] aSELECT a.[股票代码],a.[日期],a.[收盘价],a.[成交量],(a.[收盘价]-b.[收盘价])/b.[收盘价][涨幅]
FROM #t a LEFT JOIN #t b ON a.[股票代码]=b.[股票代码] AND a.id=b.id+1
----------------结果----------------------------
/*
股票代码 日期 收盘价 成交量 涨幅
-------- ----------------------- --------------------------------------- ----------- ---------------------------------------
SH601318 2013-12-12 00:00:00.000 30.11 100000000 NULL
SH601318 2013-12-13 00:00:00.000 30.55 100500000 0.0146130
SZ002322 2013-12-12 00:00:00.000 12.34 300000000 NULL
SH601318 2013-12-16 00:00:00.000 30.55 100006600 0.0000000
*/
DROP TABLE #t
select [股票代码],[日期],[收盘价],[成交量] ,(SELECT COUNT(1) FROM huang b WHERE a.[股票代码]=b.[股票代码] AND a.[日期]>b.[日期])+1 id INTO #t
from [huang] a
CREATE INDEX IX_test ON #t ([股票代码],id)
SELECT a.[股票代码],a.[日期],a.[收盘价],a.[成交量],(a.[收盘价]-b.[收盘价])/b.[收盘价][涨幅]
FROM #t a LEFT JOIN #t b ON a.[股票代码]=b.[股票代码] AND a.id=b.id+1