sql求分组数据中最小的前三条 (转)

--方法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查询的结果

 

 

分类: mssql

好文要顶 关注我 收藏该文 微信分享

qanholas
粉丝 - 159 关注 - 0

+加关注

0

0

升级成为会员