public IList <OrderRecordDetails> GetPageList(int userId, int?shopId, int?orderStateId, int start, int end, out int total) { const string sql = @" SELECT COUNT(1) AS Total FROM OrderRecord ORT WHERE ORT.UserId = @UserId{0} SELECT T.Id , T.UserId , T.UserShopId , T.UserShopWangWangAccount , T.ClientUserId , T.ClientUserAccount , T.ClientUserSubAccountId , T.ClientUserSubAccount , T.ClientUserLogin , T.OrderIp , T.OrderNum , T.OrderStateId , T.OrderTypeId , T.StartDateTime , T.LastUpdateDateTime FROM ( SELECT ORT.Id , ORT.UserId , ORT.UserShopId , ST.WangWangAccount AS UserShopWangWangAccount , ORT.ClientUserId , UT.Account AS ClientUserAccount , ORT.ClientUserSubAccountId , USAT.TaoBaoAccount AS ClientUserSubAccount , ULST.ClientLogin AS ClientUserLogin , ULST.ClientLastLoginIpAddress AS OrderIp , ORT.OrderNum , ORT.OrderStateId , ORT.OrderTypeId , ORT.StartDateTime , ORT.LastUpdateDateTime , ROW_NUMBER() OVER ( ORDER BY ORT.OrderStateId, ORT.Id DESC ) AS RowNum FROM OrderRecord ORT LEFT JOIN Shop ST ON ST.Id = ORT.UserShopId LEFT JOIN [User] UT ON UT.Id = ClientUserId LEFT JOIN UserSubAccount USAT ON USAT.Id = ORT.ClientUserSubAccountId LEFT JOIN UserLoginState ULST ON ULST.UserId = ORT.ClientUserId WHERE ORT.UserId = @UserId{0} ) T WHERE T.RowNum BETWEEN @Start AND @End"; StringBuilder querySb = new StringBuilder(); if (shopId.HasValue) { querySb.Append(" AND ORT.UserShopId = @UserShopId"); } if (orderStateId.HasValue) { querySb.Append(" AND ORT.OrderStateId = @OrderStateId"); } string sqlTmp = string.Format(sql, querySb.ToString()); using (var con = DbFactory.CreateConnection()) { var reader = con.QueryMultiple(sqlTmp, new { UserId = userId, UserShopId = shopId ?? 0, OrderStateId = orderStateId ?? 0, Start = start, End = end }); total = reader.Read <int>().First(); return(reader.Read <OrderRecordDetails>().ToList()); } }