mssql中使用类似mysql中的limit
今天在ms sql里想弄个分页,可是发现mssql里是没mysql里的limit函数的,于是在网上找了一些解决的方法,发现这样可以做到。。。
mySql输入:
select*from table_news order by news_id desc limit5,4
ms Sql输出:
select*from(select top4*from(select top9*from table_news order by news_id desc)as totleResult order by news_id)as allResult order by news_id desc
其实可以这样认为vPagesize=10,vPageNo=2,语句就可以写成:
"select*from(select top"+vPagesize+"*from(select top"+int.Parse(vPageNo*vPagesize)+"*from chenhua_news order by news_id desc)as totleResult order by news_id/*要注意这里是没有desc的*/)as allResult order by news_id desc
***例如我今天写的:这里的10就是指一页显示10条记录,40是指这里读取第4页的数据,而这里还有一个条件就是以c开头的记录like'c%'
select vendno,company,(select count(*)from cust0001where ltrim(rtrim(status))='U')as countRecords from
(select top10vendno,company from(select top40
vendno,company from cust0001where ltrim(rtrim(status))='U' and company like'c%' order by company)
as totleResult order by company desc)as allResult
order by company