求一条连续更新的SQL语句

Question

记录形式：
CompanyID,materialID,FromDate，  EndDate
Cp01        1        2010-05-01
Cp01        1        2010-05-03
Cp01        1        2010-05-15
Cp01        2        2010-01-01
Cp01        2        2010-03-01
Cp01        3        2010-02-01要求更新时，是按照相同的CompanyID和materialID按照日期的先后进行连续更新，形成连续的日期，对于没有后续的更新为'9999-12-31'
更新后的形式为：
Cp01        1        2010-05-01  2010-05-03
Cp01        1        2010-05-03  2010-05-15
Cp01        1        2010-05-15  9999-12-31
Cp01        2        2010-01-01  2010-03-01
Cp01        2        2010-03-01  9999-12-31
Cp01        3        2010-02-01  9999-12-31

Answer 1

----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date   : 2010-05-17 11:09:29
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog   : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([CompanyID] [nvarchar](10),[materialID] [int],[FromDate] [datetime],[EndDate] [datetime])
INSERT INTO [tb]
SELECT 'Cp01','1','2010-05-01',NULL UNION ALL
SELECT 'Cp01','1','2010-05-03',NULL UNION ALL
SELECT 'Cp01','1','2010-05-15',NULL UNION ALL
SELECT 'Cp01','2','2010-01-01',NULL UNION ALL
SELECT 'Cp01','2','2010-03-01',NULL UNION ALL
SELECT 'Cp01','3','2010-02-01',NULL-->SQL查询如下:
update tb set
[EndDate]=isnull((select min(FromDate) from tb t where CompanyID=tb.CompanyID and materialID=tb.materialID and FromDate>tb.FromDate),'9999-12-31')

SELECT * FROM [tb]
/*
CompanyID  materialID  FromDate                EndDate
---------- ----------- ----------------------- -----------------------
Cp01       1           2010-05-01 00:00:00.000 2010-05-03 00:00:00.000
Cp01       1           2010-05-03 00:00:00.000 2010-05-15 00:00:00.000
Cp01       1           2010-05-15 00:00:00.000 9999-12-31 00:00:00.000
Cp01       2           2010-01-01 00:00:00.000 2010-03-01 00:00:00.000
Cp01       2           2010-03-01 00:00:00.000 9999-12-31 00:00:00.000
Cp01       3           2010-02-01 00:00:00.000 9999-12-31 00:00:00.000(6 行受影响)*/

Answer 2

create table tb(CompanyID varchar(10),materialID int,FromDate datetime , EndDate datetime)
insert into tb values('Cp01', 1 ,'2010-05-01',null)
insert into tb values('Cp01', 1 ,'2010-05-03',null)
insert into tb values('Cp01', 1 ,'2010-05-15',null)
insert into tb values('Cp01', 2 ,'2010-01-01',null)
insert into tb values('Cp01', 2 ,'2010-03-01',null)
insert into tb values('Cp01', 3 ,'2010-02-01',null)
goselect CompanyID ,materialID ,FromDate ,  EndDate = isnull((select top 1 FromDate from tb where CompanyID = t.CompanyID and FromDate > t.FromDate),'9999-12-31') from tb tdrop table tb/*
CompanyID  materialID  FromDate                                               EndDate                                                
---------- ----------- ------------------------------------------------------ ------------------------------------------------------ 
Cp01       1           2010-05-01 00:00:00.000                                2010-05-03 00:00:00.000
Cp01       1           2010-05-03 00:00:00.000                                2010-05-15 00:00:00.000
Cp01       1           2010-05-15 00:00:00.000                                9999-12-31 00:00:00.000
Cp01       2           2010-01-01 00:00:00.000                                2010-05-01 00:00:00.000
Cp01       2           2010-03-01 00:00:00.000                                2010-05-01 00:00:00.000
Cp01       3           2010-02-01 00:00:00.000                                2010-05-01 00:00:00.000（所影响的行数为 6 行）*/

Answer 3

