USE tempdb
GO
--1
IF OBJECT_ID('dbo.[表1]') IS NOT NULL
DROP TABLE dbo.[表1]
GO
CREATE TABLE dbo.[表1](
[ID] NVARCHAR(MAX)
,[UserName] NVARCHAR(MAX)
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[表1] VALUES(N'1',N'A')
INSERT INTO dbo.[表1] VALUES(N'2',N'B')
INSERT INTO dbo.[表1] VALUES(N'3',N'C')
INSERT INTO dbo.[表1] VALUES(N'4',N'D')
INSERT INTO dbo.[表1] VALUES(N'5',N'E')
--2
IF OBJECT_ID('dbo.[表2]') IS NOT NULL
DROP TABLE dbo.[表2]
GO
CREATE TABLE dbo.[表2](
[ID] NVARCHAR(MAX)
,[UserName] NVARCHAR(MAX)
,[UTime] DATETIME
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[表2] VALUES(N'1',N'A',N'2019-8-29 10:20:59')
INSERT INTO dbo.[表2] VALUES(N'2',N'A',N'2019-8-29 10:21:59')
INSERT INTO dbo.[表2] VALUES(N'3',N'A',N'2019-8-29 10:22:59')
INSERT INTO dbo.[表2] VALUES(N'4',N'B',N'2019-8-29 10:23:59')
INSERT INTO dbo.[表2] VALUES(N'5',N'B',N'2019-8-29 10:24:59')
INSERT INTO dbo.[表2] VALUES(N'6',N'B',N'2019-8-29 10:25:59')
INSERT INTO dbo.[表2] VALUES(N'7',N'B',N'2019-8-29 10:26:59')
INSERT INTO dbo.[表2] VALUES(N'8',N'B',N'2019-8-29 10:27:59')
INSERT INTO dbo.[表2] VALUES(N'9',N'B',N'2019-8-29 10:28:59')
----------- 以上为测试表及测试数据 -----------
;with cte as (
select ROW_NUMBER() over(partition by [UserName] order by UTIME desc) as rid,*,datediff(d,B.UTime,getdate()) as diff
from 表2 AS B
where datediff(d,B.UTime,getdate())>=1
)
select * from [表1] as a inner join cte as b
on a.UserName=b.[UserName] and a.UserName='A'
where b.rid=1
你的思维比较混乱， 不太明白你想要的结果是什么。
整理了一下， 你看这个结果是否是你想要的。
