sqlserver 分页嵌套Order by

 sqlserver 分页嵌套Order by

 

 

//筛选
                    var whereParts = new List<string>();
                    whereParts.Add(" p.BID=@BID ");
                    if (request.NickName != null)
                    {
                        whereParts.Add(" u.NickName like '%'+ @NickName +'%' ");
                    }
                    var whereSql = "";
                    if (whereParts.Any())
                    {
                        whereSql = " where " + String.Join(" and ", whereParts.ToArray()) + " ";
                    }

                    int topCount = request.PageSize.HasValue ? Convert.ToInt32(request.PageSize) : 10;
                    int startNum = (request.PageNumber.HasValue ? Convert.ToInt32(request.PageNumber) : 0) * topCount;

                    string orderby = " order by " + request.OrderByName + " " + (request.OrderByNum == 0 ? "desc" : "asc");

                    var sql = @" select top " + topCount + " * from ("

                                        + " select row_number() over( " + orderby + " ) as rn,* from ("

                                                 + " select  * from ("
                                                       + " select "
                                                       + " p.GiveToUserID, "
                                                       + " sum(p.[Value]) as TotalMoney, "
                                                       + " u.NickName, "
                                                       + " u.PicUrl, "
                                                       + " sum( case when [Status]=" + (int)TradePercentEnum.HadPayNoRight + " then p.[Value] else 0 end) as  HadPayNoRight,"
                                                       + " sum( case when [Status]=" + (int)TradePercentEnum.HadPayHadRight + " then p.[Value] else 0 end) as HadPayHadRight, "
                                                       + " sum( case when [Status]= " + (int)TradePercentEnum.EndPercentPay + " then p.[Value] else 0 end) as EndPercentPay, "
                                                       //  + " sum( case when [Status]= " + (int)TradePercentEnum.HadPayOutRight + " then p.[Value] else 0 end) as HadPayOutRight, "
                                                       + " sum( case when [Status]=" + (int)TradePercentEnum.HadPaySetingRight + "then p.[Value] else 0 end) as HadPaySetingRight, "
                                                       + " sum( case when [Status]=" + (int)TradePercentEnum.DrawAuditNoPass + "then p.[Value] else 0 end) as DrawAuditNoPass "
                                                       + " from WxTradePercent p "
                                                       + " left join  WxUsers u on p.GiveToUserID=u.UserID " + whereSql
                                                       + " group by p.GiveToUserID , u.NickName, u.PicUrl  "
                                                   + " ) as aaa "
                                         + " ) as bbb"

                              + ")  as A where rn > " + startNum + "  order by A.rn ";

                    var result = _db.Query<WxTradeUserMoney>(sql, request).ToList();

 

=================================================================

文章地址：

https://blog.csdn.net/dxnn520/article/details/7561070

 

sql server 中关于Order By 在子查询嵌套中的出错的解决方法！

 

2012年05月12日 21:37:29 敦厚的曹操 阅读数：3555 标签： sql serversql 更多

个人分类： 1.【Sql Server】

一直以来，总是被困于一旦sql 语句，子查询中有order by 就出错的烦恼，没想到用这一招就解决了，无意中发现，确成了解决方法，。
解决方法是：只要在2个select 后加上一个top 1000000，就行了。呵呵

 

select top 10000000 * into uuu from ( Select top 10000000 Id From aaa order by sort) a

 

-- 先把重复记录提取唯一值，再汇总！

select  top 10000000 SUM(aaa) from (Select top 10000000 MAX(money) as aaa From der group by ddh order by ddh) a

 

搜索

复制