多字段排序问题.

igelf 2009-07-10 05:19:41
一个分页存储过程.支持多字段排序.
我想出两种方法.

一种是建临时表.
一种是top not in的方法.

哪种方法效率高,指点下.
...全文
379 24 打赏 收藏 转发到动态 举报
写回复
用AI写文章
24 条回复
切换为时间正序
请发表友善的回复…
发表回复
zzz1975 2009-07-12
  • 打赏
  • 举报
 回复
存储过程不要滥用，临时表也不要滥用，否则一旦执行出错，必须要退出系统断开连接，否则就得在创建临时表前判断临时表是否存在

但是一般不会出错的

not in 本人也认为效率不高
devilidea 2009-07-12
  • 打赏
  • 举报
 回复
学习
ai_li7758521 2009-07-11
  • 打赏
  • 举报
 回复 
--=======================================================

--邹建

--=======================================================





-----------------------------------------------------------------------------------------------------

--TOP n 实现的通用分页存储过程(转自邹建)

CREATE PROC sp_PageView

@tbname     sysname,               --要分页显示的表名

@FieldKey   nvarchar(1000),      --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段

@PageCurrent int=1,               --要显示的页码

@PageSize   int=10,                --每页的大小(记录数)

@FieldShow nvarchar(1000)='',      --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段

@FieldOrder nvarchar(1000)='',      --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC

                                          用于指定排序顺序

@Where    nvarchar(1000)='',     --查询条件

@PageCount 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(@Where,N'')=N''

    SET @Where=N''

ELSE

    SET @Where=N'WHERE ('+@Where+N')'



--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)

IF @PageCount IS NULL

BEGIN

    DECLARE @sql nvarchar(4000)

    SET @sql=N'SELECT @PageCount=COUNT(*)'

        +N' FROM '+@tbname

        +N' '+@Where

    EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT

    SET @PageCount=(@PageCount+@PageSize-1)/@PageSize

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' '+@Where

        +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',

            @Where=REPLACE(@Where,@Field,N'a.'+@Field),

            @FieldOrder=REPLACE(@FieldOrder,@Field,N'a.'+@Field),

            @FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field)

    SELECT @Where=REPLACE(@Where,@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 @Where='' THEN N'WHERE ('

            ELSE @Where+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 '+@Where

        +N' '+@FieldOrder

        +N')b ON '+@Where1

        +N' '+@Where2

        +N' '+@FieldOrder)
ai_li7758521 2009-07-11
  • 打赏
  • 举报
 回复
支持多字段排序的分页：
--===========================================================

--高效分页存储过程，仅适用于Sql2005

--===========================================================

set ANSI_NULLS ON 

set QUOTED_IDENTIFIER ON 

go 

-- ============================================= 

-- Description:    <高效分页存储过程，仅适用于Sql2005> 

-- Notes:        <排序字段强烈建议建索引> 

-- Page2005 'dyj_vclient','client_id,last_dt','client_id','last_dt','desc','',20,1,0 

-- ============================================= 

ALTER Procedure [dbo].[Page2005] 

@TableName varchar(50),          --表名 

@Fields varchar(5000) = '*',      --字段名(全部字段为*) 

@PrimaryKey varchar(20),          --主键 

@OrderField varchar(5000),        --排序字段(必须!支持多字段) 

@Orderby varchar(8)='desc',      --排序方式(desc：倒序，asc：顺序) 

@sqlWhere varchar(5000) = Null,  --条件语句(不用加where) 

@pageSize int,                    --每页多少条记录 

@pageIndex int = 1 ,              --指定当前为第几页 

@TotalPage int output            --返回总页数 

as 

begin 

    Begin Tran --开始事务 

    Declare @sql nvarchar(4000); 

    Declare @totalRecord int;    

    if isnull(@PrimaryKey,'')='' 

      set @PrimaryKey='*' 

    --计算总记录数 

        

    if (@SqlWhere='' or @sqlWhere=NULL) 

        set @sql = 'select @totalRecord = count('+ @PrimaryKey +') from ' + @TableName 

    else 

        set @sql = 'select @totalRecord = count('+ @PrimaryKey +') from ' + @TableName + ' where ' + @sqlWhere 

    EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数        

    

    --计算总页数 

    select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize) 

    if (@SqlWhere='' or @sqlWhere=NULL) 

        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ' '+ @Orderby +') as rowId,' + @Fields + ' from ' + @TableName 

    else 

        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ' '+ @Orderby +') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere    

        

    

    --处理页数超出范围情况 

    if @PageIndex <=0 

        Set @pageIndex = 1 

    

    if @pageIndex>@TotalPage 

        Set @pageIndex = @TotalPage 

    --处理开始点和结束点 

    Declare @StartRecord int 

    Declare @EndRecord int 

    

    set @StartRecord = (@pageIndex-1)*@PageSize + 1 

    set @EndRecord = @StartRecord + @pageSize - 1 

    --继续合成sql语句 

    set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord) 

    

    Exec(@Sql) 

    --------------------------------------------------- 

    If @@Error <> 0 

      Begin 

        RollBack Tran 

        Return -1 

      End 

    Else 

      Begin 

        Commit Tran 

        Return @totalRecord ---返回记录总数 

      End    

