SQL学习 第一节

题目：获取emp表中的最高工资、最低工资

解法：

-- 传统的聚合函数
SELECT MAX(T1.salary),MIN(T1.salary) FROM emp AS T1;

SELECT salary AS max_salary
FROM emp
WHERE salary NOT IN (
    SELECT smaller.salary
    FROM emp smaller
    JOIN emp larger ON smaller.salary < larger.salary
);
-- 传统的聚合函数+子查询
SELECT salary AS min_salary
FROM emp
WHERE salary NOT IN (
    SELECT larger.salary
    FROM emp larger
    JOIN emp smaller ON larger.salary > smaller.salary
);

-- 自连接（查到所有T2比T1大的，T2没有比T1还大的就是最大值了，但是不一定只有一条，换成右连接也要懂得灵活变换）
SELECT T1.emp_id,T1.salary,T2.emp_id,T2.salary
FROM emp AS T1 
LEFT JOIN emp AS T2 ON T1.salary < T2.salary
WHERE T2.salary IS NULL;

-- 自连接（查到所有T2比T1小的，T2没有比T1还小的就是最小值了，但是不一定只有一条，换成右连接也要懂得灵活变换）
SELECT T1.emp_id,T1.salary,T2.emp_id,T2.salary
FROM emp AS T1 
LEFT JOIN emp AS T2 ON T1.salary > T2.salary
WHERE T2.salary IS NULL;

-- NOT EXISTS(T2中不存在比T1还大的，T1符合条件的就是最大值，但是不一定只有一条）
SELECT T1.emp_id,T1.salary
FROM emp AS T1
WHERE NOT EXISTS(SELECT 1 FROM emp AS T2 WHERE T1.salary < T2.salary );

-- NOT EXISTS(T2中不存在比T1还小的，T1符合条件的就是最小值，但是不一定只有一条）
SELECT T1.emp_id,T1.salary
FROM emp AS T1
WHERE NOT EXISTS(SELECT 1 FROM emp AS T2 WHERE T1.salary > T2.salary );