SQL 计算股票当日涨跌率的问题？

Question

数据表结构（tablename：SDT）
stockid;tdate;open;high;low;close;volumn;amount
600001;"1998-01-22";8.00;8.49;7.88;7.91;1091924;87918.10
600001;"1998-02-09";8.31;8.35;8.01;8.04;177884;14456.60
600001;"1998-02-10";8.01;8.06;7.90;7.94;97832;7778.20
600001;"1998-02-11";7.95;8.17;7.89;8.06;121235;9676.90
600001;"1998-02-12";8.06;8.06;7.89;7.94;96047;7617.90
600001;"1998-02-16";7.79;7.87;7.66;7.77;74330;5747.60
600001;"1998-02-17";7.74;7.89;7.71;7.78;44264;3450.50如何直接计算该股票的每日涨跌幅度，就是 [上一交易日收盘价（close）－今日收盘价（close)]/[上一交易日收盘价（close）]？试过类似用
  select.....  from SDT0 left join SDT1 on SDT0.stockid = SDT1.stockid
  WHERE SDT1.tdate + interval '1 day' =SDT2.tdate 有2个问题：
    1.没有办法 获得 下一个交易日（就是SDT2.tdate是比SDT1.tdate大的最小的一个日期）     
    2.效率比较差 

Answer 1

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @SDT
DECLARE @SDT TABLE (stockid INT,tdate DATETIME,[open] NUMERIC(3,2),high NUMERIC(3,2),low NUMERIC(3,2),[close] NUMERIC(3,2),volumn INT,amount NUMERIC(7,2))
INSERT INTO @SDT
SELECT 600001,'1998-01-22',8.00,8.49,7.88,7.91,1091924,87918.10 UNION ALL
SELECT 600001,'1998-02-09',8.31,8.35,8.01,8.04,177884,14456.60 UNION ALL
SELECT 600001,'1998-02-10',8.01,8.06,7.90,7.94,97832,7778.20 UNION ALL
SELECT 600001,'1998-02-11',7.95,8.17,7.89,8.06,121235,9676.90 UNION ALL
SELECT 600001,'1998-02-12',8.06,8.06,7.89,7.94,96047,7617.90 UNION ALL
SELECT 600001,'1998-02-16',7.79,7.87,7.66,7.77,74330,5747.60 UNION ALL
SELECT 600001,'1998-02-17',7.74,7.89,7.71,7.78,44264,3450.50--SQL查询如下:SELECT
    stockid,
    tdate,
    涨跌幅度=(B.[close]-A.[close])/B.[close],
    [open],
    high,
    low,
    A.[close],
    volumn,
    amount
FROM @SDT AS A
    OUTER APPLY(
        SELECT TOP(1)
            [close]
        FROM @SDT
        WHERE A.stockid=stockid
            AND tdate<A.tdate
        ORDER BY tdate DESC
    ) AS B
ORDER BY stockid,tdate/*
stockid     tdate                   涨跌幅度                                    open                                    high                                    low                                     close                                   volumn      amount
----------- ----------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ----------- ---------------------------------------
600001      1998-01-22 00:00:00.000 NULL                                    8.00                                    8.49                                    7.88                                    7.91                                    1091924     87918.10
600001      1998-02-09 00:00:00.000 -0.016434                               8.31                                    8.35                                    8.01                                    8.04                                    177884      14456.60
600001      1998-02-10 00:00:00.000 0.012437                                8.01                                    8.06                                    7.90                                    7.94                                    97832       7778.20
600001      1998-02-11 00:00:00.000 -0.015113                               7.95                                    8.17                                    7.89                                    8.06                                    121235      9676.90
600001      1998-02-12 00:00:00.000 0.014888                                8.06                                    8.06                                    7.89                                    7.94                                    96047       7617.90
600001      1998-02-16 00:00:00.000 0.021410                                7.79                                    7.87                                    7.66                                    7.77                                    74330       5747.60
600001      1998-02-17 00:00:00.000 -0.001287                               7.74                                    7.89                                    7.71                                    7.78                                    44264       3450.50
*/