Question

如何在Sql Server上使用一条SQL查询结果总数并且分页

Answer 1

using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

namespace CountTest
{
class Program
{
static string conStr = "Persist Security Info=False;Integrated Security=true;Initial Catalog=TSQLFundamentals2008;server=(local)";
static long nanosecPerTick = (1000L * 1000L * 1000L) / Stopwatch.Frequency;

static void Main(string[] args)
{
test1();

test2();
}

static void test1()
{
var t1 = Stopwatch.StartNew();
using (SqlConnection conn = new SqlConnection(conStr))
{
conn.Open();
var comCount = new SqlCommand(@"select COUNT(*) from
(select emp.empid,SUM(od.unitprice*od.qty) money
from HR.Employees emp
left join Sales.Orders o on o.empid = emp.empid
left join Sales.OrderDetails od on od.orderid = o.orderid
group by emp.empid
) t", conn);
var count = comCount.ExecuteScalar();
var pageCount = new SqlCommand(@"select empid,money from
(select emp.empid,SUM(od.unitprice*od.qty) money,ROW_NUMBER() over(order by emp.empid) rm
from HR.Employees emp
left join Sales.Orders o on o.empid = emp.empid
left join Sales.OrderDetails od on od.orderid = o.orderid
group by emp.empid
) t where rm between 1 and 20", conn);
var dataAdapter = new SqlDataAdapter(pageCount);
dataAdapter.Fill(new DataTable());
}
t1.Stop();
Console.WriteLine("test1方法耗时：{0}纳秒，{1}毫秒", t1.ElapsedTicks * nanosecPerTick, t1.ElapsedMilliseconds);
}

static void test2()
{
var t2 = Stopwatch.StartNew();
using (SqlConnection conn = new SqlConnection(conStr))
{
conn.Open();

var dTable = new DataTable();
var pageCount = new SqlCommand(@"select empid,money,a from
(select emp.empid,SUM(od.unitprice*od.qty) money,ROW_NUMBER() over(order by emp.empid) rm,COUNT(*) over() a
from HR.Employees emp
left join Sales.Orders o on o.empid = emp.empid
left join Sales.OrderDetails od on od.orderid = o.orderid
group by emp.empid
) t where rm between 1 and 20", conn);
var dataAdapter = new SqlDataAdapter(pageCount);
dataAdapter.Fill(dTable);
var count = dTable.Rows.Count > 0 ? dTable.Rows[0].ItemArray[dTable.Rows[0].ItemArray.Length - 1] : 0;
dTable.Columns.RemoveAt(dTable.Columns.Count - 1);
}
t2.Stop();
Console.WriteLine("test2方法耗时：{0}纳秒，{1}毫秒", t2.ElapsedTicks * nanosecPerTick, t2.ElapsedMilliseconds);
}
}
}