四种查询SQL语句方法及两种存储过程

 

//四种查询的方法：

第一种（top）：select top 10 * from dbo.Admin where Id not in(select top 10 Id  from dbo.Admin)

第二种（max）：select top 10 * from dbo.Admin where Id >

              (select Max(Id) from dbo.Admin where Id in(select top 10 Id from dbo.Admin))

第三种（between-and）：select * from dbo.Admin where Id between 11 and 20;

第四种（row-number）：select * from (select *, Row_Number()over(order by Id)as number from dbo.Admin)t 

                      where t.number between 11 and 20;

 

//循环存储过程：

CREATE PROCEDURE XH(@Z int)
as 
declare @a int;
set @a = @Z;
while(@a>0)
begin
print @a;
 set @a = @a - 1;
END

//分页存储过程：

create procedure sqlvj
(
    @YS int,
    @XSTS int,
    @BM varchar(200),
    @ZJ varchar(100),
    @LM varchar(50),
    @PX varchar(100)
) 
as
DECLARE @sql varchar(200);
DECLARE @A1 varchar(50);
DECLARE @A2 Varchar(50);

SET @A1 = CAST((@YS-1)*@XSTS as varchar(50));
SET @A2 = CAST(@YS*@XSTS as varchar(50));
SET @sql = CAST('select ' +@LM+ ' from ' +@BM+ ' where ' + @ZJ+ ' BETWEEN ' +@A1+ ' AND ' +@A2+ ' ORDER BY ' +@ZJ+ ' ' +@PX+ ' ' as varchar(200));
exec(@sql)

//调循环和分页的方法：

exec XH 5;

exec sqlvj 1,10,'Admin','Id','*','ASC';