你要分页,你想取第几页的,每页几条? select top 10 * from tablename order by id desc
你只要把那基本的储存过程中的语句放到SQL语句就行呀。不一定要放到存储过程。。 比如 string SQL ="select * from 表 where 条件 " 如果那里要用直接调用SQL就可以了
--int pagecount ,int pageindex --pagecount 数量,pageindex 页码(程序里面定义,在下面拼接,也可以直接赋值) select * from ( select *,row_number() over(order by id desc) as rows from tb) a where rows between pagecount*(pageindex-1)+1 and pagecount*pageindex --------------------- declare @tb table (id int identity (1,1),val varchar(20)) insert into @tb values('a') insert into @tb values('b') insert into @tb values('c') insert into @tb values('d') insert into @tb values('e') insert into @tb values('f') insert into @tb values('g') insert into @tb values('h') --------------------- select * from ( select *,row_number() over(order by id desc) as rows from tb) a where rows between 2*(2-1)+1 and 2*2 --第二页每页两行 ----------------------- id val rows -- --- ---- 4 d 3 3 c 4
SELECT TOP 20 * FROM Tabsuite WHERE Tabsuite_Id NOT IN ( SELECT TOP 40 Tabsuite_Id FROM Tabsuite ORDER BY Tabsuite_Id desc) and Tabsuite_mianji>=100 and Tabsuite_mianji<=200 and Tabsuite_time <= '2011-4-25 18:00:00' ORDER BY Tabsuite_Id desc各位帮忙给这个跳跳错吧
sql.Append("WITH Result AS(select *, ROW_NUMBER() OVER(order by "); if (orderBy == null || orderBy == "") { sql.Append("默认排序字段"); } else { sql.Append(orderBy); } sql.Append(") as row from 你的表"); if (filter != null && filter != "") { sql.Append(" where "); sql.Append(filter); } sql.Append(") select "); if (filds != null && filds != "") { sql.Append(filds); } else { sql.Append("默认取出的字段"); } sql.Append(" from Result where row between "); sql.Append(startIndex.ToString()); sql.Append(" AND "); sql.Append(endIndex.ToString());
int pageSize;//没有个数 int pageCurrent;//当前页select top(pageSize) * from table where Id not in (select top(pageSize*(pageCurrent-1)) Id from table)通过当前页就可以实现分页效果
--借用5楼数据-- --如果lz是2005的话-- declare @tb table (id int identity (1,1),val varchar(20)) insert into @tb values('a') insert into @tb values('b') insert into @tb values('c') insert into @tb values('d') insert into @tb values('e') insert into @tb values('f') insert into @tb values('g') insert into @tb values('h') declare @pagesize int,--每页显示条数-- @pageindex int--当前页-- set @pagesize=3 set @pageindex=1 ;with cte as ( select row_number() over(order by id desc) as row_number,* from @tb ) select * from cte where row_number between (@pageindex-1)*@pagesize+1 and @pagesize*@pageindex
select top 10 * from tablename order by id desc
比如 string SQL ="select * from 表 where 条件 " 如果那里要用直接调用SQL就可以了
--int pagecount ,int pageindex
--pagecount 数量,pageindex 页码(程序里面定义,在下面拼接,也可以直接赋值)
select * from (
select *,row_number() over(order by id desc) as rows from tb) a where rows between pagecount*(pageindex-1)+1 and
pagecount*pageindex
---------------------
declare @tb table (id int identity (1,1),val varchar(20))
insert into @tb values('a')
insert into @tb values('b')
insert into @tb values('c')
insert into @tb values('d')
insert into @tb values('e')
insert into @tb values('f')
insert into @tb values('g')
insert into @tb values('h')
---------------------
select * from (
select *,row_number() over(order by id desc) as rows from tb) a where rows between 2*(2-1)+1 and
2*2
--第二页每页两行
-----------------------
id val rows
-- --- ----
4 d 3
3 c 4
FROM Tabsuite
WHERE Tabsuite_Id NOT IN
( SELECT TOP 40 Tabsuite_Id FROM Tabsuite ORDER BY Tabsuite_Id desc)
and Tabsuite_mianji>=100 and Tabsuite_mianji<=200 and Tabsuite_time <= '2011-4-25 18:00:00'
ORDER BY Tabsuite_Id desc各位帮忙给这个跳跳错吧
if (orderBy == null || orderBy == "")
{
sql.Append("默认排序字段");
}
else
{
sql.Append(orderBy);
}
sql.Append(") as row from 你的表");
if (filter != null && filter != "")
{
sql.Append(" where ");
sql.Append(filter);
}
sql.Append(") select ");
if (filds != null && filds != "")
{
sql.Append(filds);
}
else
{
sql.Append("默认取出的字段");
}
sql.Append(" from Result where row between ");
sql.Append(startIndex.ToString());
sql.Append(" AND ");
sql.Append(endIndex.ToString());
int pageCurrent;//当前页select top(pageSize) * from table where Id not in (select top(pageSize*(pageCurrent-1)) Id from table)通过当前页就可以实现分页效果
--借用5楼数据--
--如果lz是2005的话--
declare @tb table (id int identity (1,1),val varchar(20))
insert into @tb values('a')
insert into @tb values('b')
insert into @tb values('c')
insert into @tb values('d')
insert into @tb values('e')
insert into @tb values('f')
insert into @tb values('g')
insert into @tb values('h')
declare @pagesize int,--每页显示条数--
@pageindex int--当前页--
set @pagesize=3
set @pageindex=1
;with cte as
(
select row_number() over(order by id desc) as row_number,* from @tb
)
select * from cte where row_number between (@pageindex-1)*@pagesize+1 and @pagesize*@pageindex