SqlServer Rownum问题

Question

最近在公司做核心，原来的核心是oracle数据库的，现在改用sqlserver，没有rownum了，分页实现遇到问题了，我在网上也查了一些解决办法，都不是很顺手，不知道有没有更好的解决方法。row_number() over(order by xxx)这种方式必须要单独写出order by条件，不适合我们以前的核心思路，以往我们的核心就是PG拼好一个sql文调用就可以了，分页都是核心处理的，但是现在，用了row_number() over(order by xxx)sql文种不能加order by语句了，我很纠结！top吧只能去前多少条记录，遇到10-20这种就没法取了，有没有做过的达人帮帮忙。万分感谢啊！这是我现在的用法~SELECT *
FROM (SELECT row_number() over(order by A.rzid) as rownum, A.* 
FROM (SELECT
                        *
                        FROM SYS_XTRZ log
                        ) A
     ) B
WHERE B.rownum between 10 and 20;[

Answer 1

SELECT *
FROM (SELECT row_number() over(order by rzid) as rownum,* 
      FROM SYS_XTRZ log
     ) B
WHERE rownum between 10 and 20
这种不行么？想排序就写在row_number() over (order by 排序字段)

Answer 2

因为我现在做的是公司核心函数，别的程序员要拼好sql文，直接调用就好了，他不需要知道我要分页等等，他只要给我sql文就行了，所以你这种方式是不行的，这个sql需要程序员自己加row_number() over (order by ，我就是想封装一下，让别的人不需要这样写，想看看有没有好的方法

Answer 3

也就是说
SELECT *
FROM (SELECT row_number() over(order by A.rzid) as rownum, A.* 
        FROM (
                --[usersql
              ) A
     ) B
WHERE B.rownum between 10 and 20;
[/code]

Answer 4

usersql 是别人写的sql文，他也许还有别的order by条件，不一定是我的rzid，
而且我在写共通函数的时候，rzid这个字段我是不知道的，我也不知道他要检索的是哪张表
主键是什么~

Answer 5

Create Procedure proc_CurrencyPage
(
    @Columns        varchar(max),
    @TableName      varchar(max),
    @Condition      varchar(max),
    @OrderBy        varchar(max),
    @PageNum        int,
    @PageSize       int,
    @PageCount      int output,
    @RecordCount    bigint output
)
AS
    DECLARE @Sql nvarchar(max);
    Set @Sql = 'Select @CountOut = Count(*) From ' + @TableName + ' Where ' + @Condition;
    EXEC sp_executesql @Sql,N'@CountOut INT OUTPUT',@CountOut = @RecordCount OUTPUT;    Set @PageCount = @RecordCount / @PageSize;
    IF(@RecordCount % @PageSize > 0) Set @PageCount = @PageCount + 1;
    IF(@PageNum < 1) Set @PageNum = 1;
    IF(@PageNum > @PageCount) Set @PageNum = @PageCount;
    Declare @BRowNum int,@ERowNum int;
    Set @BRowNum = (@PageNum - 1) * @PageSize;
    Set @ERowNum = @BRowNum + @PageSize;
    Set @Sql = 
       'Select * From (
            Select '+@Columns+', ROW_NUMBER() Over(Order By ' + @OrderBy + ') As RowNum 
            From ' + @TableName + ' 
            Where ' + @Condition + '
        ) as TempT 
        Where RowNum > ' + Convert(varchar(10),@BRowNum) + ' 
              And 
              RowNum <= ' + Convert(varchar(10),@ERowNum) + ' 
              Order By ' + @OrderBy;
    Exec(@Sql);
GO楼主是想要个分页储存过程吧？

Answer 6

--创建个测试表
create table test0810(id int primary key)
insert into test0810
select a.col*1000+b.col*100+c.col*10+d.col+1 as col
from 
(select 0 as col union all select 1 union all select 2 union all 
select 3 union all select 4 union all select 5 union all 
select 6 union all select 7 union all select 8 union all select 9)a
cross join 
(select 0 as col union all select 1 union all select 2 union all 
select 3 union all select 4 union all select 5 union all 
select 6 union all select 7 union all select 8 union all select 9)b
cross join 
(select 0 as col union all select 1 union all select 2 union all 
select 3 union all select 4 union all select 5 union all 
select 6 union all select 7 union all select 8 union all select 9)c
cross join 
(select 0 as col union all select 1 union all select 2 union all 
select 3 union all select 4 union all select 5 union all 
select 6 union all select 7 union all select 8 union all select 9)d
order by col

Answer 7

SELECT *
FROM (SELECT row_number() over(order by getdate()) as rownum, A.*  
  FROM (
  --[usersql
  ) A
  ) B
WHERE B.rownum between 10 and 20;

Answer 8

--使用实例
Declare @Columns     varchar(max)
Declare @TableName   varchar(max)
Declare @Condition   varchar(max)
Declare @OrderBy     varchar(max)
Declare @PageNum     int
Declare @PageSize    int
Declare @PageCount   int
Declare @RecordCount bigintset @Columns = 'ID'
set @TableName = 'test0810'
set @Condition = '1 = 1'
set @ORDERBY = 'ID' --如果有多列自己写例如'name desc,id'
set @PageNum = 2
set @PageSize=20Exec proc_CurrencyPage @Columns,@TableName,@Condition,
@ORDERBY,@PageNum,@PageSize,@PageCount output,@RecordCount output
select @PageCount,@RecordCount

Answer 9

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROC [dbo].[PROCE_PageView2000]
(
@tbname     nvarchar(100),               --要分页显示的表名
@FieldKey   nvarchar(1000),      --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int=1,               --要显示的页码
@PageSize   int=10,                --每页的大小(记录数)
@FieldShow nvarchar(1000)='',      --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='',      --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
@WhereString    nvarchar(1000)=N'',     --查询条件
@RecordCount int OUTPUT             --总记录数
)
AS
SET NOCOUNT ON
--检查对象是否有效
--IF OBJECT_ID(@tbname) IS NULL
--BEGIN
--    RAISERROR(N'对象"%s"不存在',1,16,@tbname)
--    RETURN
--END
--IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
--    AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
--    AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
--BEGIN
--    RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
--    RETURN
--END--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
    RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)
    RETURN
END--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
    SET @FieldOrder=N''
ELSE
    SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@WhereString,N'')=N''
    SET @WhereString=N''
ELSE
    SET @WhereString=N'WHERE ('+@WhereString+N')'--如果@RecordCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@RecordCount赋值)
