82 lines
970 B
SQL
82 lines
970 B
SQL
/*
|
|
Author: Fromsko
|
|
Title: 超过经理收入的员工
|
|
*/
|
|
|
|
DROP TABLE EMPLOYEE;
|
|
|
|
CREATE TABLE EMPLOYEE (
|
|
ID INT,
|
|
NAME VARCHAR(255),
|
|
SALARY INT,
|
|
MANAGERID INT
|
|
);
|
|
|
|
TRUNCATE TABLE EMPLOYEE;
|
|
|
|
INSERT INTO EMPLOYEE (
|
|
ID,
|
|
NAME,
|
|
SALARY,
|
|
MANAGERID
|
|
) VALUES (
|
|
'1',
|
|
'Joe',
|
|
'70000',
|
|
'3'
|
|
);
|
|
|
|
INSERT INTO EMPLOYEE (
|
|
ID,
|
|
NAME,
|
|
SALARY,
|
|
MANAGERID
|
|
) VALUES (
|
|
'2',
|
|
'Henry',
|
|
'80000',
|
|
'4'
|
|
);
|
|
|
|
INSERT INTO EMPLOYEE (
|
|
ID,
|
|
NAME,
|
|
SALARY,
|
|
MANAGERID
|
|
) VALUES (
|
|
'3',
|
|
'Sam',
|
|
'60000',
|
|
NULL
|
|
);
|
|
|
|
INSERT INTO EMPLOYEE (
|
|
ID,
|
|
NAME,
|
|
SALARY,
|
|
MANAGERID
|
|
) VALUES (
|
|
'4',
|
|
'Max',
|
|
'90000',
|
|
NULL
|
|
);
|
|
|
|
-- 双查询(笛卡尔积 4x4=16)
|
|
SELECT
|
|
A.*
|
|
FROM
|
|
EMPLOYEE A,
|
|
EMPLOYEE B
|
|
WHERE
|
|
A.MANAGERID=B.ID
|
|
AND A.SALARY > B.SALARY;
|
|
|
|
-- 表连接
|
|
SELECT
|
|
A.*
|
|
FROM
|
|
EMPLOYEE A
|
|
JOIN EMPLOYEE B
|
|
ON A.MANAGERID=B.ID
|
|
AND A.SALARY > B.SALARY; |