sql编写

[Purplme]

表
code invtp date
1001 A 2018-01-01
1001 B 2018-03-02
1001 C 2018-04-01
1002 AA 2018-01-01
1002 BB 2018-02-28
1003 CC 2018-01-01
结果：
code invtp start_date end_date
1001 A 2018-01-01 2018-03-01
1001 B 2018-03-02 2018-03-30
1001 C 2018-04-01 3000-12-31
1002 AA 2018-01-01 2018-02-27
1002 BB 2018-02-28 3000-12-31
1003 CC 2018-01-01 3000-12-31

怎么实现？

[早起晚睡]

没说明就直接得到后面的数据！！

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL 
	DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[code] NVARCHAR(10)
,[invtp] NVARCHAR(10)
,[date] DATE	
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'1001',N'A',N'2018-01-01')
INSERT INTO dbo.[t] VALUES(N'1001',N'B',N'2018-03-02')
INSERT INTO dbo.[t] VALUES(N'1001',N'C',N'2018-04-01')
INSERT INTO dbo.[t] VALUES(N'1002',N'AA',N'2018-01-01')
INSERT INTO dbo.[t] VALUES(N'1002',N'BB',N'2018-02-28')
INSERT INTO dbo.[t] VALUES(N'1003',N'CC',N'2018-01-01')

;WITH cte AS (
	SELECT row_number() OVER (ORDER BY code,invtp) AS rid,* 
	FROM t
)
SELECT 
a.code
,a.invtp
,a.[date] AS [start_date]
,isnull(dateadd(day,-1,b.[date]),'3000-12-31') AS [end_date]
FROM cte AS a LEFT JOIN cte AS b ON a.rid=b.rid-1
/*
code       invtp      start_date end_date
---------- ---------- ---------- ----------
1001       A          2018-01-01 2018-03-01
1001       B          2018-03-02 2018-03-31
1001       C          2018-04-01 2017-12-31
1002       AA         2018-01-01 2018-02-27
1002       BB         2018-02-28 2017-12-31
1003       CC         2018-01-01 3000-12-31
 */

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([code] int,[invtp] nvarchar(22),[date] Date)
Insert #T
select 1001,N'A','2018-01-01' union all
select 1001,N'B','2018-03-02' union all
select 1001,N'C','2018-04-01' union all
select 1002,N'AA','2018-01-01' union all
select 1002,N'BB','2018-02-28' union all
select 1003,N'CC','2018-01-01'
Go
--测试数据结束
;WITH cte AS (
Select *,ROW_NUMBER()OVER(ORDER BY code,invtp) rn from #T
)
SELECT a.code,
       a.invtp,
       a.date AS start_date,
       ISNULL(b.date, '3000-12-31') AS end_date
FROM cte a
    LEFT JOIN cte b
        ON a.rn + 1 = b.rn
           AND b.code = a.code;

[Dear SQL(燊)]

sql server 2016+

USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL 
    DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[code] NVARCHAR(10)
,[invtp] NVARCHAR(10)
,[date] DATE   
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'1001',N'A',N'2018-01-01')
INSERT INTO dbo.[t] VALUES(N'1001',N'B',N'2018-03-02')
INSERT INTO dbo.[t] VALUES(N'1001',N'C',N'2018-04-01')
INSERT INTO dbo.[t] VALUES(N'1002',N'AA',N'2018-01-01')
INSERT INTO dbo.[t] VALUES(N'1002',N'BB',N'2018-02-28')
INSERT INTO dbo.[t] VALUES(N'1003',N'CC',N'2018-01-01')


SELECT *,enddate=isnull(dateadd(day,-1,lead([date])over(order by code,invtp)) ,'3000-12-31')
FROM T

code       invtp      date       enddate
---------- ---------- ---------- ----------
1001       A          2018-01-01 2018-03-01
1001       B          2018-03-02 2018-03-31
1001       C          2018-04-01 2017-12-31
1002       AA         2018-01-01 2018-02-27
1002       BB         2018-02-28 2017-12-31
1003       CC         2018-01-01 3000-12-31

[morliz子轩]

还是二十六大师兄看的细致。

[qq_39007465]

Dear SQL大佬的这个lead over用的好，学到了！