需求:筛选出userid对应最大日期的数据,并得到特定时间在筛选出数据对应的ID
建表
create table #a_aa_a(
t_id int,
t_uesrid INT ,
t_time varchar(10)
)
insert into #a_aa_a VALUES (1,1,'20200101')
insert into #a_aa_a VALUES (2,1,'20210101')
insert into #a_aa_a VALUES (3,1,'20220101')
insert into #a_aa_a VALUES (4,2,'20210101')
insert into #a_aa_a VALUES (5,2,'20220101')
insert into #a_aa_a VALUES (6,2,'20230101')
insert into #a_aa_a VALUES (7,3,'20210101')
insert into #a_aa_a VALUES (8,3,'20220101')
insert into #a_aa_a VALUES (9,3,'20230101')
先得到userid对应最大日期的数据
SELECT t_uesrid,MAX(t_time) t_time FROM #a_aa_a
GROUP BY t_uesrid
得到结果
得到特定时间对应的ID:
错误语句
SELECT t_uesrid,MAX(t_time) t_time FROM #a_aa_a
where t_time=20210101 GROUP BY t_uesrid
正确语句
例1
select * from (
SELECT t_uesrid,MAX(t_time) t_time FROM #a_aa_a
GROUP BY t_uesrid)t1 where t1.t_time=20230101
例2---注意having后面需要跟聚合函数,max不能丢
SELECT t_uesrid,MAX(t_time) t_time FROM #a_aa_a
GROUP BY t_uesrid having MAX(t_time)=20230101