mysql 复杂查询

基础数据表

/*
 Navicat Premium Data Transfer

 Source Server         : 118.107.47.139
 Source Server Type    : MySQL
 Source Server Version : 50732
 Source Host           : icake.top:3306
 Source Schema         : emp

 Target Server Type    : MySQL
 Target Server Version : 50732
 File Encoding         : 65001

 Date: 20/09/2022 17:10:21
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) NOT NULL COMMENT '部门编号',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of dept
-- ----------------------------
BEGIN;
INSERT INTO `dept` VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES (40, 'OPERATIONS', 'BOSTON');
COMMIT;

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(4) NOT NULL COMMENT '员工编号',
  `ename` varchar(10) DEFAULT NULL,
  `job` varchar(9) DEFAULT NULL,
  `mgr` int(4) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` float(7,2) DEFAULT NULL,
  `comm` float(7,2) DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL,
  PRIMARY KEY (`empno`),
  KEY `fk_deptno` (`deptno`),
  CONSTRAINT `fk_deptno` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of emp
-- ----------------------------
BEGIN;
INSERT INTO `emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20);
INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30);
INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30);
INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20);
INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30);
INSERT INTO `emp` VALUES (7697, 'BLAKE_L', 'MANAGER', 7839, '1981-05-01', 2830.00, NULL, 30);
INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30);
INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10);
INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-07-13', 3000.00, NULL, 20);
INSERT INTO `emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-07', 5000.00, NULL, 10);
INSERT INTO `emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30);
INSERT INTO `emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-07-13', 1100.00, NULL, 20);
INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30);
INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20);
INSERT INTO `emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10);
COMMIT;

-- ----------------------------
-- Table structure for salgrade
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '等级',
  `losal` int(11) DEFAULT NULL COMMENT '最低工资',
  `hisal` int(11) DEFAULT NULL COMMENT '最高工资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='工资等级';

-- ----------------------------
-- Records of salgrade
-- ----------------------------
BEGIN;
INSERT INTO `salgrade` VALUES (1, 700, 1200);
INSERT INTO `salgrade` VALUES (2, 1201, 1400);
INSERT INTO `salgrade` VALUES (3, 1401, 2000);
INSERT INTO `salgrade` VALUES (4, 2001, 3000);
INSERT INTO `salgrade` VALUES (5, 3001, 9999);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

题目

1.  列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。
2.  列出公司各个工资等级雇员的数量、平均工资。
3.  列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
4.  列出在每个部门工作的员工数量、平均工资和平均服务期限。
5.  列出所有员工的姓名、部门名称和工资。
6.  列出所有部门的详细信息和部门人数。
7.  列出各种工作的最低工资及从事此工作的雇员姓名。
8.  列出各个部门的MANAGER(经理)的最低薪金、姓名、部门名称、部门人数。
9.  列出所有员工的年工资，所在部门名称，按年薪从低到高排序。
10. 查出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000
11. 求出部门名称中，带‘S’字符的部门员工的、工资合计、部门人数。
12. 给任职日期超过30年或者在87年雇佣的雇员加薪，加薪原则：10部门增长10%，20部门增长20%，30部门增长30%，依次类推。
13. 列出至少有一个员工的所有部门的信息：
14. 列出薪金比SMITH对的所有员工：
15. 列出所有员工的姓名以及其直接上级的姓名：
16. 列出受雇日期早于其直接上级的所有员工的编号、姓名，部门名称
17. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
18. 列出所有"CLERK(职员)"的姓名以及部门名称，部门的人数
19. 列出最低薪金大于1500的各种工作以及从事此工作的全部雇员人数
20. 列出在部门"SALES"工作的员工的姓名，假定不知道销售部的部门编号
21. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级
22. 列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。 
23. 列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。 
24. 列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。 
25. 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。 
26. 列出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。 
27. 列出所有“CLERK”（办事员）的姓名及其部门名称，部门的人数，工资等级。 
28. 列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数及所在部门名称、位置、平均工资。 
29. 列出在部门“SALES”（销售部）工作的员工的姓名、基本工资、雇佣日期、部门名称，假定不知道销售部的部门编号。 
30. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。    
31. 列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。
32. 查询dept表的结构
33. 检索emp表，用is a 这个字符串来连接员工姓名和工种两个字段
34. 检索emp表中有提成的员工姓名、月收入及提成。

答案

-- 1、 列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。
# 按照部门分组 , 求出部门名称和人数
# 多表联查
SELECT
    e.*,
    nt.dname,
    nt.ct 
FROM
    emp e,
    (
    SELECT
        e.deptno,
        dname,
        count( empno ) ct 
    FROM
        emp e,
        dept d 
    WHERE
        e.deptno = d.deptno 
    GROUP BY
        e.deptno,
        d.dname 
    ) nt 
WHERE
    e.deptno = nt.deptno 
    AND job = ( SELECT job FROM emp WHERE ename = 'SCOTT' );

-- 2、 列出公司各个工资等级雇员的数量、平均工资。
SELECT
    salgrade.grade,
    COUNT( emp.empno ),
    AVG( emp.sal ) 
FROM
    salgrade
    JOIN emp ON emp.sal BETWEEN salgrade.losal 
    AND salgrade.hisal 
GROUP BY
    salgrade.grade


-- 3、 列出薪金高于在部门30工作的所有员工的薪金, 的员工姓名和薪金、部门名称。
SELECT emp.ename, emp.sal, dept.dname
FROM emp LEFT JOIN dept ON emp.deptno = dept.deptno
WHERE emp.sal > (SELECT MAX(sal) FROM emp WHERE emp.deptno = 30)

-- 4、 列出在每个部门工作的员工数量、平均工资和平均服务期限。
SELECT deptno, COUNT(ename), AVG(sal), AVG(DATEDIFF(CURRENT_DATE, hiredate))
FROM emp
GROUP BY deptno
-- 5、 列出所有员工的姓名、部门名称和工资。
SELECT emp.ename, dept.dname, emp.sal
FROM emp LEFT JOIN dept on emp.deptno = dept.deptno
-- 6、 列出所有部门的详细信息和部门人数。
SELECT
    dept.*,
    n.ct 
FROM
    dept
    JOIN ( SELECT deptno, COUNT(*) ct FROM emp GROUP BY deptno ) n ON n.deptno = dept.deptno
-- 7、 列出各种工作的最低工资及从事此工作的雇员姓名。
SELECT MIN(sal) min_sal
FROM emp 
GROUP BY emp.job;
SELECT ename, emp.job, min_sal
FROM emp RIGHT JOIN (SELECT MIN(sal) min_sal
FROM emp 
GROUP BY emp.job) n on emp.sal = n.min_sal
-- 8、 列出各个部门的MANAGER(经理)的最低薪金、姓名、部门名称、部门人数。
SELECT
    min_sal,
    e.ename,
    dept.dname,
    d_count 
FROM
    emp e
    RIGHT JOIN ( SELECT deptno, MIN( sal ) min_sal FROM emp WHERE job = 'MANAGER' GROUP BY deptno ) m_min ON e.sal = m_min.min_sal
    LEFT JOIN ( SELECT deptno, COUNT(*) d_count FROM emp GROUP BY deptno ) d ON m_min.deptno = d.deptno
    LEFT JOIN dept ON m_min.deptno = dept.deptno 
WHERE
    e.job = 'MANAGER'
-- 9、 列出所有员工的年工资，所在部门名称，按年薪从低到高排序。
SELECT (emp.sal + IFNULL(comm, 0)) * 12 year_sal, dept.dname
FROM emp LEFT JOIN dept on emp.deptno = dept.deptno
ORDER BY year_sal
-- 10、查出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000
SELECT e.ename, m.ename, d.dname, m.sal
FROM emp e JOIN emp m on e.mgr = m.empno 
                JOIN dept d on m.deptno = d.deptno
WHERE m.sal > 3000

-- 11、求出部门名称中，带‘S’字符的部门员工的、工资合计、部门人数。
SELECT e.deptno, SUM(e.sal), COUNT(*)
FROM emp e JOIN (SELECT deptno, dname
from dept
WHERE dname like '%S%') d on e.deptno = d.deptno 
GROUP BY e.deptno

-- 12、给任职日期超过30年或者在87年雇佣的雇员加薪，加薪原则：10部门增长10%，20部门增长20%，30部门增长30%，依次类推。
SELECT e.ename, e.sal * deptno / 100
FROM emp e
WHERE YEAR(e.hiredate) = 1987 OR (YEAR(CURRENT_DATE) - YEAR(e.hiredate) > 30)
-- 13.列出至少有一个员工的所有部门的信息：
SELECT
    dept.*,
    m_size 
FROM
    dept
    RIGHT JOIN ( SELECT deptno, COUNT(*) m_size FROM emp GROUP BY deptno HAVING m_size > 1 ) ed ON dept.deptno = ed.deptno
-- 14.列出薪金比SMITH多的所有员工：
SELECT * 
FROM emp
WHERE sal > (SELECT sal from emp WHERE ename = 'SMITH')

-- 15.列出所有员工的姓名以及其直接上级的姓名：
SELECT e.ename, m.ename
FROM emp e LEFT JOIN emp m on e.mgr = m.empno

-- 16.列出受雇日期早于其直接上级的所有员工的编号、姓名，部门名称
SELECT e.empno, e.ename, d.dname
FROM emp e LEFT JOIN emp m on e.mgr = m.empno JOIN dept d on e.deptno = d.deptno
WHERE e.hiredate < m.hiredate

-- 17.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT d.*, e.*
FROM dept d LEFT JOIN emp e ON e.deptno = d.deptno

-- 18.列出所有"CLERK(职员)"的姓名以及部门名称，部门的人数

SELECT e.ename, d.dname, d_count.d_size
FROM emp e JOIN dept d on e.deptno = d.deptno JOIN (SELECT d.deptno, COUNT(e.empno) d_size
FROM dept d LEFT JOIN emp e on d.deptno = e.deptno
GROUP BY d.deptno) d_count ON d.deptno = d_count.deptno
WHERE e.job = 'CLERK'

-- 19.列出最低薪金大于1500的各种工作以及从事此工作的全部雇员人数
SELECT job, COUNT(*)
FROM emp
GROUP BY job
HAVING MIN(sal) > 1500

-- 20.列出在部门"SALES"工作的员工的姓名，假定不知道销售部的部门编号
SELECT e.ename
FROM emp e JOIN dept d on e.deptno = d.deptno
WHERE d.dname = 'SALES'
-- 21.列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级


SELECT
    e.ename,
    m.ename mgr,
    d.dname,
    sd.grade 
FROM
    emp e
    LEFT JOIN emp m ON e.mgr = m.empno
    JOIN dept d ON e.deptno = d.deptno
    JOIN salgrade sd ON e.sal BETWEEN sd.losal 
    AND sd.hisal 
WHERE
    e.sal > (
    SELECT
        AVG( sal ) 
    FROM
    emp)

-- 22. 列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。 
SELECT
    e_count.*,
    d.dname 
FROM
    dept d
    JOIN ( SELECT deptno, COUNT(*) size, AVG( sal ), MIN( sal ), MAX( sal ) FROM emp GROUP BY deptno ) e_count ON d.deptno = e_count.deptno 
WHERE
    size >= 1
-- 23. 列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。
SELECT e.empno, e.ename,  d.dname, m.ename mname, e.sal
FROM
    emp e
    LEFT JOIN emp m ON e.mgr = m.empno
    JOIN dept d ON e.deptno = d.deptno 
WHERE
    e.sal > (
        SELECT
            MIN( sal ) 
        FROM
            emp 
        WHERE
        ename IN ( "SMITH", "ALLEN" )
    )
-- 24. 列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。 
SELECT e.empno, e.ename, m.empno as mno, m.ename as mname
FROM emp e LEFT JOIN emp m ON e.mgr = m.empno
ORDER BY m.sal DESC

-- 25.列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。 
SELECT
    e.empno,
    e.ename,
    d.dname,
    d_count.size,
    e.hiredate
FROM
    emp e
    LEFT JOIN emp m ON e.mgr = m.empno
    JOIN dept d ON e.deptno = d.deptno
    JOIN ( SELECT deptno, COUNT(*) size FROM emp GROUP BY deptno ) d_count ON d_count.deptno = d.deptno 
WHERE
    e.hiredate < m.hiredate

-- 26. 列出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。 


SELECT
    d.dname,
    d_count.* 
FROM
    dept d
    LEFT JOIN ( SELECT deptno, COUNT( 1 ) size, AVG( sal ) avg_sal FROM emp GROUP BY deptno ) d_count ON d.deptno = d_count.deptno
-- 27. 列出所有“CLERK”（办事员）的姓名及其部门名称，部门的人数，工资等级。 
SELECT
    e.ename,
    d.dname,
    d_count.size,
    s.grade,
    e.job 
FROM
    emp e
    JOIN dept d ON e.deptno = d.deptno
    JOIN salgrade s ON e.sal BETWEEN s.losal 
    AND s.hisal
    JOIN ( SELECT deptno, COUNT( 1 ) size FROM emp GROUP BY deptno ) d_count ON d.deptno = d_count.deptno 
WHERE
    e.job = 'CLERK'

-- 28. 列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数及所在部门名称、位置、平均工资。 

-- 29. 列出在部门“SALES”（销售部）工作的员工的姓名、基本工资、雇佣日期、部门名称，假定不知道销售部的部门编号。 
-- 30. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。     
-- 31. 列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。
--

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 215,133评论 6赞 497
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 91,682评论 3赞 390
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 160,784评论 0赞 350
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 57,508评论 1赞 288
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 66,603评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 50,607评论 1赞 293
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,604评论 3赞 415
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,359评论 0赞 270
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,805评论 1赞 307
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,121评论 2赞 330
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,280评论 1赞 344
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,959评论 5赞 339
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,588评论 3赞 322
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,206评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,442评论 1赞 268
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,193评论 2赞 367
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,144评论 2赞 352

mysql 复杂查询

推荐阅读更多精彩内容