sql server记录几个分组取top n数据的写法

数据表：BUS_VisitorStatics 

1、开窗函数实现：

 SELECT  *

FROM    ( SELECT    * ,

                    ROW_NUMBER() OVER ( PARTITION BY visitorDate ORDER BY Count DESC ) AS 'num'

          FROM      BUS_VisitorStatics

        ) AS tb1

WHERE   tb1.num <= 10

 

2、Linq实现：

DbContext dd = new SZAdminEntities();

 list = dd.Set<BUS_VisitorStatics>().OrderByDescending(p => p.Count).ToList();

var result = from n in list

                         group n by n.VisitorDate

                             into g

                             orderby g.Key

                             select new

                             {

                                 date = g.Key,

                                 dateList = g.OrderByDescending(p => p.Count).Take(10),

                             };

var wordlist = new List<BUS_VisitorStatics>();

            foreach (var item in result)

            {

                if (item.dateList.Count() > 0)

                {

                    foreach (var o in item.dateList)

                    {

                        var mod = new BUS_VisitorStatics();

                        mod.VisitorDate = item.date;

                        mod.Count = o.Count;

                        mod.Keyword = o.Keyword;

                        wordlist.Add(mod);

                    }

                }

            }

 return wordlist;

 

3、普通SQL实现：

SELECT  *

FROM    BUS_VisitorStatics AS a

WHERE   ( SELECT    COUNT(*)

          FROM      BUS_VisitorStatics AS b

          WHERE     a.VisitorDate = b.VisitorDate

                    AND a.Count < b.Count

        ) < 10

ORDER BY a.VisitorDate ,a.Count DESC