mysql分组取每组前几条记录(排名)

资料来源：http://www.cnblogs.com/JulyZhang/archive/2011/02/12/1952213.html

按name分组取val最大的值所在行的数据。

  
  

   
   --
   
   方法1：
   
   

   
   select
   
    a.
   
   *
   
    
   
   from
   
    tb a 
   
   where
   
    val 
   
   =
   
    (
   
   select
   
    
   
   max
   
   (val) 
   
   from
   
    tb 
   
   where
   
    name 
   
   =
   
    a.name) 
   
   order
   
    
   
   by
   
    a.name

   
   --
   
   方法2：
   
   

   
   select
   
    a.
   
   *
   
    
   
   from
   
    tb a 
   
   where
   
    
   
   not
   
    
   
   exists
   
   (
   
   select
   
    
   
   1
   
    
   
   from
   
    tb 
   
   where
   
    name 
   
   =
   
    a.name 
   
   and
   
    val 
   
   >
   
    a.val)

   
   --
   
   方法3：
   
   

   
   select
   
    a.
   
   *
   
    
   
   from
   
    tb a,(
   
   select
   
    name,
   
   max
   
   (val) val 
   
   from
   
    tb 
   
   group
   
    
   
   by
   
    name) b 
   
   where
   
    a.name 
   
   =
   
    b.name 
   
   and
   
    a.val 
   
   =
   
    b.val 
   
   order
   
    
   
   by
   
    a.name

   
   --
   
   方法4：
   
   

   
   select
   
    a.
   
   *
   
    
   
   from
   
    tb a 
   
   inner
   
    
   
   join
   
    (
   
   select
   
    name , 
   
   max
   
   (val) val 
   
   from
   
    tb 
   
   group
   
    
   
   by
   
    name) b 
   
   on
   
    a.name 
   
   =
   
    b.name 
   
   and
   
    a.val 
   
   =
   
    b.val 
   
   order
   
    
   
   by
   
    a.name

   
   --
   
   方法5
   
   

   
   select
   
    a.
   
   *
   
    
   
   from
   
    tb a 
   
   where
   
    
   
   1
   
    
   
   >
   
    (
   
   select
   
    
   
   count
   
   (
   
   *
   
   ) 
   
   from
   
    tb 
   
   where
   
    name 
   
   =
   
    a.name 
   
   and
   
    val 
   
   >
   
    a.val ) 
   
   order
   
    
   
   by
   
    a.name