27,579
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID1] int,[fID] int,[tID] int,[TXT] nvarchar(22),[Time] Date)
Insert #T
select 1,5,6,N'aa','2003-1-1' union all
select 1,2,3,N'bb','2002-1-1' union all
select 1,1,2,N'cc','2001-1-1' union all
select 2,1,3,N'dd','2001-1-1' union all
select 2,4,6,N'ee','2002-1-1' union all
select 2,6,7,N'ff','2003-1-1' union all
select 3,1,2,N'gg','2001-1-1' union all
select 3,2,3,N'hh','2002-1-1' union all
select 3,3,4,N'ii','2003-1-1'
Go
--测试数据结束
;WITH cte as (
Select *,ROW_NUMBER()OVER(PARTITION BY ID1 ORDER BY Time) rn from #T
)
SELECT
a.ID1,a.fID,a.tID,a.TXT
FROM
cte a
LEFT JOIN
cte b
ON b.ID1 = a.ID1
AND a.rn + 1 = b.rn
WHERE
a.tID <> ISNULL(b.fID,-1);
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[ID1] INT
,[fID] INT
,[tID] INT
,[TXT] NVARCHAR(20)
,[Time] DATETIME
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'1',N'5',N'6',N'aa',N'2003-1-1')
INSERT INTO dbo.[t] VALUES(N'1',N'2',N'3',N'bb',N'2002-1-1')
INSERT INTO dbo.[t] VALUES(N'1',N'1',N'2',N'cc',N'2001-1-1')
INSERT INTO dbo.[t] VALUES(N'2',N'1',N'3',N'dd',N'2001-1-1')
INSERT INTO dbo.[t] VALUES(N'2',N'4',N'6',N'ee',N'2002-1-1')
INSERT INTO dbo.[t] VALUES(N'2',N'6',N'7',N'ff',N'2003-1-1')
INSERT INTO dbo.[t] VALUES(N'3',N'1',N'2',N'gg',N'2001-1-1')
INSERT INTO dbo.[t] VALUES(N'3',N'2',N'3',N'hh',N'2002-1-1')
INSERT INTO dbo.[t] VALUES(N'3',N'3',N'4',N'ii',N'2003-1-1')
;WITH cte AS (
SELECT ROW_NUMBER () OVER (PARTITION BY id1 ORDER BY [Time]) AS rid,* FROM t
)
SELECT a.* FROM cte AS a INNER JOIN cte AS b ON a.id1=b.id1 AND a.rid=b.rid-1 AND a.fid!=b.fid
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID1] int,[fID] int,[tID] int,[TXT] nvarchar(22),[Time] Date)
Insert #T
select 1,5,6,N'aa','2003-1-1' union all
select 1,2,3,N'bb','2002-1-1' union all
select 1,1,2,N'cc','2001-1-1' union all
select 2,1,3,N'dd','2001-1-1' union all
select 2,4,6,N'ee','2002-1-1' union all
select 2,6,7,N'ff','2003-1-1' union all
select 3,1,2,N'gg','2001-1-1' union all
select 3,2,3,N'hh','2002-1-1' union all
select 3,3,4,N'ii','2003-1-1'
Go
--测试数据结束
;WITH cte as (
Select *,ROW_NUMBER()OVER(PARTITION BY ID1 ORDER BY Time) rn from #T
)
SELECT
a.ID1,a.fID,a.tID,a.TXT
FROM
cte a
LEFT JOIN
cte b
ON b.ID1 = a.ID1
AND a.rn + 1 = b.rn
WHERE
a.tID <> b.fID;