end
ai_li7758521 2009-07-11
  • 打赏
  • 举报
 回复
几十万的数据量可能就可以看到临时表的优势了。
SQL77 2009-07-11
  • 打赏
  • 举报
 回复
[Quote=引用 15 楼 herowang 的回复:]
引用 4 楼 haiwer 的回复:
数据量大用临时表，否则怎么都可以


顶
[/Quote]
0.0
Tomzzu 2009-07-11
  • 打赏
  • 举报
 回复
数据量小的情况下, top not in 比较高效, 因为如果采用表变量节省了IO操作, 临时表是写硬盘的
数量量大的情况下自然临时表快了, 因为 not in 此时显得很笨拙, 表变量太耗内存不现实
yuangang1011 2009-07-11
  • 打赏
  • 举报
 回复
学习
redhat4 2009-07-11
  • 打赏
  • 举报
 回复
人力资源管理培训咨询项目遇到同样问题

学习
叶子 2009-07-11
  • 打赏
  • 举报
 回复
个人认为top not in的效率低
haitao 2009-07-11
  • 打赏
  • 举报
 回复
left join可以取代in
而且可以是轻松达到(f1,f2,...fn) in (select fx1,fx2,...,fxn from ...)的效果（即语法上还不支持的多字段元组的in）
让你望见影子的墙 2009-07-11
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 haiwer 的回复:]
数据量大用临时表，否则怎么都可以

[/Quote]
顶
plglenn6 2009-07-11
  • 打赏
  • 举报
 回复
期待高手出现
无锡阔微信息科技 2009-07-11
  • 打赏
  • 举报
 回复
学习
ChinaJiaBing 2009-07-10
  • 打赏
  • 举报
 回复 


---分页一条件语句



Select * FROM ( 

    select ROW_NUMBER() Over(order by 排序字段 desc) as rowId,* from 数据表 

) 

where rowId between 页面大小*(页号-1) and 页面大小*页号
低代码开发平台 2009-07-10
  • 打赏
  • 举报
 回复
选择临时表
htl258_Tony 2009-07-10
  • 打赏
  • 举报
 回复
[Quote=引用 6 楼 igelf 的回复:]
数据量大.具体是指多大?
[/Quote]
至少百万级吧.
claro 2009-07-10
  • 打赏
  • 举报
 回复
TB
igelf 2009-07-10
  • 打赏
  • 举报
 回复
数据量大.具体是指多大?
igelf 2009-07-10
  • 打赏
  • 举报
 回复
有空我会去测试下.
有谁做过的.哪个快.
加载更多回复（4）
stream.sorted（）多字段排序及foreach的参数问题
（1）多字段排序：使用Comparator.comparing，此时会对第一字段进行排序，如果第一字段相等，此时进入第二字段的排序Comparator.comparing（）.thenComparing（），接着，第三个字段···第n个字段均可使用....
Collections.sort 依据多个字段排序
今天有个需求，需要根据用户报名的课程进行排序。...在使用Collections.sort，最后写出来的效果，利用多个字段，对列表进行排序 Collections.sort(result, new Comparator() { @Override public int compa
Collections.sort多字段排序-指定排序方式-指定排序字段
1、创建需要排序的字段数组 String [] sortNameArr = {“one”, “tow”,“startDate”}; 2、为每个字段执行排序规则 boolean[] isAs = {false, false, false}; 3、重写sort方法进行排序 ListUtils.sort(list, ...
Springboot data jpa 多字段排序问题
Springboot data jpa 多字段排序问题 对于正常排序，我们可以用如下方式： Sort sort = new Sort(Sort.Direction.DESC, "id"); 但是如果我们想根据多条字段排序呢？ 比如想根据第一个字段正序，结果再根据第二个...
mysql order by 多个字段排序
工作中需用到order by后两个字段排序，但结果却产生了一个Bug，以此备录。 【1】复现问题场景 为了说明问题，模拟示例数据库表students，效果同实例。 如下语句Sql_1： 1 SELECT * FROM students st ORDER BY ...
MS-SQL Server

34,593

社区成员

254,590

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章