这条语句什么意思,执行的先后顺序可以给我讲一下吗?(mysql)
还有就是这个最里面括号里的a.sal怎么可以和b.sal做比较的啊,当时不是还没有看到a.sal吗?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
----------------------
emp表格式如下:
CREATE TABLE EMP
(EMPNO integer NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR integer,
HIREDATE DATE,
SAL integer,
COMM integer,
DEPTNO integer)INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902,
'1980-12-17', 800, NULL, 20)
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,
'1981-2-20', 1600, 300, 30)
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698,
'1981-2-22', 1250, 500, 30)
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839,
'1981-4-2', 2975, NULL, 20)
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,
'1981-9-28', 1250, 1400, 30)
还有就是这个最里面括号里的a.sal怎么可以和b.sal做比较的啊,当时不是还没有看到a.sal吗?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
----------------------
emp表格式如下:
CREATE TABLE EMP
(EMPNO integer NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR integer,
HIREDATE DATE,
SAL integer,
COMM integer,
DEPTNO integer)INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902,
'1980-12-17', 800, NULL, 20)
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,
'1981-2-20', 1600, 300, 30)
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698,
'1981-2-22', 1250, 500, 30)
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839,
'1981-4-2', 2975, NULL, 20)
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,
'1981-9-28', 1250, 1400, 30)
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
先执行红色部分,通过这个SELECT产生一个结果集,你可以理解成一个表,然后再做查询。
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
先执行内部select 由内向外执行
要是先执行红色部分, where a.sal <= b.sal里面的 a.sal 是多少啊,怎么来的啊
create table test
select
(select count(distinct b.sal) from emp b where a.sal <= b.sal) as rnk,a.sal,a.ename from emp a第二步骤
select ename,sal
from test T
where rnk <=5
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
1.先执行
a.sal,a.ename from emp a
取得一条数据,然后传入子查询
select count(distinct b.sal) from emp b where a.sal <= b.sal
循环遍历完成后再执行where rnk <=5
SELECT (...) AS rnk, a.sal, a.ename from emp a
先把()部分当作一个常量来看。
得到
7369-7654共5行记录,由于括号中还有子查询,此时,会遍历5行记录,依次得到每个的sal值,代入括号中的查询,7369-7654这5行记录,每个都得到一个1行1列的结果集(count),这个结果就是rnk字段。
然后,再在这5行记录(附带统计出的rnk字段)的基础上,筛选所有rnk字段<=5的记录,把之前已经select出来的ename,sal显示出来