选读SQL经典实例笔记10_高级查询-Toy模板网

这篇具有很好参考价值的文章主要介绍了选读SQL经典实例笔记10_高级查询。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

选读SQL经典实例笔记10_高级查询文章来源地址https://www.toymoban.com/news/detail-585217.html

1. 结果集分页

1.1. 只有做过了排序，才有可能准确地从结果集中返回指定区间的记录

1.2. DB2

1.3. Oracle

1.4. SQL Server

1.5. sql

select sal
  from (
select row_number() over (order by sal) as rn,
       sal
  from emp
       ) x
 where rn between 1 and 5
SAL
----
 800
950
1100
1250
1250

1.5.2. sql

select sal
  from (
select row_number() over (order by sal) as rn,
       sal
  from emp
       ) x
 where rn between 6 and 10
SAL
-----
 1300
 1500
 1600
 2450
 2850

1.6. PostgreSQL

1.7. MySQL

1.8. sql

select sal
  from emp
 order by sal limit 5 offset 0
SAL
------
   800
   950
  1100
  1250
  1250

1.8.2. sql

 select sal
  from emp
 order by sal limit 5 offset 5
SAL
-----
 1300
 1500
 1600
 2450
 2850

2. 跳过n行记录

2.1. 获得第一个员工、第三个员工，等等

2.2. DB2

2.3. Oracle

2.4. SQL Server

2.5. 使用窗口函数ROW_NUMBER OVER为每一行分配一个序号

select ename
    from (
  select row_number() over (order by ename) rn,
         ename
    from emp
         ) x
   where mod(rn,2) = 1

2.6. PostgreSQL

2.7. MySQL

2.8. 使用标量子查询

select x.ename
    from (
  select a.ename,
         (select count(*)
            from emp b
           where b.ename ＜= a.ename) as rn
    from emp a
         )x
   where mod(x.rn,2) = 1

3. 提取最靠前的n行记录

3.1. 基于某种排序方式从结果集中提取出限定数目的记录

3.2. DB2

3.3. Oracle

3.4. SQL Server

3.5. DENSE_RANK函数

select ename,sal
   from (
 select ename, sal,
        dense_rank() over (order by sal desc) dr
   from emp
        ) x
  where dr ＜= 5

3.6. PostgreSQL

3.7. MySQL

3.8. 使用标量子查询

select ename,sal
    from (
  select (select count(distinct b.sal)
            from emp b
           where a.sal ＜= b.sal) as rnk,
          a.sal,
          a.ename
    from emp a
         )
   where rnk ＜= 5

4. 对结果排序

4.1. DB2

4.2. Oracle

4.3. SQL Server

4.4. 窗口函数DENSE_RANK OVER

select dense_rank() over(order by sal) rnk, sal
   from emp

4.5. PostgreSQL

4.6. MySQL

4.7. 标量子查询

select (select count(distinct b.sal)
            from emp b
           where b.sal ＜= a.sal) as rnk,
         a.sal
    from emp a

5. 删除重复项

5.1. DB2

5.2. Oracle

5.3. SQL Server

5.4. 窗口函数ROW_NUMBER OVER

select job
    from (
  select job,
         row_number()over(partition by job order by job) rn
    from emp
         )x
   where rn = 1

5.5. PostgreSQL

5.6. MySQL

5.7. sql

select distinct job
  from emp
select job
  from emp
 group by job

5.7.3. GROUP BY和DISTINCT是两个非常不同的子句，它们是不可互换的

6. 骑士值

6.1. 返回一个结果集，其中包括每个员工的姓名、部门、工资、入职时间以及每一个部门里最近入职的那个员工的工资

6.2. DB2

6.3. SQL Server

6.4. 窗口函数MAX OVER

select deptno,
        ename,
        sal,
        hiredate,
        max(latest_sal)over(partition by deptno) latest_sal
   from (
 select deptno,
        ename,
        sal,
        hiredate,
        case
          when hiredate = max(hiredate)over(partition by deptno)
          then sal else 0
        end latest_sal
   from emp
        ) x
  order by 1, 4 desc

6.5. Oracle

select deptno,
        ename,
        sal,
        hiredate,
        max(sal)
          keep(dense_rank last order by hiredate)
          over(partition by deptno) latest_sal
   from emp
  order by 1, 4 desc

6.6. PostgreSQL

6.7. MySQL

6.8. 两层嵌套的标量子查询

select e.deptno,
         e.ename,
         e.sal,
         e.hiredate,
         (select max(d.sal)
            from emp d
           where d.deptno  = e.deptno
             and d.hiredate =
                 (select max(f.hiredate)
                    from emp f
                   where f.deptno = e.deptno)) as latest_sal
    from emp e
   order by 1, 4 desc

到了这里，关于选读SQL经典实例笔记10_高级查询的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！