/*
with cte as
(
select 1 id,1 personid,2 EducationOrder
union all
select 2 id,1 personid,1 EducationOrder
union all
select 3 id,2 personid,1 EducationOrder
union all
select 4 id,3 personid,1 EducationOrder
)
select * from cte
id personid EducationOrder
----------- ----------- --------------
1 1 2
2 1 1
3 2 1
4 3 1
*/
按照personid分组 取EducationOrder大的行的id值
我想要的结果是
/*
id personid EducationOrder
----------- ----------- --------------
1 1 2
3 2 1
4 3 1
*/
------解决思路----------------------
select * from cte T1
WHERE NOT EXISTS
(SELECT 1 FROM cte T2 WHERE T1.personid=T2.personid AND T2.EducationOrder>T1.EducationOrder)