22,300
社区成员




with t as (
select '9D82766E-67BB-460D-B2AB-29C1350B9F60' AS project_sid,1 AS plan_version_sid,'2015-12-7' AS registerdate union
select '9D82766E-67BB-460D-B2AB-29C1350B9F60',2 ,'2015-12-4' union
select '9D82766E-67BB-460D-B2AB-29C1350B9F60',3 ,'2015-12-1' union
select '114A8783-BCA2-4A58-8814-C2A977BF9E6F',11 ,'2015-08-04' union
select '114A8783-BCA2-4A58-8814-C2A977BF9E6F',21 ,'2015-08-05'
)
SELECT * FROM (
select t.*, ROW_NUMBER()OVER (PARTITION BY project_sid ORDER BY registerdate) as SeqNo from t
) tt WHERE tt.SeqNo=1
如果project_sid,registerdate相同的值存在多行记录,还需要其他字典排序,都放到ROW_NUMBER()OVER (PARTITION BY project_sid ORDER BY register date)中select * from tb t
where not exists(select 1 from tb where project_sid=t.project_sid and registerdate<t.registerdate)
with cte(project_sid,plan_version_sid,registerdate) as (
select '9D82766E-67BB-460D-B2AB-29C1350B9F60', 1 ,'2015-12-7' union
select '9D82766E-67BB-460D-B2AB-29C1350B9F60', 2 ,'2015-12-4' union
select '9D82766E-67BB-460D-B2AB-29C1350B9F60', 3 ,'2015-12-1' union
select '114A8783-BCA2-4A58-8814-C2A977BF9E6F', 11 ,'2015-08-04' union
select '114A8783-BCA2-4A58-8814-C2A977BF9E6F', 21 ,'2015-08-05')
select cte.* from cte,(
select project_sid,min(registerdate)as registerdate from cte group by project_sid) a
where cte.project_sid=a.project_sid and cte.registerdate=a.registerdate