22,209
社区成员
发帖
与我相关
我的任务
分享
SELECT id ,
MAX(time) AS [time]
INTO #temp
FROM #T3
GROUP BY id;
SELECT #T1.id ,
#T1.name ,
COUNT(1) AS 次数
FROM #T1
JOIN #temp AS t3 ON t3.id = #T1.id
JOIN #T2 ON #T2.id = #T1.id
AND #T2.time > t3.time
GROUP BY #T1.id ,
#T1.name;
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([id] int,[name] nvarchar(22))
Insert #T1
select 1,N'张三' union all
select 2,N'李四' union all
select 3,N'王五'
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([id] int,[time] Date)
Insert #T2
select 1,'2017-05-01' union all
select 1,'2017-05-02' union all
select 1,'2017-05-03' union all
select 2,'2017-05-02' union all
select 2,'2017-05-03' union all
select 2,'2017-05-04' union all
select 3,'2017-05-03' union all
select 3,'2017-05-04' union all
select 3,'2017-05-05' union all
select 3,'2017-05-06'
GO
if not object_id(N'Tempdb..#T3') is null
drop table #T3
Go
Create table #T3([id] int,[time] Date)
Insert #T3
select 1,'2017-05-01' union all
select 2,'2017-05-01' union all
select 2,'2017-05-02' union all
select 3,'2017-05-03' union all
select 3,'2017-05-04'
Go
--测试数据结束
SELECT #T1.id ,
#T1.name ,
COUNT(1) AS 次数
FROM #T1
JOIN ( SELECT id ,
MAX(time) AS [time]
FROM #T3
GROUP BY id
) AS t3 ON t3.id = #T1.id
JOIN #T2 ON #T2.id = #T1.id
AND #T2.time > t3.time
GROUP BY #T1.id ,
#T1.name
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([id] int,[name] nvarchar(22))
Insert #T1
select 1,N'张三' union all
select 2,N'李四' union all
select 3,N'王五'
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([id] int,[time] Date)
Insert #T2
select 1,'2017-05-01' union all
select 1,'2017-06-02' union all
select 1,'2017-06-03' union all
select 2,'2017-05-02' union all
select 2,'2017-05-03' union all
select 2,'2017-05-04' union all
select 3,'2017-05-03' union all
select 3,'2017-05-04' union all
select 3,'2017-05-05' union all
select 3,'2017-05-06'
GO
if not object_id(N'Tempdb..#T3') is null
drop table #T3
Go
Create table #T3([id] int,[time] Date)
Insert #T3
select 1,'2017-05-01' union ALL
select 1,'2017-06-01' union all
select 2,'2017-05-02' union all
select 3,'2017-05-03'
Go
--测试数据结束
WITH cte AS (
SELECT distinct a.*,max(b.[TIME]) OVER( PARTITION BY b.id) AS maxtime FROM #T1 a
INNER JOIN #t3 b ON a.id=b.id
)
SELECT distinct a.id,a.name,COUNT(b.[time]) OVER( PARTITION BY a.id) FROM cte a
LEFT JOIN #T2 b ON a.id=b.id
WHERE b.[time]>a.maxtime
id name
----------- ---------------------- -----------
1 张三 2
2 李四 2
3 王五 3
表三中有多条记录去最大时间的那一条的写法
select t.id,r.name,count(time) from (
select * from #t2 except select * from #t3
) as t
inner join #t1 as r on r.id=t.id
group by t.id,r.name
id name (No column name)
1 张三 2
2 李四 2
3 王五 3
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([id] int,[name] nvarchar(22))
Insert #T1
select 1,N'张三' union all
select 2,N'李四' union all
select 3,N'王五'
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([id] int,[time] Date)
Insert #T2
select 1,'2017-05-01' union all
select 1,'2017-05-02' union all
select 1,'2017-05-03' union all
select 2,'2017-05-02' union all
select 2,'2017-05-03' union all
select 2,'2017-05-04' union all
select 3,'2017-05-03' union all
select 3,'2017-05-04' union all
select 3,'2017-05-05' union all
select 3,'2017-05-06'
GO
if not object_id(N'Tempdb..#T3') is null
drop table #T3
Go
Create table #T3([id] int,[time] Date)
Insert #T3
select 1,'2017-05-01' union all
select 2,'2017-05-02' union all
select 3,'2017-05-03'
Go
--测试数据结束
SELECT #T1.id ,
#T1.name ,
COUNT(1) AS 次数
FROM #T1
JOIN #T3 ON #T3.id = #T1.id
JOIN #T2 ON #T2.id = #T1.id
AND #T2.time > #T3.time
GROUP BY #T1.id ,
#T1.name