create table tb(CompanyID varchar(10),materialID int,FromDate datetime , EndDate datetime)
insert into tb values('Cp01', 1 ,'2010-05-01',null)
insert into tb values('Cp01', 1 ,'2010-05-03',null)
insert into tb values('Cp01', 1 ,'2010-05-15',null)
insert into tb values('Cp01', 2 ,'2010-01-01',null)
insert into tb values('Cp01', 2 ,'2010-03-01',null)
insert into tb values('Cp01', 3 ,'2010-02-01',null)
go--查询
select CompanyID ,materialID ,FromDate ,  EndDate = isnull((select top 1 FromDate from tb where CompanyID = t.CompanyID and materialID = t.materialID and FromDate > t.FromDate),'9999-12-31') from tb t--更新
update tb 
set EndDate = isnull((select top 1 FromDate from tb where CompanyID = t.CompanyID and materialID = t.materialID and FromDate > t.FromDate),'9999-12-31')
from tb tselect * from tbdrop table tb/*
CompanyID  materialID  FromDate                                               EndDate                                                
---------- ----------- ------------------------------------------------------ ------------------------------------------------------ 
Cp01       1           2010-05-01 00:00:00.000                                2010-05-03 00:00:00.000
Cp01       1           2010-05-03 00:00:00.000                                2010-05-15 00:00:00.000
Cp01       1           2010-05-15 00:00:00.000                                9999-12-31 00:00:00.000
Cp01       2           2010-01-01 00:00:00.000                                2010-03-01 00:00:00.000
Cp01       2           2010-03-01 00:00:00.000                                9999-12-31 00:00:00.000
Cp01       3           2010-02-01 00:00:00.000                                9999-12-31 00:00:00.000（所影响的行数为 6 行）*/

Answer 4

if not object_id('tb') is null
drop table tb
Go
Create table tb([CompanyID] nvarchar(4),[materialID] int,[FromDate] Datetime,[EndDate]datetime)
Insert tb
select N'Cp01',1,'2010-05-01',null union all
select N'Cp01',1,'2010-05-03',null union all
select N'Cp01',1,'2010-05-15',null union all
select N'Cp01',2,'2010-01-01',null union all
select N'Cp01',2,'2010-03-01',null union all
select N'Cp01',3,'2010-02-01',null
Go
update t
set [EndDate]=isnull((select top 1 [FromDate] 
               from tb 
               where [CompanyID]=t.[CompanyID] and [materialID]=t.[materialID]
               and [FromDate]>t.[FromDate] order by [FromDate]),'9999-12-31')
from tb t
select * from tb
/*
CompanyID materialID  FromDate                EndDate
--------- ----------- ----------------------- -----------------------
Cp01      1           2010-05-01 00:00:00.000 2010-05-03 00:00:00.000
Cp01      1           2010-05-03 00:00:00.000 2010-05-15 00:00:00.000
Cp01      1           2010-05-15 00:00:00.000 9999-12-31 00:00:00.000
Cp01      2           2010-01-01 00:00:00.000 2010-03-01 00:00:00.000
Cp01      2           2010-03-01 00:00:00.000 9999-12-31 00:00:00.000
Cp01      3           2010-02-01 00:00:00.000 9999-12-31 00:00:00.000(6 個資料列受到影響)
*/

Answer 5

--> 测试数据: #t
if object_id('tempdb.dbo.#t') is not null drop table #t
create table #t (CompanyID varchar(4),materialID int,FromDate datetime,EndDate sql_variant)
insert into #t
select 'Cp01',1,'2010-05-01',null union all
select 'Cp01',1,'2010-05-03',null union all
select 'Cp01',1,'2010-05-15',null union all
select 'Cp01',2,'2010-01-01',null union all
select 'Cp01',2,'2010-03-01',null union all
select 'Cp01',3,'2010-02-01',null
go
with t as (
select row_number() over(partition by materialID order by getdate()) rn,
* from #t 
)
update t1 
set t1.EndDate =case when t2.rn is null then '9999-12-31' else t2.FromDate end 
from t t1 left join t t2 on t1.materialID=t2.materialID and t1.rn+1=t2.rn select * from #t
CompanyID materialID  FromDate                EndDate
--------- ----------- ----------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Cp01      1           2010-05-01 00:00:00.000 2010-05-03 00:00:00.000
Cp01      1           2010-05-03 00:00:00.000 2010-05-15 00:00:00.000
Cp01      1           2010-05-15 00:00:00.000 9999-12-31 00:00:00.000
Cp01      2           2010-01-01 00:00:00.000 2010-03-01 00:00:00.000
Cp01      2           2010-03-01 00:00:00.000 9999-12-31 00:00:00.000
Cp01      3           2010-02-01 00:00:00.000 9999-12-31 00:00:00.000(6 行受影响)

Answer 6

