更新时间:2023-01-20 08:43:58
很简单-
SELECT empname,
empid,
(SELECT COUNT (profileid)
FROM profile
WHERE profile.empid = employee.empid)
AS number_of_profiles
FROM employee;
当你使用这样的表连接时,它会更简单:
It is even simpler when you use a table join like this:
SELECT e.empname, e.empid, COUNT (p.profileid) AS number_of_profiles
FROM employee e LEFT JOIN profile p ON e.empid = p.empid
GROUP BY e.empname, e.empid;
子查询说明:
基本上,select
中的子查询获取一个标量值并将其传递给主查询.select
中的子查询不允许传递多于一行和多于一列,这是一个限制.在这里,我们将 count
传递给主查询,正如我们所知,它始终只是一个数字——一个标量值.如果未找到值,则子查询将 null
返回到主查询.此外,子查询可以访问主查询的 from
子句中的列,如我的查询中所示,其中 employee.empid
从外部查询传递到内部查询.
Essentially, a subquery in a select
gets a scalar value and passes it to the main query. A subquery in select
is not allowed to pass more than one row and more than one column, which is a restriction. Here, we are passing a count
to the main query, which, as we know, would always be only a number- a scalar value. If a value is not found, the subquery returns null
to the main query. Moreover, a subquery can access columns from the from
clause of the main query, as shown in my query where employee.empid
is passed from the outer query to the inner query.
编辑:
当您在 select
子句中使用子查询时,Oracle 本质上将其视为左连接(您可以在 为您的查询解释计划),对于左侧的每一行,行的基数仅在右侧一个.
When you use a subquery in a select
clause, Oracle essentially treats it as a left join (you can see this in the explain plan for your query), with the cardinality of the rows being just one on the right for every row in the left.
左连接说明
左连接非常方便,尤其是当您想替换 select
子查询时,因为它的限制.这里对LEFT JOIN
关键字两边的表格行数没有限制.
A left join is very handy, especially when you want to replace the select
subquery due to its restrictions. There are no restrictions here on the number of rows of the tables in either side of the LEFT JOIN
keyword.
有关详细信息,请阅读 Oracle 文档关于子查询 和 左连接或左外连接.
For more information read Oracle Docs on subqueries and left join or left outer join.