group by分组有关问题
group by分组问题
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
*/
------解决思路----------------------
本帖最后由 frankl123 于 2014-12-25 11:52:59 编辑
/*
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)