• 查询分页


    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

  • 相关阅读:
    将戴尔台式机的系统刷成Linux Centos7
    如何在Maven构建的Java项目中使用log4j
    mybatis-generator逆向工程自动生成mapper
    Win10改变系统主题颜色,降低对眼睛的刺激
    09、SpringBoot 整合 jdbcTemplate、Mybatis
    08、SpringBoot配置拦截器
    navicat快捷键
    DevOps:从理念到实施
    活到老学到老啊技术人
    Linux上安装java+tomcat+mysql运行环境
  • 原文地址:https://www.cnblogs.com/enych/p/8279896.html
Copyright © 2020-2023  润新知