些许会常用到的sql语句

这里记录些许个会经常用到的sql语句。

 

P.S.   [Your_Table]  = 你的表名, [nid] = 你表中的主键唯一列.

/*按分页数量取表记录中的数据*/
/*以nid排序, 取20条之后的10条*/SELECT TOP 10 *
FROM Your_Table
WHERE (nid NOT IN
          (SELECT TOP 20 nid
         FROM Your_Table
         ORDER BY nid))
ORDER BY nid

/*取传入条件记录的前一条数据*/
/*取 nid='533' 记录的前一条, 返回记录 nid='532'*/
SELECT *
FROM Your_Table
WHERE (nid =
          (SELECT MAX(nid)
         FROM Your_Table
         WHERE nid < 533))

/*取传入条件记录的后一条数据*/
/*取 nid='533' 记录的后一条, 返回记录 nid='532'*/		 
SELECT *
FROM Your_Table
WHERE (nid =
          (SELECT MIN(nid)
         FROM Your_Table
         WHERE nid > 534))
		 
		 

 

分页存储过程，SQL 2000 数据库下使用的：

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

-- DocumentName       分页存储过程 SQL 2000数据库下使用的

-- 描述               利用SQL查询语句进行分页

-- 输入

--       {

--        @SQL            :    SQL查询语句,示例：'Select * from [TableName]'

--        @Order        :    排序,示例：[ColumnName] [ASC | DESC]

--        @CurPage        :    当前页,示例：0..9

--        @PageRows        :    每页显示的行数,示例：0..9

--        @TotalRecorder:    查询记录总数(输出参数)

--          @IsXML        :    表示返回的结果，0表示以表格形式记录，1表示以XML格式返回记录；默认为0

--       }

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



ALTER PROCEDURE [dbo].[SeparatePage]

    -- Add the parameters for the stored procedure here

    @SQL Nvarchar(2000),

    @Order Nvarchar(20),

    @CurPage int,

    @PageRows int,

    @TotalRecorder int output,

    @IsXML bit = 0

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    SET NOCOUNT ON;

    declare @ExceSQL nvarchar(4000)

    

    --设置开始行号

    declare  @start_row_num AS int

    SET @start_row_num = (@CurPage - 1) * @PageRows

   

    if @CurPage > 1 

        BEGIN

            SET @start_row_num = @start_row_num + 1;

            SET @PageRows = @PageRows - 1

        END

    else

        SET @start_row_num = @start_row_num

    

    --获取总记录数

    set @ExceSQL = 'SELECT tb.* into [tb_Temp] FROM ('+ @SQL +') tb ORDER BY'+ @order

    execute(@ExceSQL)

    SELECT @TotalRecorder = COUNT(*) from [tb_Temp]



    ALTER TABLE [tb_Temp] add [RowNumber] int



    --设置查询语句

    SET @ExceSQL = 'SELECT TOP '+ convert(varchar(10),@PageRows) +' * 

                    FROM (SELECT TOP '+ convert(varchar(10),@PageRows) +' * 

                    FROM (SELECT TOP '+ convert(varchar(10),@TotalRecorder-@start_row_num+1) +' *

                    FROM [tb_Temp] ORDER BY '+ REPLACE(@Order,'DESC','ASC') +') AS a

                    ORDER BY '+ REPLACE(@Order,'ASC','DESC') +') AS b'



    IF(@IsXML = 1)

        SET @ExceSQL = @ExceSQL + ' FOR XML AUTO,ELEMENTS'

 

    EXECUTE(@ExceSQL)

    DROP Table [tb_Temp]

END

 

分页存储过程，SQL 2005 数据库下使用的：

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go


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

-- DocumentName       分页存储过程  SQL 2005数据库下使用

-- 描述                 利用SQL查询语句进行分页

-- 输入

--       {

--        @SQL            :    SQL查询语句,示例：'Select * from [TableName]'

--        @Order        :    排序,示例：[ColumnName] [ASC | DESC]

--        @CurPage        :    当前页,示例：0..9

--        @PageRows        :    每页显示的行数,示例：0..9

--        @TotalRecorder:    查询记录总数(输出参数)

--          @IsXML        :    表示返回的结果，0表示以表格形式记录，1表示以XML格式返回记录；默认为0

--       }

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



ALTER PROCEDURE [dbo].[SeparatePage]

    -- Add the parameters for the stored procedure here

    @SQL Nvarchar(2000),

    @Order Nvarchar(20),

    @CurPage int,

    @PageRows int,

    @TotalRecorder int output,

    @IsXML bit = 0

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    SET NOCOUNT ON;

    declare @ExceSQL nvarchar(4000)

    

    --设置开始行号

    declare  @start_row_num AS int

    SET @start_row_num = (@CurPage - 1) * @PageRows

   

    if @CurPage > 1 

        BEGIN

            SET @start_row_num = @start_row_num + 1;

            SET @PageRows = @PageRows - 1

        END

    else

        SET @start_row_num = @start_row_num

    

    --设置标签语句

    declare @RowNumber nvarchar(100)

    set @RowNumber = ', ROW_NUMBER() OVER(ORDER BY ' + @Order + ') as RowNumber from '



    set @SQL = Replace(@SQL,' from ',@RowNumber)



    --获取总记录数

    set @ExceSQL = 'WITH [TempTable] AS (' + @SQL + ') 

        select @TotalRecorder=max(RowNumber) from [TempTable]'



    execute sp_executesql @ExceSQL,N'@TotalRecorder int output',@TotalRecorder output



    --设置查询语句

    set @ExceSQL = 'WITH [TempTable] AS (' + @SQL + ') 

        select * from [TempTable] where RowNumber between ' + Convert(nvarchar,@start_row_num)

        + ' And ' + Convert(nvarchar,@start_row_num+@PageRows)

    

    IF(@IsXML = 1)SET @ExceSQL = @ExceSQL + 'FOR XML AUTO,ELEMENTS'

 

    execute(@ExceSQL)



END