查询分页

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