谁能解释这个SQL查询中到底发生了什么? [英] Can anybody explain what exactily is happening in this SQL query?

问题描述

SELECT sal 
FROM emp e 
WHERE 5 >= (SELECT COUNT(sal) 
            FROM emp a 
            WHERE a.sal <= e.sal)
ORDER BY sal;

我知道我们将获得至少五个收入者.但是查询如何执行?我听不懂请帮助我

I know that we are going to get least five earners. But how exactly does the query execute? I am unable to understand. Please help me

推荐答案

此查询具有所谓的相关子查询.您可以将查询视为运行两个嵌套循环，遍历emp中所有可能的行对.这是它的核心:

This query has what's called a correlated subquery. You can think of the query as running two nested loops, iterating over all possible pairs of rows from emp. Here is the heart of it:

WHERE a.sal <= e.sal

e指定外部 SELECT(FROM emp e)中的行，而a指定嵌套 SELECT中的行(FROM emp a).该条件使嵌套查询对所有emp行进行计数，且其薪水少于外部查询之一.外部查询包括低薪人数小于或等于5时的薪水.

e designates the row from the outer SELECT (FROM emp e), while a designates the row from the nested SELECT (FROM emp a). The condition makes the nested query count all emp rows with salaries less than the one of the outer query. The outer query includes the salary when the count of lower salaries is less than or equal to five.

请注意，薪水相同的行将被多次计数，从而有可能减少返回集中的行数.例如，如果有六个底薪相同的最低收入者，则查询将返回一组空行.您可以使用DISTINCT更改此行为.

Note that rows with identical salaries will be counted multiple times, potentially reducing the number of rows in the return set. For example, if there are six bottom earners with an identical salary, the query will return an empty set of rows. You can use DISTINCT to change this behavior you can.

这篇关于谁能解释这个SQL查询中到底发生了什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！