db2 查询前几条,和嵌套

 
 

  
  //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
  
  )