一个union all SQL语句,怎么写分页存储过程?

2009-03-27 06:58:41 379 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

语义分割系列7-Attention Unet（pytorch实现）本文介绍了AttentionUnet模型和其主要中心思想，并在pytorch框架上构建了Attention Unet模型，构建了Attention gate模块，在数据集Camvid上进行复现。阅读详情

select id,cpname from cp where cpname like"%22%" union all select id,cnname from cp2 where cpname like"%22%"

这个SQL语句怎么来写一个分页存储过程?数据库SQL2005有没有分页存储过程可以执行这个SQL语句.?求高手帮忙.

...全文

深入解析Lora模块的工作原理与应用场景本文深入解析了Lora模块的工作原理与应用场景。Lora模块作为物联网领域的核心无线通信组件，凭借其独特的扩频调制与半双工通信技术，实现了远距离、低功耗的数据传输。文章详细拆解了其技术内核，包括空中唤醒等低功耗奥秘，并提供了地址、信道、空中速率等关键参数的实战配置指南。最后，探讨了Lora模块在智能家居、智慧农业、城市监测等广域物联网场景中的具体应用，为开发者提供了从理论到实践的完整参考。阅读详情

aileenpp

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

aileenpp 2009-03-28

打赏
举报

上面这些存储过程,我想执行select id,cpname from cp where cpname like"%22%" union all select id,cnname from cp2 where cpname like"%22%" 这个SQL语句,用ASP怎么调用?

必用我用第二个存储过程@Tables 是cp

后面的2个WHERE 语句怎么来放置?

aileenpp 2009-03-28

打赏
举报

我先挨着试试,要是有问题还希望大家来顶顶.

aileenpp 2009-03-28

打赏
举报

呵呵,谢谢大家帮忙,不过,我那个SQL语句,可以用这些存储过程吗?我那个语句中有union all ,这个放在上面的分页存储过程里面.好像不行把.

claro 2009-03-27

打赏
举报

帮顶。貌似２楼简单点。

play7788 2009-03-27

打赏
举报

学习。

让你望见影子的墙 2009-03-27

打赏
举报

select id,cpname from cp where cpname like"%22%" union all select id,cnname from cp2 where cpname like"%22%"

先把你的这个结果放到一张临时表中，然后分页，这样会比较简单一些

ai_li7758521 2009-03-27

打赏
举报

--续上...

