学步园

--方法1.求分组数据中最小的前三条
SELECT  t1.*
FROM    dbo.info t1
WHERE   t1.name IN ( SELECT TOP 3
                            t2.name
                     FROM   info t2
                     WHERE  t2.class = t1.class
                     ORDER BY t2.score ASC )
                     ORDER BY t1.class DESC ,t1.score asc
--方法2.求分组数据中最小的前三条
SELECT * FROM
(SELECT  * ,
        ( SELECT    COUNT(1)
          FROM      dbo.info t2
          WHERE     t1.score > t2.score
                    AND t1.class = t2.class
        ) AS num
FROM    dbo.info t1) t
            WHERE num<=2
            ORDER BY t.class DESC,num ASC

 

 

 

以下是表结构

方法1查询后的结果

方法2查询的结果