22,207
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([ID] int,[姓名] nvarchar(22),[地点] nvarchar(22),[限定次数] int)
Insert #A
select 1,N'AA',N'XX',2 union all
select 2,N'AA',N'YY',4 union all
select 3,N'BB',N'XX',1 union all
select 4,N'BB',N'YY',2
GO
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([ID] int,[姓名] nvarchar(22),[地点] nvarchar(22),[刷卡时间] DATETIME)
Insert #B
select 1,N'AA',N'XX','2017-01-01 09:00:01' union all
select 2,N'AA',N'XX','2017-01-01 09:30:01' union all
select 3,N'AA',N'XX','2017-01-01 14:50:30' union all
select 4,N'BB',N'XX','2017-01-01 07:12:01' union all
select 5,N'BB',N'XX','2017-01-01 09:32:01' union all
select 6,N'BB',N'YY','2017-01-02 19:32:01'
Go
--测试数据结束
;WITH tempa AS(
SELECT * FROM #A
),tempb AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY 姓名,地点,day(刷卡时间) ORDER BY ID) AS num FROM #B
)
SELECT tempb.ID ,
tempb.姓名 ,
tempb.地点 ,
tempb.刷卡时间 from tempb left JOIN tempa ON tempb.姓名 = tempa.姓名 and tempb.地点 = tempa.地点
where tempb.num <= tempa.限定次数
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([ID] int,[姓名] nvarchar(22),[地点] nvarchar(22),[限定次数] int)
Insert #A
select 1,N'AA',N'XX',2 union all
select 2,N'AA',N'XX',4 union all
select 3,N'BB',N'XX',1 union all
select 4,N'BB',N'YY',2
GO
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([ID] int,[姓名] nvarchar(22),[地点] nvarchar(22),[刷卡时间] DATETIME)
Insert #B
select 1,N'AA',N'XX','2017-01-01 09:00:01' union all
select 2,N'AA',N'XX','2017-01-01 09:30:01' union all
select 3,N'AA',N'XX','2017-01-01 14:50:30' union all
select 4,N'BB',N'XX','2017-01-01 07:12:01' union all
select 5,N'BB',N'XX','2017-01-01 09:32:01' union all
select 6,N'BB',N'YY','2017-01-02 19:32:01'
Go
--测试数据结束
;WITH tempa AS(
SELECT *,ROW_NUMBER()OVER(PARTITION BY 姓名 ORDER BY ID) AS num FROM #A
),tempb AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY 姓名 ORDER BY ID) AS num FROM #B
)
SELECT tempb.ID ,
tempb.姓名 ,
tempb.地点 ,
tempb.刷卡时间
FROM tempa
JOIN tempb ON tempb.num = tempa.num
AND tempb.姓名 = tempa.姓名;