• 009 identity 视图 分页


    /*
    时间:2020/09/13
    功能:
        一 identity
        二 视图
        三 分页
    */   

    一 identity

    -- identity
    create table student2
    (
        student_id int primary key,
        student_name nvarchar(200) not null
    )
    
    insert into student2 values(1, '张三')
    insert into student2 values(2, '李四')
    insert into student2 values(3, '王五')
    
    select * from student2
    
    insert into student2(student_name) values('赵六')    -- error
    
    create table student3
    (
        student_id int primary key identity(100, 5),
        student_name nvarchar(200) not null
    )
    
    insert into student3 values('李四')
    insert into student3 values('王五')
    
    select * from student3
    
    insert into student3 values(1, '张三') -- error
    insert into student3(student_name) values('张三')
    
    delete from student3 
        where student_name = '张三'
    insert into student3 values('赵六')

    二 视图

    -- 求平均工资最高部门编号和部门平均工资
    -- SQL Server
    select top 1 deptno, avg(sal)
        from emp
        group by deptno
        order by avg(sal) desc
    
    -- 通用SQL语句
    select *
        from (
            select deptno, avg(sal) "avg_sal"
            from emp
            group by deptno
    )"E"    -- 小表全部信息
    where "E"."avg_sal" = (
    select max("T"."avg_dept")
        from(
            select deptno, avg(sal) "avg_dept"
                from emp
                group by deptno
                ) "T"
    )    -- 部门最高平均工资
    
    
    -- 视图用法
    create view v$_emp_1
    as
        select deptno, avg(sal) "avg_sal"
            from emp
            group by deptno
    
    select * 
        from v$_emp_1
        where avg_sal = (
            select max(avg_sal)
                from v$_emp_1
    )

    三 分页

    -- 分页查询
    -- 查询工资最高的员工信息,排序前三
    select top 3 *
        from emp
        order by sal desc
    
    -- 查询工资最高的员工信息,排序前4-6
    select top 3 *
        from emp
        where empno not in
            (
                select top 3 empno
                    from emp
                    order by sal desc
            )
        order by sal desc
    
    -- 查询工资最高的员工信息,排序前7-9
    select top 3 *
        from emp
        where empno not in
            (
                select top 6 empno
                    from emp
                    order by sal desc
            )
        order by sal desc
    
    -- 查询工资最高的员工信息,排序前10-12
    select top 3 *
        from emp
        where empno not in
            (
                select top 9 empno
                    from emp
                    order by sal desc
            )
        order by sal desc
    
    
    假设每页显示n条记录, 当前要显示第m页。
    表名是A, 主键是A_id。
    m n
    1 0
    2 3
    3 6
    4 9
    
    select top n *
        from A
        where A_id not in 
        (
            select top (m-1)*n A_id
                from emp 
        )
  • 相关阅读:
    sql行列互转
    用户角色权限设计思路
    树节点类型数据的Datatable转Json
    [C#]最简单的Base64加密解密
    WEB打印控件Lodop(V6.x)使用说明及样例
    js代码 设为首页 加入收藏
    Json字符转化成对象
    C++函数返回值为const
    7.双指针(two pointer)
    线程同步与锁
  • 原文地址:https://www.cnblogs.com/huafan/p/13661234.html
Copyright © 2020-2023  润新知