分组排序,取每组前N条记录

 

DB2版本（嵌套版本）

 

 

select * from hr.aaaa t
where name in
(
select  name from hr.aaaa  where id = t.id order by name fetch first 3 row only
)

DB2版本（函数版本）

 

select id,name ,row_number() over(partition by id order by name desc) as idd from hr.aaaa

 

SQL版本（嵌套版本）

 

 

 

select * from hr.aaaa t
where name in
(
select  name from hr.aaaa  where id = t.id order by name fetch first 3 row only
)

 

select * from hr.aaaa t where =id
(select id,avg(out) from  hr.aaaa group by id);

update hr.aaaa set out = 47 where out=42

select * from hr.aaaa t
where out >=
( select avg(out) as out from hr.aaaa where id=t.id group by id)
     
    
select * from hr.aaaa t
where out in
(
select  out from hr.aaaa  where id = t.id order by out desc fetch first 3 row only
)

  
select * from
(
select id,name ,row_number() over(partition by id order by name desc) as idd from hr.aaaa
) z where idd <=3

select * from hr.aaaa t
where out >=
( select avg(out) as out from hr.aaaa where id=t.id group by id)