IF @RecordCount IS NULL
BEGIN
    DECLARE @sql nvarchar(4000)
    SET @sql=N'SELECT @RecordCount=COUNT(*)'
        +N' FROM '+@tbname
        +N' '+@WhereString
    EXEC sp_executesql @sql,N'@RecordCount int OUTPUT',@RecordCount OUTPUT
END--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
    @TopN1=(@PageCurrent-1)*@PageSize--第一页直接显示
IF @PageCurrent=1
    EXEC(N'SELECT TOP '+@TopN
        +N' '+@FieldShow
        +N' FROM '+@tbname
        +N' '+@WhereString
        +N' '+@FieldOrder)
ELSE
BEGIN
    --处理别名
    IF @FieldShow=N'*'
        SET @FieldShow=N'a.*'    --生成主键(惟一键)处理条件
    DECLARE @Where1 nvarchar(4000),@Where2 nvarchar(4000),
        @s nvarchar(1000),@Field sysname
    SELECT @Where1=N'',@Where2=N'',@s=@FieldKey
    WHILE CHARINDEX(N',',@s)>0
        SELECT @Field=LEFT(@s,CHARINDEX(N',',@s)-1),
            @s=STUFF(@s,1,CHARINDEX(N',',@s),N''),
            @Where1=@Where1+N' AND a.'+@Field+N'=b.'+@Field,
            @Where2=@Where2+N' AND b.'+@Field+N' IS NULL',
            @WhereString=REPLACE(@WhereString,@Field,N'a.'+@Field),
            @FieldOrder=REPLACE(@FieldOrder,@Field,N'a.'+@Field),
            @FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field)
    SELECT @WhereString=REPLACE(@WhereString,@s,N'a.'+@s),
        @FieldOrder=REPLACE(@FieldOrder,@s,N'a.'+@s),
        @FieldShow=REPLACE(@FieldShow,@s,N'a.'+@s),
        @Where1=STUFF(@Where1+N' AND a.'+@s+N'=b.'+@s,1,5,N''),    
        @Where2=CASE
            WHEN @WhereString='' THEN N'WHERE ('
            ELSE @WhereString+N' AND ('
            END+N'b.'+@s+N' IS NULL'+@Where2+N')'    --执行查询
    EXEC(N'SELECT TOP '+@TopN
        +N' '+@FieldShow
        +N' FROM '+@tbname
        +N' a LEFT JOIN(SELECT TOP '+@TopN1
        +N' '+@FieldKey
        +N' FROM '+@tbname
        +N' a '+@WhereString
        +N' '+@FieldOrder
        +N')b ON '+@Where1
        +N' '+@Where2
        +N' '+@FieldOrder)
