数据库子查询

风和雪的爱情

已于 2023-11-15 09:52:36 修改

阅读量38

点赞数

文章标签：数据库

于 2023-11-15 09:52:00 首次发布

原文链接：https://blog.csdn.net/qq_41896822/article/details/127929554

版权

postgres LATERAL

在PostgreSQL中，LATERAL关键字用于在FROM子句中引用前面的FROM项提供的列。通过使用LATERAL，您可以在子查询或函数中引用前面FROM子句中的列。

在没有LATERAL的情况下，每个子查询或函数都是独立求值的，因此不能交叉引用其他FROM项。但是，使用LATERAL关键字后，您可以在子查询或函数中引用位于其前面的FROM项中的列，从而进行交叉引用。

LATERAL可以出现在FROM列表的顶层，也可以出现在JOIN树中。在JOIN树中，它还可以引用位于JOIN右侧的左侧的任何项。

计算过程如下：对于提供交叉引用列的FROM项的每一行，或者多个FROM项提供引用列的行的集合，LATERAL项会使用行或行集的列值进行计算。计算出来的结果集将像往常一样被加入到联合查询中。这一过程会在列的来源表的每一行或行的集合上重复进行。

请注意，使用LATERAL可能会增加查询的计算复杂性，因为它涉及对多个FROM项进行交叉引用。因此，在使用LATERAL时要谨慎，并确保查询的逻辑和性能符合您的需求。

基础数据

当然，以下是一个使用PostgreSQL中的LATERAL关键字的例子：

假设我们有两个表：customers（客户表）和orders（订单表），并且每个客户可以有多个订单。我们想要检索每个客户的订单数以及订单的总金额。

首先，我们会创建这两个表的简单版本：

--create table
CREATE TABLE customers (  
  customer_id INT PRIMARY KEY,  
  customer_name VARCHAR(50)  
);  
  
CREATE TABLE orders (  
  order_id INT PRIMARY KEY,  
  customer_id INT REFERENCES customers(customer_id),  
  order_amount DECIMAL(10, 2)  
);
--insert
INSERT INTO customers (customer_id, customer_name)  
VALUES  
  (1, 'John Doe'),  
  (2, 'Jane Smith'),  
  (3, 'Michael Johnson');

INSERT INTO orders (order_id, customer_id, order_amount)  
VALUES  
  (1, 1, 100.00),  
  (2, 1, 200.00),  
  (3, 2, 150.00),  
  (4, 3, 50.00),  
  (5, 3, 250.00);

查询示例

SELECT c.customer_id, c.customer_name, o.total_orders, o.total_amount  
FROM customers c,  
LATERAL (  
  SELECT COUNT(*) AS total_orders, SUM(order_amount) AS total_amount  
  FROM orders  
  WHERE customer_id = c.customer_id  
) o;
--查询结果
SELECT c.customer_id, c.customer_name, o.total_orders, o.total_amount  
FROM customers c,  
LATERAL (  
  SELECT COUNT(*) AS total_orders, SUM(order_amount) AS total_amount  
  FROM orders  
  WHERE customer_id = c.customer_id  
  7  ) o;

CUSTOMER_ID CUSTOMER_NAME                                                                                             TOTAL_ORDERS TOTAL_AMOUNT
          1 John Doe                                                                                                        2          300
          2 Jane Smith                                                                                                      1          150
          3 Michael Johnson                                                                                                 2          300

C##SCOTT@LHRCDB>

oracle 子查询

select t. from (select * from emp order by sal desc) t where rownum<4;*

由此可见使用子查询需要注意：

子查询要在括号内，括号内子查询只返回一行
子查询放在比较条件右侧增强可读性
单行操作符对应单行子查询，多行操作符对应多行子查询
————————————————
版权声明：本文为CSDN博主「十三妹_」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_41896822/article/details/127929554

一、单行子查询

只返回一行
比较操作符：= > >= < <= <>(<>不等于，等价于!=)

使用单行比较操作符

--##例，查询工作与7369相同且工资大于7876的员工信息
select empno,ename,sal from emp where job=(select job from emp where empno=7369) and sal >(select sal from emp where empno=7876);
 
     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7788 SCOTT                      3000
      7934 MILLER                     1300

C##SCOTT@LHRCDB>

使用分组函数

--#例 ，查询工资最低的员工
select empno,ename,sal from emp where sal=(select min(sal) from emp);
 
     EMPNO ENAME	     SAL
---------- ---------- ----------
      7369 SMITH	     800

--##上述子查询等价于：

select empno,ename,sal from emp order by sal asc fetch first 1 rows only;

子查询使用HAVING

使用HAVING

首先执行子查询
想主查询的having语句返回结果

--#例 查询哪些部门最低工资比20号部门最低工资高
select deptno,min(sal) from emp group by deptno having min(sal) >(select min(sal) from emp where deptno=20);
 
    DEPTNO   MIN(SAL)
---------- ----------
	30	  950
	10	 1300
--# 查询平均工资最高的职位
select job,avg(sal) from emp group by job having avg(sal)=(select max(avg(sal)) from emp group by job);
JOB	    AVG(SAL)
--------- ----------
PRESIDENT	5000
--#为了测试子查询，但从结果来说有多种查询方法

注：单行子查询返回有多行结果时，会出错

ORA-01427*：*single-row subquery returns more than one row

