这里记录些许个会经常用到的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
