select top 10 * from ComprehensiveShow where id not in(select top 40 id from ComprehensiveShow order by id)
意思是查询前40条数据,在查询不在前40条数据中的前10条数据
//select top 10 * from ComprehensiveShow where id not in(select top 10(1-1) id from ComprehensiveShow order by id)
//表示第一页 10(1-1)=0
select top 10 * from Base_Dict where DictID not in (select top 20 DictID from Base_Dict) and ParentID !=0
--查询不在前20条数据 的前10条数据 并且ParentID !=0
--
--非条件查询 select top 10 * from Table_1 where id not in( select top (10*(2-1)) id from Table_1) --条件查询 分页 select top 10 * from Table_1 where id not in (1,2,3,4,5,6,7,8,9,10) and id not in( select top (10*(2-1)) id from Table_1 where id not in (1,2,3,4,5,6,7,8,9,10) ) --条件查询 select top 10 * from Table_1 where id !=1 and id not in( select top (10*(1-1)) id from Table_1 where id !=1
--分页
--查询2 select top 1 * from ( select userName, COUNT(*) as 数量 from Table_1 where 1=1 and (userName='admin1' or userName='admin2'or userName='admin3') group by userName )A where 1=1 AND userName not in( select top 2 userName from ( select userName, COUNT(*) as 数量 from Table_1 where 1=1 and (userName='admin1' or userName='admin2'or userName='admin3') group by userName )A )
--条件查询后的结果 进行分页 select * from ( select ROW_NUMBER() OVER (ORDER BY userName) line ,userName, COUNT(*) as 数量 from Table_1 where 1=1 and (userName='admin1' or userName='admin2'or userName='admin3') group by userName )A --where line between 1 and 1 ---能取出第1行 --where line between 2 and 2 --能取出 2行 --where line between 1 and 2 --每页取出2行 第1页 where line between 3 and 4 --能取出 2行 第2页
--四舍五入 select round(COUNT(*)/10.0,0) from Table_1 --10.000000 select cast(round(COUNT(*)/10.0,0) as int) from Table_1 --10
-- step 3. select top 20 userid,nick,classid,writetime from t_userinfo where userid not in ( select top 900000 userid from t_userinfo order by userid asc ) -- 耗时 8 秒 ,够长的 -- step 4. select a.userid,b.nick,b.classid,b.writetime from ( select top 20 userid from t_userinfo where userid not in ( select top 900000 userid from t_userinfo order by userid asc ) ) a left join t_userinfo b on a.userid = b.userid -- 耗时 1 秒,太快了吧,不可以思议
---sql
var page = dal_menu.Db.Queryable<Model_T_MenuInfo>().OrderBy(it => it.Id).ToPageList(pageIndex, pageSize, ref totalCount);
SELECT * FROM (
SELECT*,
ROW_NUMBER()
OVER(
ORDER BY [Id] ASC
) AS RowIndex
FROM [T_MenuInfo]
) T WHERE RowIndex BETWEEN 1 AND 2