create table tb
(CompanyID varchar(10),materialID int,FromDate datetime, EndDate datetime)
insert tb
select 'Cp01', 1 ,'2010-05-01',null union all
select 'Cp01', 1 ,'2010-05-03',null union all
select 'Cp01', 1 ,'2010-05-15',null union all
select 'Cp01', 2 ,'2010-01-01',null union all
select 'Cp01', 2 ,'2010-03-01',null union all
select 'Cp01', 3 ,'2010-02-01',nullalter table tb add rowid int identity(1,1)update a set EndDate= isnull(b.FromDate,'9999-12-31')
from tb a left join tb b on a.CompanyID=b.CompanyID and a.materialID=b.materialID
and a.rowid=b.rowid-1 select * from tb
/*
CompanyID  materialID  FromDate                                               EndDate                                                rowid       
---------- ----------- ------------------------------------------------------ ------------------------------------------------------ ----------- 
Cp01       1           2010-05-01 00:00:00.000                                2010-05-03 00:00:00.000                                1
Cp01       1           2010-05-03 00:00:00.000                                2010-05-15 00:00:00.000                                2
Cp01       1           2010-05-15 00:00:00.000                                9999-12-31 00:00:00.000                                3
Cp01       2           2010-01-01 00:00:00.000                                2010-03-01 00:00:00.000                                4
Cp01       2           2010-03-01 00:00:00.000                                9999-12-31 00:00:00.000                                5
Cp01       3           2010-02-01 00:00:00.000                                9999-12-31 00:00:00.000                                6（所影响的行数为 6 行）
*/
alter table tb drop column rowid

Answer 7

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([CompanyID] varchar(4),[materialID] int,[FromDate] datetime,[EndDate] sql_variant)
insert [tb]
select 'Cp01',1,'2010-05-01',null union all
select 'Cp01',1,'2010-05-03',null union all
select 'Cp01',1,'2010-05-15',null union all
select 'Cp01',2,'2010-01-01',null union all
select 'Cp01',2,'2010-03-01',null union all
select 'Cp01',3,'2010-02-01',null--------------------------------查询开始------------------------------
update a set [EndDate]=isnull(
(select top 1 FromDate from tb 
where [CompanyID]=a.[CompanyID] and [materialID]=a.[materialID]  and [FromDate]>a.[FromDate]order by [FromDate]
),'9999-12-31')from tb a
select * from [tb]
/*
CompanyID materialID  FromDate                EndDate
--------- ----------- ----------------------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Cp01      1           2010-05-01 00:00:00.000 2010-05-03 00:00:00.000
Cp01      1           2010-05-03 00:00:00.000 2010-05-15 00:00:00.000
Cp01      1           2010-05-15 00:00:00.000 9999-12-31 00:00:00.000
Cp01      2           2010-01-01 00:00:00.000 2010-03-01 00:00:00.000
Cp01      2           2010-03-01 00:00:00.000 9999-12-31 00:00:00.000
Cp01      3           2010-02-01 00:00:00.000 9999-12-31 00:00:00.000(6 行受影响)
*/

Answer 8

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
CompanyID VARCHAR(20),materialID INT,FromDate DATETIME, EndDate DATETIME
)
INSERT INTO TB
SELECT 'Cp01', 1 ,'2010-05-01',NULL UNION ALL
SELECT 'Cp01', 1 ,'2010-05-03',NULL UNION ALL
SELECT 'Cp01', 1 ,'2010-05-15',NULL UNION ALL
SELECT 'Cp01', 2 ,'2010-01-01',NULL UNION ALL
SELECT 'Cp01', 2 ,'2010-03-01',NULL UNION ALL
SELECT 'Cp01', 3 ,'2010-02-01',NULL
;WITH MU AS (
SELECT 
ROW_NUMBER() OVER (PARTITION BY COMPANYID,MATERIALID ORDER BY FROMDATE) 'DATEID'
,* FROM TB
)
UPDATE T1 SET T1.ENDDATE=T2.ENDDATE
FROM TB T1
INNER JOIN (
SELECT 
T1.COMPANYID,T1.MATERIALID,T1.FROMDATE,ISNULL(T2.FROMDATE,'9999-12-31') 'ENDDATE'
FROM MU T1
LEFT JOIN MU T2 ON T1.COMPANYID=T2.COMPANYID AND T1.MATERIALID=T2.MATERIALID AND T1.DATEID=T2.DATEID-1
) T2 ON T1.COMPANYID=T2.COMPANYID AND T1.MATERIALID=T2.MATERIALID AND T1.FROMDATE=T2.FROMDATESELECT * FROM TB
/*
Cp01 1 2010-05-01 00:00:00.000 2010-05-03 00:00:00.000
Cp01 1 2010-05-03 00:00:00.000 2010-05-15 00:00:00.000
Cp01 1 2010-05-15 00:00:00.000 9999-12-31 00:00:00.000
Cp01 2 2010-01-01 00:00:00.000 2010-03-01 00:00:00.000
Cp01 2 2010-03-01 00:00:00.000 9999-12-31 00:00:00.000
Cp01 3 2010-02-01 00:00:00.000 9999-12-31 00:00:00.000
*/