SQLServer之分页问题
?
? ?问题记录:
?
? ?1.order by ?row_id失效?
?
? ?
-- 创建表 BEGIN TRANSACTION GO CREATE TABLE [dbo].[table_01] ( [id] int NOT NULL , [name] nvarchar(1000) NULL , [addr] nvarchar(1000) NULL ) ON [PRIMARY] GO EXECUTE sp_addextendedproperty N‘MS_Description‘, N‘‘, N‘SCHEMA‘, N‘dbo‘, N‘TABLE‘, N‘table_01‘, NULL, NULL GO ALTER TABLE [dbo].[table_01] ADD CONSTRAINT [PK_table_01] PRIMARY KEY CLUSTERED ( [id] ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) GO COMMIT -- 里层数据排序成功,到外层包一层row_id失效了 select * from ( select top 100 percent ROW_NUMBER() over (order by GETDATE() ) as row_id , * from [test].[dbo].[table_01] where [id] > 1 order by [id] desc ) as t where t.row_id > 0 and t.row_id <=500 -- 分页order by 失效 select * from ( select top 100 percent ROW_NUMBER() over ( order by GETDATE() ) as row_id , t1.* from ( select * from [test].[dbo].[table_01] ) as t1 order by t1.id desc ) as t2 where t2.row_id > 0 and t2.row_id <= 500
?
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。