END
 
MSSQL2005set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[PROCE_SQL2005PAGECHANGE]
(
 @TableName varchar(50),            --表名
 @ReFieldsStr varchar(200) = '*',   --字段名(全部字段为*)
 @OrderString varchar(200),         --排序字段(必须!支持多字段不用加order by)
 @WhereString varchar(500) =N'',  --条件语句(不用加where)
 @PageSize int,                     --每页多少条记录
 @PageIndex int = 1 ,               --指定当前为第几页
 @TotalRecord int output            --返回总记录数
)
AS
 
BEGIN        --处理开始点和结束点
    Declare @StartRecord int;
    Declare @EndRecord int; 
    Declare @TotalCountSql nvarchar(500); 
    Declare @SqlString nvarchar(2000);    
    set @StartRecord = (@PageIndex-1)*@PageSize + 1
    set @EndRecord = @StartRecord + @PageSize - 1 
    SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName;--总记录数语句
    SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;--查询语句
    --
    IF (@WhereString! = '' or @WhereString!=null)
        BEGIN
            SET @TotalCountSql=@TotalCountSql + '  where '+ @WhereString;
            SET @SqlString =@SqlString+ '  where '+ @WhereString;            
        END
    --第一次执行得到
    --IF(@TotalRecord is null)
    --   BEGIN
           EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数
    --  END
    ----执行主语句
    set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' +  ltrim(str(@EndRecord));
    Exec(@SqlString)    
END

Answer 10

我是想用这种方式的，可是我的usersql那里依旧不能写order by 语句的，因为实际业务是肯定需要order by的，是不是那样的话就不能用这种方式了~

Answer 11

非常感谢你的回答，但其实写存储过程和我用c#代码自己拼sql语句是一样的效果，你的这个过程也是需要单独的传递order by条件的。

Answer 12

        protected DataSet select(SqlConnection con, string sqlStr, int page , string orderStr) 
        {
            if (string.IsNullOrEmpty(orderStr))
            {
                throw new Exception("排序字符串不能为空");
            }            int srownum = 0; //开始番号
    int erownum = 0; //结束番号            StringBuilder SqlBuilder = new StringBuilder();            //总页数的取得
    SqlBuilder.Append("SELECT COUNT(1) AS CNT FROM (");
    SqlBuilder.Append(sqlStr);
            SqlBuilder.Append(") AS A");            DataSet ds = SqlHelper.ExecuteDataset(con,CommandType.Text, SqlBuilder.ToString());            int iCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);            int modCount = iCount % Constants.PAGE_ITEM_NUM;            if(modCount == 0)
            {
                PageCout = iCount / Constants.PAGE_ITEM_NUM;
            }
            else
            {
                PageCout = iCount / Constants.PAGE_ITEM_NUM + 1;
            }            if (page < 1) {
                page = 1;
            }
            else if (page > PageCout)
            {
                page = PageCout;
    }            CurrentPage = page;            //数据的检索
    SqlBuilder = new StringBuilder();
    if (iCount > 0) {
    srownum = ((page - 1) * Constants.PAGE_ITEM_NUM) + 1;
    erownum = page * Constants.PAGE_ITEM_NUM;
                SqlBuilder.Append("SELECT * FROM ( SELECT row_number() over(order by "+ orderStr +") rownum,A.*  FROM (");
    SqlBuilder.Append(sqlStr);
    SqlBuilder.Append(")");
    SqlBuilder
                    .Append("A ) B WHERE rownum BETWEEN ")
    .Append(srownum)
    .Append(" AND ")
    .Append(erownum);
    } else {
    SqlBuilder.Append(sqlStr);
    }            return SqlHelper.ExecuteDataset(con, CommandType.Text, SqlBuilder.ToString());
        }这是我现在写好的函数，我其实就是希望不加order by 这个参数，因为以前oracle写的时候就是直接加在sqlStr这个参数里面的，不需要单独列出来