MySQL-子查询

多行子查询

# 多行子查询

SELECT deptno
	FROM emp
	WHERE ename = 'SMITH'

-- 显示和smith同一个部门的人	
SELECT * FROM emp
	WHERE deptno = ( -- 只返回一条用‘=’	
		SELECT deptno
			FROM emp
			WHERE ename = 'SMITH'
	)

SELECT ename,job,sal,deptno 
	FROM emp
	WHERE job IN ( -- 关键词IN，因为返回的不止一条
		SELECT DISTINCT job -- distinct去重
			FROM emp
			WHERE deptno = 10
			)AND deptno != 10 -- <>和!=都是不等
			

-- 子查询当临时表使用
SELECT cat_id , MAX(shop_price) -- 当作临时表
	FROM ecs_goods
	GROUP BY cat_id

SELECT goods_id,cat_id,goods_name,shop_price
	FROM ecs_goods;
	
SELECT goods_id,ecs_goods.cat_id,goods_name,shop_price
	FROM(
	SELECT cat_id , MAX(shop_price) AS max_price -- 当作临时表
	FROM ecs_goods
	GROUP BY cat_id
	)temp,ecs_goods
	WHERE temp.cat_id = ecs_goods.cat_id 
	AND temp.max_price = ecs_goods.shop_price
	

-- all操作符
-- 比30号所有人工资高
SELECT ename,sal,deptno 
	FROM emp
	WHERE sal > ALL(
			SELECT sal FROM emp
			WHERE deptno = 30)

-- any操作符
-- 比30号其中一个人高
SELECT ename,sal,deptno 
	FROM emp
	WHERE sal > ANY(
			SELECT sal FROM emp
			WHERE deptno = 30)

多列子查询

# 多列子查询

SELECT deptno,job 
	FROM emp
	WHERE ename = 'ALLEN'

SELECT * 
	FROM emp
	WHERE (deptno,job) = ( -- 要求有两列数据相等
		SELECT deptno,job 
		FROM emp
		WHERE ename = 'ALLEN'
		)AND ename <> 'ALLEN' -- 排除本人 

练习

SELECT dname,dept.deptno,loc,temp.per_num AS '人数'
	FROM dept,(
	SELECT COUNT(*) AS per_num,deptno
	FROM emp
	GROUP BY deptno
	)temp
	WHERE temp.deptno = dept.deptno
	
-- 表.*
SELECT temp.*,dname,loc
	FROM dept,(
	SELECT COUNT(*) AS per_num,deptno
	FROM emp
	GROUP BY deptno
	)temp
	WHERE temp.deptno = dept.deptno