SET @SqlCount = 'Select @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'          

    + CAST(@PageSize AS VARCHAR)+') FROM (Select * FROM ' + @TableName + @new_where1+') AS T'          

    IF @RecorderCount  = 0          

        BEGIN          

            EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',          

            @TotalCount OUTPUT,@TotalPageCount OUTPUT          

        END          

    ELSE          

        BEGIN          

            Select @TotalCount = @RecorderCount        

        END          

    IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)          

        BEGIN          

            SET @PageIndex =  CEILING((@TotalCount+0.0)/@PageSize)          

        END          

    IF @PageIndex = 1 or @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)          

        BEGIN          

            IF @PageIndex = 1 --返回第一页数据          

                BEGIN          

                    SET @Sql = 'Select * FROM (Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '          

                    + @TableName + @new_where1 + @new_order1 +') AS TMP ' + @new_order1    

                END          

            IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)  --返回最后一页数据          

                BEGIN          

                    SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('          

                    + 'Select TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))          

                    + ' ' + @FieldList + ' FROM '          

                    + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '          

                    + @new_order1          

                END          

        END          

    ELSE      

            

        BEGIN          

        IF @SortType = 1  --仅主键正序排序          

            BEGIN          

                IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2  --正向检索          

                    BEGIN          

                        SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '          

                        + @TableName + @new_where2 + @PrimaryKey + ' > '          

                        + '(Select MAX(' + @PrimaryKey + ') FROM (Select TOP '          

                        + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey          

                        + ' FROM ' + @TableName          

                        + @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1          

                    END          

                ELSE  --反向检索          

                    BEGIN          

                        SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('          

                        + 'Select TOP ' + STR(@PageSize) + ' '          

                        + @FieldList + ' FROM '          

                        + @TableName + @new_where2 + @PrimaryKey + ' < '          

                        + '(Select MIN(' + @PrimaryKey + ') FROM (Select TOP '         

                        + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey          

                        + ' FROM ' + @TableName          

                        + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2          

                        + ' ) AS TMP ' + @new_order1          

                    END          

            END          

        IF @SortType = 2  --仅主键反序排序          

            BEGIN          

                IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2  --正向检索          

                    BEGIN          

                        SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '          

                        + @TableName + @new_where2 + @PrimaryKey + ' < '          

                        + '(Select MIN(' + @PrimaryKey + ') FROM (Select TOP '          

                        + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey          

                        +' FROM '+ @TableName          

                        + @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1          

                    END          

                ELSE  --反向检索          

                    BEGIN          

                        SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('          

                        + 'Select TOP ' + STR(@PageSize) + ' '          

                        + @FieldList + ' FROM '          

                        + @TableName + @new_where2 + @PrimaryKey + ' > '          

                        + '(Select MAX(' + @PrimaryKey + ') FROM (Select TOP '          

                        + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey          

                        + ' FROM ' + @TableName          

                        + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2          

                        + ' ) AS TMP ' + @new_order1          

                    END          

            END          

        IF @SortType = 3  --多列排序，必须包含主键，且放置最后，否则不处理          

            BEGIN          

                IF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0          

                    BEGIN PRINT('ERR_02') RETURN END          

                    IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2  --正向检索          

                        BEGIN          

                            SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '          

                            + 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '          

                            + ' Select TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList          

                            + ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP '          

                            + @new_order2 + ' ) AS TMP ' + @new_order1          

                        END          

                    ELSE  --反向检索          

                        BEGIN          

                            SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '          

                            + 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '          

                            + ' Select TOP ' + STR(@TotalCount-@PageSize *@PageIndex+@PageSize) + ' ' + @FieldList          

                            + ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '          

                            + @new_order1 + ' ) AS TMP ' + @new_order1          

                        END          

            END          

        END          

    PRINT(@SQL)          

    EXEC(@Sql)

ai_li7758521 2009-03-27

打赏
举报

Create PROC P_viewPage         

    /*          

    nzperfect [no_mIss] 高效通用分页存储过程(双向检索) 2007.5.7  QQ:34813284          

    敬告：适用于单一主键或存在唯一值列的表或视图          

    ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围          

    */          

    @TableName VARCHAR(200),     --表名          

    @FieldList VARCHAR(2000),    --显示列名，如果是全部字段则为*          

    @PrimaryKey VARCHAR(100),    --单一主键或唯一值键          

    @Where VARCHAR(2000),        --查询条件 不含'where'字符，如id>10 and len(userid)>9          

    @Order VARCHAR(1000),        --排序 不含'order by'字符，如id asc,userid desc，必须指定asc或desc          

    --注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷          

    @SortType INT,               --排序规则 1:正序asc 2:倒序desc 3:多列排序方法          

    @RecorderCount INT,          --记录总数 0:会返回总记录          

    @PageSize INT,               --每页输出的记录数          

    @PageIndex INT,              --当前页数          

    @TotalCount INT OUTPUT ,      --记返回总记录          

    @TotalPageCount INT OUTPUT   --返回总页数          

AS          

SET NOCOUNT ON          

    IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0          

    SET @Order = RTRIM(LTRIM(@Order))          

    SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))          

    SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')          

    WHILE CHARINDEX(', ',@Order) > 0 or CHARINDEX(' ,',@Order) > 0          

        BEGIN          

            SET @Order = REPLACE(@Order,', ',',')          

            SET @Order = REPLACE(@Order,' ,',',')          

        END          

    IF ISNULL(@TableName,'') = '' or ISNULL(@FieldList,'') = ''          

            or ISNULL(@PrimaryKey,'') = ''          

            or @SortType < 1 or @SortType >3          

            or @RecorderCount  < 0 or @PageSize < 0 or @PageIndex < 0          

        BEGIN          

            PRINT('ERR_00')          

            RETURN          

        END          

    IF @SortType = 3          

        BEGIN          

            IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')          

                BEGIN PRINT('ERR_02') RETURN END          

        END          

    DECLARE @new_where1 VARCHAR(1000)          

    DECLARE @new_where2 VARCHAR(1000)          

    DECLARE @new_order1 VARCHAR(1000)          

    DECLARE @new_order2 VARCHAR(1000)          

    DECLARE @new_order3 VARCHAR(1000)          

    DECLARE @Sql VARCHAR(8000)          

    DECLARE @SqlCount NVARCHAR(4000)          

    IF ISNULL(@where,'') = ''          

        BEGIN          

            SET @new_where1 = ' '          

            SET @new_where2 = ' Where  '          

        END          

    ELSE          

        BEGIN          

            SET @new_where1 = ' Where ' + @where          

            SET @new_where2 = ' Where ' + @where + ' AND '          

        END          

    IF ISNULL(@order,'') = '' or @SortType = 1  or @SortType = 2          

        BEGIN          

            IF @SortType = 1          

                BEGIN          

                    SET @new_order1 = ' orDER BY ' + @PrimaryKey + ' ASC'          

                    SET @new_order2 = ' orDER BY ' + @PrimaryKey + ' DESC'          

                END          

            IF @SortType = 2          

                BEGIN          

                    SET @new_order1 = ' orDER BY ' + @PrimaryKey + ' DESC'          

                    SET @new_order2 = ' orDER BY ' + @PrimaryKey + ' ASC'          

                END          

        END          

    ELSE          

        BEGIN          

            SET @new_order1 = ' orDER BY ' + @Order          

        END          

    

    IF @SortType = 3 AND  CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0          

    BEGIN          

        SET @new_order1 = ' orDER BY ' + @Order          

        SET @new_order2 = @Order + ','          

        SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')          

        SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')          

        SET @new_order2 = ' orDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)          

        IF @FieldList <> '*'          

            BEGIN          

                SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',')          

                SET @FieldList = ',' + @FieldList          

                WHILE CHARINDEX(',',@new_order3)>0          

                    BEGIN          

                        IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0          

                            BEGIN          

                                SET @FieldList =          

                                @FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))          

                            END          

                        SET @new_order3 =          

                        SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))          

                    END          

                SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))          

            END          

        END --待续..

