CREATE TABLE HolderDetail(
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[OpentDate] datetime,
[TradeDate] datetime
)
INSERT INTO HolderDetail([OpentDate])
SELECT '2013-01-01 15:00:00' UNION ALL
SELECT '2013-01-02 12:18:00' UNION ALL
SELECT '2013-01-03 09:00:00' UNION ALL
SELECT '2013-01-04 06:15:00'
CREATE TABLE ReckonDate(
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[TradeDate] datetime,
[Startdate] datetime,
[Enddate] datetime
)
INSERT INTO ReckonDate([TradeDate],[Startdate],[Enddate])
SELECT '2013-01-01 00:00:00','2013-01-01 04:00:00','2013-01-02 04:00:00' UNION ALL
SELECT '2013-01-02 00:00:00','2013-01-02 04:00:00','2013-01-03 04:00:00' UNION ALL
SELECT '2013-01-03 00:00:00','2013-01-03 04:00:00','2013-01-04 04:00:00' UNION ALL
SELECT '2013-01-04 00:00:00','2013-01-04 04:00:00','2013-01-05 04:00:00'
----------------------------------------------
--现在需要更新HolderDetail,Tradedate值
--计算公式，HolderDetail表里OpentDate,对照ReckonDate表里的[Startdate]，[Enddate]
--如果在它的范围里，对应找到TradeDate，然后把TradeDate的值更新到HolderDetail表里的 [TradeDate]
/*
'2013-01-01 15:00:00' = '2013-01-01 00:00:00'
'2013-01-02 12:18:00' = '2013-01-02 00:00:00'
'2013-01-03 09:00:00' = '2013-01-03 00:00:00'
'2013-01-04 06:15:00' = '2013-01-04 00:00:00'
*/
------------------------------------------------
--我只想到最笨的方法，用游标。一个个对比得到tradedate值，然后再更新。数据量很大，更新慢。
--有没有更好方法，比如批量直接更新。
优化 sql
------解决方案--------------------
CREATE TABLE HolderDetail(
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[OpentDate] datetime,
[TradeDate] datetime
)
INSERT INTO HolderDetail([OpentDate])
SELECT '2013-01-01 15:00:00' UNION ALL
SELECT '2013-01-02 12:18:00' UNION ALL
SELECT '2013-01-03 09:00:00' UNION ALL
SELECT '2013-01-04 06:15:00'
CREATE TABLE ReckonDate(
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[TradeDate] datetime,
[Startdate] datetime,
[Enddate] datetime
)
INSERT INTO ReckonDate([TradeDate],[Startdate],[Enddate])
SELECT '2013-01-01 00:00:00','2013-01-01 04:00:00','2013-01-02 04:00:00' UNION ALL
SELECT '2013-01-02 00:00:00','2013-01-02 04:00:00','2013-01-03 04:00:00' UNION ALL
SELECT '2013-01-03 00:00:00','2013-01-03 04:00:00','2013-01-04 04:00:00' UNION ALL
SELECT '2013-01-04 00:00:00','2013-01-04 04:00:00','2013-01-05 04:00:00'
UPDATE
A
SET
TradeDate=B.TradeDate
FROM
HolderDetail A, ReckonDate B
WHERE
DATEDIFF(DD,A.OpentDate,B.TradeDate)=0
AND
A.OpentDate BETWEEN B.Startdate AND B.Enddate
SELECT * FROM HolderDetail
DROP TABLE HolderDetail,ReckonDate
/*Id OpentDate TradeDate
----------- ----------------------- -----------------------
1 2013-01-01 15:00:00.000 2013-01-01 00:00:00.000
2 2013-01-02 12:18:00.000 2013-01-02 00:00:00.000
3 2013-01-03 09:00:00.000 2013-01-03 00:00:00.000
4 2013-01-04 06:15:00.000 2013-01-04 00:00:00.000
(4 行受影响)*/
------解决方案--------------------
update a
set a.TradeDate=b.TradeDate
from HolderDetail a
inner join ReckonDate b on a.OpentDate between b.Startdate and b.Enddate