22,302
社区成员




declare @sql varchar(max)
declare @w varchar(max)
set @w = '
with tt as (select ta.*, tb.name bname, tc.name cname from [dbo].[inf_customer] as ta
join [dbo].[inf_custstatus] as tb on ta.statusid=tb.id
join [dbo].[inf_company] as tc on ta.companyid=tc.id
)'
set @sql = @w + 'select cname ,count(*) as count,convert(VARCHAR(10),updatetime,120) as time'
;with tt as (select ta.*, tb.name bname, tc.name cname from [dbo].[inf_customer] as ta
join [dbo].[inf_custstatus] as tb on ta.statusid=tb.id
join [dbo].[inf_company] as tc on ta.companyid=tc.id
)
select @sql = @sql + ',SUM(case bname when '''+Convert(varchar(100),bname)+''' then 1 else 0 end) ['+Convert(varchar(100),bname)+']'
from (select distinct top 1000 bname from tt order by 1) as a
select @sql = @sql+' from tt group by cname,convert(VARCHAR(10),updatetime,120) order by convert(VARCHAR(10),updatetime,120) DESC'
exec(@sql)
/*
功能:生成测试数据.
*/
create table Test_paging(
id int identity(1,1) not null primary key,
TestNumber int not null,
TestName varchar(20) not null,
TestDept varchar(10) not null,
TestDate datetime not null
)
go
with tep(Number,Name,Dept,Date) as
(
select 1,cast('0_testname' as varchar(20)),cast('0_DBA' as varchar(10)),getdate()
union all
select Number+1,cast(cast(Number as varchar(20))+'_testname' as varchar(20)),cast(cast(Number/500 as varchar(10))+'_DBA' as varchar(10)) ,getdate()
from tep
where Number<=20000000
)
insert into Test_paging(TestNumber,TestName,TestDept,TestDate)
select Number,Name,Dept,Date from tep option(maxrecursion 0)
--添加索引(我有测试没有索引的情况,2012的优势更加明显,但是我们的数据库不可能不建索引的,故可忽略没有索引的情况)
create nonclustered index IX_TestDept on Test_paging(
TestDept
) include
(
TestName,TestDate
)
go
/*
功能:测试2012版本中offset/fetch分页.
*/
dbcc dropcleanbuffers
dbcc freeproccache
set statistics io on
set statistics time on
set statistics profile on
declare
@page int, --第@page页
@size int, --每页@size行
@total int --总行数
select @page=20,@size=10,@total=count(1) from Test_paging where TestDept = '1000_DBA'
select
TestName,TestDept,TestDate,@total
from
Test_paging
where
TestDept = '1000_DBA'
order by id offset (@page-1)*@size rows fetch next @size rows only
set statistics io off
set statistics time off
set statistics profile off
/*
功能:测试2005/2008版本中row_number分页.
*/
dbcc dropcleanbuffers
dbcc freeproccache
set statistics io on
set statistics time on
set statistics profile on
declare
@page int, --第@page页
@size int, --每页@size行
@total int
select @page=20,@size=10,@total=count(1) from Test_paging where TestDept = '1000_DBA'
select TestName,TestDept,TestDate,@total from
(
select
TestName,TestDept,TestDate,row_number() over(order by ID) as num
from
Test_paging
where
TestDept = '1000_DBA'
) test where num between (@page-1)*@size+1 and @page*@size order by num
set statistics io off
set statistics time off
set statistics profile off
/*
功能:测试2000版本中top分页.
*/
dbcc dropcleanbuffers
dbcc freeproccache
set statistics io on
set statistics time on
set statistics profile on
declare
@page int, --第@page页
@size int, --每页@size行
@total int --总行数
select @page=20,@size=10,@total=count(1) from Test_paging where TestDept = '1000_DBA'
select TestName,TestDept,TestDate,@total from
(
select top(@size) id,TestName,TestDept,TestDate from
(
select top(@page*@size) id,TestName,TestDept,TestDate
from Test_paging
where TestDept = '1000_DBA'
order by id
)temp1 order by id desc
)temp2 order by id
set statistics io off
set statistics time off
set statistics profile off
这是几种分页形式,根据sql版本来使用。
order by convert(VARCHAR(10),updatetime,120) DESC
这里不已然进行排序了么?
您还要怎么排?