htl258_Tony 2009-03-27

打赏
举报

--SQL2005分页存储过程2007-05-29 11:41-- =============================================



-- Author:   <夏亮>



-- Create date: <2007-5-26>



-- Description: <SQL2005分页存储过程>



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



CREATE PROCEDURE [dbo].[sp_Pagination2]



/***************************************************************



名称:dbo.sp_Pagination2



作者:夏亮



日期:2007-15-26



版本:1.0



***************************************************************



** 千万数量级分页存储过程 **



***************************************************************



参数说明:



1.Tables :表名称,视图，不带dbo.



2.PrimaryKey :主关键字



3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc (Desc Asc前必须加空格)



4.CurrentPage :当前页码



5.PageSize :分页尺寸



6.Fields: 字段名



7.Filter :过滤语句，不带Where



8.Group :Group语句,不带Group By



***************************************************************/



(



@Tables varchar(1000),



@PrimaryKey varchar(100) = NULL,



@Sort varchar(200) = NULL,



@CurrentPage int = 1,



@PageSize int = 20,



@Fields varchar(1000) = '*',



@Filter varchar(1000) = NULL,



@Group varchar(1000) = NULL



)



AS



BEGIN



SET NOCOUNT ON



/*默认排序*/



SET @Sort = RTRIM(LTRIM(@Sort))



IF @Sort IS NULL OR @Sort = ''



SET @Sort = @PrimaryKey



DECLARE @SortTable varchar(100)



DECLARE @SortName varchar(100)



DECLARE @SQL1 varchar(4000)



/*设定排序语句.*/



DECLARE @strPageSize varchar(50)



DECLARE @strStartRow varchar(50),@strEndRow varchar(50)



DECLARE @strFilter varchar(1000)



DECLARE @strGroup varchar(1000)



/*默认当前页*/



IF @CurrentPage < 1



SET @CurrentPage = 1



/*设置分页参数.*/



SET @strPageSize = CAST(@PageSize AS varchar(50))



SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))



SET @strEndRow = CAST((@CurrentPage*@PageSize) AS varchar(50))



/*筛选以及分组语句.*/



IF @Filter IS NOT NULL AND @Filter != ''



BEGIN



SET @strFilter = ' Where ' + @Filter + ' '



END



ELSE



BEGIN



SET @strFilter = ''



END



IF @Group IS NOT NULL AND @Group != ''



BEGIN



SET @strGroup = ' GROUP BY ' + @Group + ' '



END



ELSE



BEGIN



SET @strGroup = ''



END



/*执行查询语句*/



SET @SQL1 ='select * from (Select ' + @Fields + ',ROW_NUMBER() OVER(Order By '+@Sort+') as row FROM ' + @Tables + @strFilter + @strGroup + ') a where row between '+@strStartRow+' and '+@strEndRow



--PRINT (@SQL1)



EXEC (@SQL1)



END

sdhdy 2009-03-27

打赏
举报

--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)

END

一个union all SQL语句,怎么写分页存储过程?

相关推荐