二、多行子查询

1、操作符

IN 等于列表中的某一个值
ANY 与列表的任意值比较
ALL 与列表所有值比较

2、返回值多于一行

--#查询是领导的员工，mgr存的是员工的领导工号
select empno,ename from emp where empno in (select mgr from emp);
 
     EMPNO ENAME
---------- ----------
      7566 JONES
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7902 FORD
 
6 rows selected.
 
select empno,ename from emp where empno not in (select mgr from emp);
 
no rows selected
 
select empno,ename from emp where empno not in (select mgr from emp where mgr is not null);
 
     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7654 MARTIN
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7934 MILLER
 
8 rows selected.

从上述可知，使用NOT IN时不能对null操作

使用ANY

--#查询工资比职位是 clerk 的员工中任意(某)一个低的员工信息
select empno,ename,sal from emp where sal < any (select sal from emp where job='CLERK') and job !='CLERK';
 
     EMPNO ENAME	     SAL
---------- ---------- ----------
      7521 WARD 	    1250
      7654 MARTIN	    1250

附：

< any :小于最大值
> any :大于最小值
= any :等价于 in

使用ALL

--# 例，查询工资比各部门平均工资都高的员工
select empno,ename,sal from emp where sal > all (select avg(sal) from emp group by deptno);
 
     EMPNO ENAME	     SAL
---------- ---------- ----------
      7566 JONES	    2975
      7788 SCOTT	    3000
      7902 FORD 	    3000
      7839 KING 	    5000

多行多列子查询要使用多行操作符，如：

select empno,ename,sal,job from emp
where (job,sal) in (select job,sal from emp where deptno=10)   and empno<>7788;

在FROM中使用子查询

--#例：查询比本部门平均工资高的员工
select a.empno,a.ename,a.sal,a.job,a.deptno,b.avgsal from emp a, (select deptno,avg(sal) avgsal from emp group by deptno) b where a.deptno=b.deptno and a.sal > b.avgsal;
 
     EMPNO ENAME	     SAL JOB	       DEPTNO	  AVGSAL
---------- ---------- ---------- --------- ---------- ----------
      7499 ALLEN	    1600 SALESMAN	   30 1566.66667
      7566 JONES	    2975 MANAGER	   20	    2175
      7698 BLAKE	    2850 MANAGER	   30 1566.66667
      7788 SCOTT	    3000 ANALYST	   20	    2175
      7839 KING 	    5000 PRESIDENT	   10 2916.66667
      7902 FORD 	    3000 ANALYST	   20	    2175
 
6 rows selected.

关联子查询

普通子查询：在主查询执行之前，子查询首先执行一次。子查询的结果要在主查询中使用。

关联子查询：需要重复执行子查询。（where写在子查询中）

--#例：查询比本部门平均工资高的员工
select a.empno,a.ename,a.sal,a.deptno from emp a where a.sal > (select avg(sal) from emp b  where a.deptno=b.deptno);
 
     EMPNO ENAME	     SAL     DEPTNO
---------- ---------- ---------- ----------
      7499 ALLEN	    1600	 30
      7566 JONES	    2975	 20
      7698 BLAKE	    2850	 30
      7788 SCOTT	    3000	 20
      7839 KING 	    5000	 10
      7902 FORD 	    3000	 20
 
6 rows selected.

在SELECT中使用子查询

--#例：查询员工所属的部门名
select empno,ename,deptno, (select dname from dept d where a.deptno=d.deptno) from emp a;
 
     EMPNO ENAME	  DEPTNO (SELECTDNAMEFR
---------- ---------- ---------- --------------
      7369 SMITH	      20 RESEARCH
      7499 ALLEN	      30 SALES
      7521 WARD 	      30 SALES
      7566 JONES	      20 RESEARCH
      7654 MARTIN	      30 SALES
      7698 BLAKE	      30 SALES
      7782 CLARK	      10 ACCOUNTING
      7788 SCOTT	      20 RESEARCH
      7839 KING 	      10 ACCOUNTING
      7844 TURNER	      30 SALES
      7876 ADAMS	      20 RESEARCH
      7900 JAMES	      30 SALES
      7902 FORD 	      20 RESEARCH
      7934 MILLER	      10 ACCOUNTING
 
14 rows selected.

使用exists 和 not exists

--#，查询是经理的员工
select a.empno,a.ename from emp a where exists (select 1 from emp b where b.mgr=a.empno);
 
     EMPNO ENAME
---------- ----------
      7566 JONES
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7902 FORD
 
6 rows selected.
--#，查询不是经理的员工
select a.empno,a.ename from emp a where not exists (select 1 from emp b where b.mgr=a.empno);
 
     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7654 MARTIN
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7934 MILLER
 
8 rows selected.

风和雪的爱情

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库子查询

在PostgreSQL中，LATERAL关键字用于在FROM子句中引用前面的FROM项提供的列。通过使用LATERAL，您可以在子查询或函数中引用前面FROM子句中的列。在没有LATERAL的情况下，每个子查询或函数都是独立求值的，因此不能交叉引用其他FROM项。但是，使用LATERAL关键字后，您可以在子查询或函数中引用位于其前面的FROM项中的列，从而进行交叉引用。LATERAL可以出现在FROM列表的顶层，也可以出现在JOIN树中。
复制链接

扫一扫