【九】【SQL】子查询和from

最新推荐文章于 2024-06-12 10:49:09 发布
最新推荐文章于 2024-06-12 10:49:09 发布
阅读量1.2k 收藏 19
点赞数 26
分类专栏： 数据库SQL 文章标签： 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_74163972/article/details/136460119
版权

显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

 

mysql> select avg(sal) from emp;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)

mysql> select avg(sal) from emp group by deptno;
+-------------+
| avg(sal)    |
+-------------+
| 2916.666667 |
| 2175.000000 |
| 1566.666667 |
+-------------+
3 rows in set (0.00 sec)

mysql> select  deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

mysql> select * from emp,(select  deptno,avg(sal) from emp group by deptno);
ERROR 1248 (42000): Every derived table must have its own alias
mysql> select * from emp,(select  deptno,avg(sal) from emp group by deptno) tmp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+-------------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | avg(sal)    |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+-------------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     10 | 2916.666667 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     10 | 2916.666667 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     10 | 2916.666667 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     10 | 2916.666667 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     10 | 2916.666667 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     10 | 2916.666667 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 | 2916.666667 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     10 | 2916.666667 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 | 2916.666667 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     10 | 2916.666667 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     10 | 2916.666667 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     10 | 2916.666667 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     10 | 2916.666667 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 | 2916.666667 |
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 | 2175.000000 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     20 | 2175.000000 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     20 | 2175.000000 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 | 2175.000000 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     20 | 2175.000000 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     20 | 2175.000000 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     20 | 2175.000000 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     20 | 2175.000000 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     20 | 2175.000000 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     20 | 2175.000000 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     20 | 2175.000000 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     20 | 2175.000000 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 | 2175.000000 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     20 | 2175.000000 |
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     30 | 1566.666667 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 | 1566.666667 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 | 1566.666667 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     30 | 1566.666667 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | 1566.666667 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 | 1566.666667 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     30 | 1566.666667 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     30 | 1566.666667 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     30 | 1566.666667 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 | 1566.666667 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     30 | 1566.666667 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 | 1566.666667 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     30 | 1566.666667 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     30 | 1566.666667 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+-------------+
42 rows in set (0.00 sec)

mysql> select * from emp,(select  deptno,avg(sal) from emp group by deptno) tmp where emp.deptno=tmp.deptno;
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+-------------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | avg(sal)    |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+-------------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 | 2175.000000 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 | 1566.666667 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 | 1566.666667 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 | 2175.000000 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | 1566.666667 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 | 1566.666667 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 | 2916.666667 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     20 | 2175.000000 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 | 2916.666667 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 | 1566.666667 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     20 | 2175.000000 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 | 1566.666667 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 | 2175.000000 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 | 2916.666667 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+-------------+
14 rows in set (0.00 sec)

mysql> select * from emp,(select  deptno,avg(sal) as myavg from emp group by deptno) tmp where emp.deptno=tmp.deptno;
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+-------------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | myavg       |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+-------------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 | 2175.000000 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 | 1566.666667 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 | 1566.666667 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 | 2175.000000 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | 1566.666667 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 | 1566.666667 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 | 2916.666667 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     20 | 2175.000000 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 | 2916.666667 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 | 1566.666667 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     20 | 2175.000000 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 | 1566.666667 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 | 2175.000000 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 | 2916.666667 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+-------------+
14 rows in set (0.00 sec)

mysql> select * from emp,(select  deptno,avg(sal) as myavg from emp group by deptno) tmp where emp.deptno=tmp.deptno and emp.sal>tmp.myavg;
+--------+-------+-----------+------+---------------------+---------+--------+--------+--------+-------------+
| empno  | ename | job       | mgr  | hiredate            | sal     | comm   | deptno | deptno | myavg       |
+--------+-------+-----------+------+---------------------+---------+--------+--------+--------+-------------+
| 007499 | ALLEN | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 |     30 |     30 | 1566.666667 |
| 007566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |   NULL |     20 |     20 | 2175.000000 |
| 007698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |   NULL |     30 |     30 | 1566.666667 |
| 007788 | SCOTT | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |   NULL |     20 |     20 | 2175.000000 |
| 007839 | KING  | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |   NULL |     10 |     10 | 2916.666667 |
| 007902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |   NULL |     20 |     20 | 2175.000000 |
+--------+-------+-----------+------+---------------------+---------+--------+--------+--------+-------------+
6 rows in set (0.00 sec)

mysql> select ename,emp.deptno from emp,(select  deptno,avg(sal) as myavg from emp group by deptno) tmp where emp.deptno=tmp.deptno and emp.sal>tmp.myavg;
+-------+--------+
| ename | deptno |
+-------+--------+
| ALLEN |     30 |
| JONES |     20 |
| BLAKE |     30 |
| SCOTT |     20 |
| KING  |     10 |
| FORD  |     20 |
+-------+--------+
6 rows in set (0.00 sec)

mysql> select *from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql>  select *from dept ,(select ename,emp.deptno from emp,(select  deptno,avg(sal) as myavg from emp group by deptno) tmp where emp.ddeptno=tmp.deptno and emp.sal>tmp.myavg) t1;
+--------+------------+----------+-------+--------+
| deptno | dname      | loc      | ename | deptno |
+--------+------------+----------+-------+--------+
|     10 | ACCOUNTING | NEW YORK | ALLEN |     30 |
|     20 | RESEARCH   | DALLAS   | ALLEN |     30 |
|     30 | SALES      | CHICAGO  | ALLEN |     30 |
|     40 | OPERATIONS | BOSTON   | ALLEN |     30 |
|     10 | ACCOUNTING | NEW YORK | JONES |     20 |
|     20 | RESEARCH   | DALLAS   | JONES |     20 |
|     30 | SALES      | CHICAGO  | JONES |     20 |
|     40 | OPERATIONS | BOSTON   | JONES |     20 |
|     10 | ACCOUNTING | NEW YORK | BLAKE |     30 |
|     20 | RESEARCH   | DALLAS   | BLAKE |     30 |
|     30 | SALES      | CHICAGO  | BLAKE |     30 |
|     40 | OPERATIONS | BOSTON   | BLAKE |     30 |
|     10 | ACCOUNTING | NEW YORK | SCOTT |     20 |
|     20 | RESEARCH   | DALLAS   | SCOTT |     20 |
|     30 | SALES      | CHICAGO  | SCOTT |     20 |
|     40 | OPERATIONS | BOSTON   | SCOTT |     20 |
|     10 | ACCOUNTING | NEW YORK | KING  |     10 |
|     20 | RESEARCH   | DALLAS   | KING  |     10 |
|     30 | SALES      | CHICAGO  | KING  |     10 |
|     40 | OPERATIONS | BOSTON   | KING  |     10 |
|     10 | ACCOUNTING | NEW YORK | FORD  |     20 |
|     20 | RESEARCH   | DALLAS   | FORD  |     20 |
|     30 | SALES      | CHICAGO  | FORD  |     20 |
|     40 | OPERATIONS | BOSTON   | FORD  |     20 |
+--------+------------+----------+-------+--------+
24 rows in set (0.00 sec)

mysql>  select *from dept ,(select ename,emp.deptno from emp,(select  deptno,avg(sal) as myavg from emp group by deptno) tmp where emp.deptno=tmp.deptno and emp.sal>tmp.myavg) t1 where t1.deptno=dept.deptno;
+--------+------------+----------+-------+--------+
| deptno | dname      | loc      | ename | deptno |
+--------+------------+----------+-------+--------+
|     30 | SALES      | CHICAGO  | ALLEN |     30 |
|     20 | RESEARCH   | DALLAS   | JONES |     20 |
|     30 | SALES      | CHICAGO  | BLAKE |     30 |
|     20 | RESEARCH   | DALLAS   | SCOTT |     20 |
|     10 | ACCOUNTING | NEW YORK | KING  |     10 |
|     20 | RESEARCH   | DALLAS   | FORD  |     20 |
+--------+------------+----------+-------+--------+
6 rows in set (0.00 sec)

mysql>  select t1.ename ,dept.loc,t1.deptno  from dept ,(select ename,emp.deptno from emp,(select  deptno,avg(sal) as myavg from emp grooup by deptno) tmp where emp.deptno=tmp.deptno and emp.sal>tmp.myavg) t1 where t1.deptno=dept.deptno;
+-------+----------+--------+
| ename | loc      | deptno |
+-------+----------+--------+
| ALLEN | CHICAGO  |     30 |
| JONES | DALLAS   |     20 |
| BLAKE | CHICAGO  |     30 |
| SCOTT | DALLAS   |     20 |
| KING  | NEW YORK |     10 |
| FORD  | DALLAS   |     20 |
+-------+----------+--------+
6 rows in set (0.00 sec)

mysql> 

 

这一系列查询展示了如何使用子查询和分组聚合函数来筛选数据，并将其与其他表进行联结查询。
 

计算所有员工的平均薪水:
 

SELECT AVG(sal) FROM emp;
 

这条查询返回了所有员工的平均薪水。
 

按部门分组计算平均薪水:
 

SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;
 

这条查询按部门分组并计算每个部门的平均薪水。
 

尝试直接联结员工表和分组查询结果但忘记为分组查询结果指定别名，导致错误:
 

SELECT * FROM emp, (SELECT deptno, AVG(sal) FROM emp GROUP BY deptno);
 

MySQL 报错，提示每个派生表必须有自己的别名。
 

为分组查询结果指定别名后进行联结查询:
 

SELECT * FROM emp, (SELECT deptno, AVG(sal) FROM emp GROUP BY deptno) AS tmp;
 

这条查询返回了员工表的所有记录与每个部门平均薪水的笛卡尔积。
 

基于部门号进行连接，筛选出员工的薪水高于其所在部门平均薪水的记录:
 

SELECT * FROM emp, (SELECT deptno, AVG(sal) AS myavg FROM emp GROUP BY deptno) AS tmp WHERE emp.deptno = tmp.deptno AND emp.sal > tmp.myavg;
 

这条查询找出了薪水高于其所在部门平均薪水的员工记录。
 

列出上述查询结果的员工名称、部门位置和部门编号:
 

SELECT t1.ename, dept.loc, t1.deptno FROM dept, (SELECT ename, emp.deptno FROM emp, (SELECT deptno, AVG(sal) AS myavg FROM emp GROUP BY deptno) AS tmp WHERE emp.deptno = tmp.deptno AND emp.sal > tmp.myavg) AS t1 WHERE t1.deptno = dept.deptno;
 

这条查询进一步联结了dept表，用以显示符合条件的员工名称、他们所在部门的位置和部门编号。
 

查询每个部门工资最高的人的姓名、工资、部门、最高工资
 

 

mysql> select deptno ,max(sal) from emp;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'scott.emp.deptno'; this is incompatible with sql_mode=only_full_group_by
mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+
1 row in set (0.00 sec)

mysql> select max(sal) from emp group by deptno;
+----------+
| max(sal) |
+----------+
|  5000.00 |
|  3000.00 |
|  2850.00 |
+----------+
3 rows in set (0.00 sec)

mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
|     20 |  3000.00 |
|     30 |  2850.00 |
+--------+----------+
3 rows in set (0.00 sec)

mysql> select* from emp t1 ,(select deptno ,max(sal) from emp group by deptno ) t2;
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+----------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | max(sal) |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+----------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     10 |  5000.00 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     10 |  5000.00 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     10 |  5000.00 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     10 |  5000.00 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     10 |  5000.00 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     10 |  5000.00 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 |  5000.00 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     10 |  5000.00 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 |  5000.00 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     10 |  5000.00 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     10 |  5000.00 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     10 |  5000.00 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     10 |  5000.00 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 |  5000.00 |
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 |  3000.00 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     20 |  3000.00 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     20 |  3000.00 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 |  3000.00 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     20 |  3000.00 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     20 |  3000.00 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     20 |  3000.00 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     20 |  3000.00 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     20 |  3000.00 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     20 |  3000.00 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     20 |  3000.00 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     20 |  3000.00 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 |  3000.00 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     20 |  3000.00 |
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     30 |  2850.00 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 |  2850.00 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 |  2850.00 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     30 |  2850.00 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 |  2850.00 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 |  2850.00 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     30 |  2850.00 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     30 |  2850.00 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     30 |  2850.00 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 |  2850.00 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     30 |  2850.00 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 |  2850.00 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     30 |  2850.00 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     30 |  2850.00 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+----------+
42 rows in set (0.00 sec)

mysql> select* from emp t1 ,(select deptno ,max(sal) from emp group by deptno ) t2 where t1.deptno =t2.deptno;
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+----------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | max(sal) |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+----------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 |  3000.00 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 |  2850.00 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 |  2850.00 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 |  3000.00 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 |  2850.00 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 |  2850.00 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 |  5000.00 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     20 |  3000.00 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 |  5000.00 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 |  2850.00 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     20 |  3000.00 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 |  2850.00 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 |  3000.00 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 |  5000.00 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+----------+
14 rows in set (0.00 sec)

mysql> select* from emp t1 ,(select deptno ,max(sal)  mymax from emp group by deptno ) t2 where t1.deptno =t2.deptno and t1.sal=t2.mymax;
+--------+-------+-----------+------+---------------------+---------+------+--------+--------+---------+
| empno  | ename | job       | mgr  | hiredate            | sal     | comm | deptno | deptno | mymax   |
+--------+-------+-----------+------+---------------------+---------+------+--------+--------+---------+
| 007698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |     30 | 2850.00 |
| 007788 | SCOTT | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |     20 | 3000.00 |
| 007839 | KING  | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |     10 | 5000.00 |
| 007902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |     20 | 3000.00 |
+--------+-------+-----------+------+---------------------+---------+------+--------+--------+---------+
4 rows in set (0.00 sec)

mysql> select ename,sal,t1.deptno,mymax from emp t1 ,(select deptno ,max(sal)  mymax from emp group by deptno ) t2 where t1.deptno =t2.ddeptno and t1.sal=t2.mymax;
+-------+---------+--------+---------+
| ename | sal     | deptno | mymax   |
+-------+---------+--------+---------+
| BLAKE | 2850.00 |     30 | 2850.00 |
| SCOTT | 3000.00 |     20 | 3000.00 |
| KING  | 5000.00 |     10 | 5000.00 |
| FORD  | 3000.00 |     20 | 3000.00 |
+-------+---------+--------+---------+
4 rows in set (0.00 sec)

mysql> 

 

这一系列的 MySQL 查询演示了如何使用聚合函数（如 MAX()）和分组（GROUP BY）来查询数据，并结合子查询和连接操作来进一步分析和筛选数据。
 

首先尝试查询每个部门的最高薪水，但没有使用 GROUP BY 语句，导致 MySQL 报错。这是因为在启用了 only_full_group_by SQL 模式的情况下，如果查询包含了聚合函数，那么所有非聚合列都必须包含在 GROUP BY 子句中。
 

查询了所有员工中的最高薪水，这是一个简单的聚合查询，返回了单一的最大值。
 

通过对每个部门进行分组，查询了每个部门的最高薪水。这展示了如何使用 GROUP BY 语句来分析每个分组（在这里是每个部门）的数据。
 

尝试联结员工表 (emp) 和一个子查询结果（每个部门的最高薪水），但由于没有指定连接条件，结果是一个笛卡尔积，这通常不是预期的结果。
 

修正了上述查询，通过添加 WHERE 子句来指定连接条件（t1.deptno = t2.deptno），确保只有当员工所在部门与子查询中的部门号相匹配时才返回记录。这个查询显示了每个部门及其最高薪水的员工信息。
 

最后，通过将薪水与每个部门的最高薪水相匹配（t1.sal = t2.mymax），筛选出了每个部门薪水最高的员工。这种类型的查询对于找出每个分组内特定条件下的记录非常有用。
 

通过这些查询，我们可以看到如何结合使用 MySQL 的各种功能来进行复杂的数据分析。聚合函数和 GROUP BY 子句能够帮助我们汇总数据，而子查询和连接操作则允许我们将这些汇总结果与其他表或查询结果结合起来，进行进一步的分析和筛选。
 

显示每个部门的信息（部门号、编号、地址）和人员数量
 

 

mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from emp group by deptno;
+----------+
| count(*) |
+----------+
|        3 |
|        5 |
|        6 |
+----------+
3 rows in set (0.00 sec)

mysql> select deptno,count(*) from emp group by deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|     10 |        3 |
|     20 |        5 |
|     30 |        6 |
+--------+----------+
3 rows in set (0.00 sec)


mysql> select *from dept t1,(select deptno,count(*) from emp group by deptno) t2;
+--------+------------+----------+--------+----------+
| deptno | dname      | loc      | deptno | count(*) |
+--------+------------+----------+--------+----------+
|     10 | ACCOUNTING | NEW YORK |     10 |        3 |
|     20 | RESEARCH   | DALLAS   |     10 |        3 |
|     30 | SALES      | CHICAGO  |     10 |        3 |
|     40 | OPERATIONS | BOSTON   |     10 |        3 |
|     10 | ACCOUNTING | NEW YORK |     20 |        5 |
|     20 | RESEARCH   | DALLAS   |     20 |        5 |
|     30 | SALES      | CHICAGO  |     20 |        5 |
|     40 | OPERATIONS | BOSTON   |     20 |        5 |
|     10 | ACCOUNTING | NEW YORK |     30 |        6 |
|     20 | RESEARCH   | DALLAS   |     30 |        6 |
|     30 | SALES      | CHICAGO  |     30 |        6 |
|     40 | OPERATIONS | BOSTON   |     30 |        6 |
+--------+------------+----------+--------+----------+
12 rows in set (0.00 sec)

mysql> select *from dept t1,(select deptno,count(*) from emp group by deptno) t2 where t1.deptno=t2.deptno;
+--------+------------+----------+--------+----------+
| deptno | dname      | loc      | deptno | count(*) |
+--------+------------+----------+--------+----------+
|     10 | ACCOUNTING | NEW YORK |     10 |        3 |
|     20 | RESEARCH   | DALLAS   |     20 |        5 |
|     30 | SALES      | CHICAGO  |     30 |        6 |
+--------+------------+----------+--------+----------+
3 rows in set (0.00 sec)

mysql> select t1.dname,t1.loc,t2.dept_num ,t1.deptno from dept t1,(select deptno,count(*) dept_num from emp group by deptno) t2 where t1
1.deptno=t2.deptno;
+------------+----------+----------+--------+
| dname      | loc      | dept_num | deptno |
+------------+----------+----------+--------+
| ACCOUNTING | NEW YORK |        3 |     10 |
| RESEARCH   | DALLAS   |        5 |     20 |
| SALES      | CHICAGO  |        6 |     30 |
+------------+----------+----------+--------+
3 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> 
mysql> select *from emp,dept;
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | dname      | loc      |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     10 | ACCOUNTING | NEW YORK |
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     10 | ACCOUNTING | NEW YORK |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     20 | RESEARCH   | DALLAS   |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     40 | OPERATIONS | BOSTON   |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     10 | ACCOUNTING | NEW YORK |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     20 | RESEARCH   | DALLAS   |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     40 | OPERATIONS | BOSTON   |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     10 | ACCOUNTING | NEW YORK |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     10 | ACCOUNTING | NEW YORK |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     20 | RESEARCH   | DALLAS   |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     40 | OPERATIONS | BOSTON   |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     10 | ACCOUNTING | NEW YORK |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     20 | RESEARCH   | DALLAS   |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     40 | OPERATIONS | BOSTON   |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     20 | RESEARCH   | DALLAS   |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     30 | SALES      | CHICAGO  |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     40 | OPERATIONS | BOSTON   |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     10 | ACCOUNTING | NEW YORK |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     20 | RESEARCH   | DALLAS   |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     30 | SALES      | CHICAGO  |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     40 | OPERATIONS | BOSTON   |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     10 | ACCOUNTING | NEW YORK |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     20 | RESEARCH   | DALLAS   |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     40 | OPERATIONS | BOSTON   |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     10 | ACCOUNTING | NEW YORK |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     10 | ACCOUNTING | NEW YORK |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     20 | RESEARCH   | DALLAS   |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     40 | OPERATIONS | BOSTON   |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     10 | ACCOUNTING | NEW YORK |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     20 | RESEARCH   | DALLAS   |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     30 | SALES      | CHICAGO  |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     40 | OPERATIONS | BOSTON   |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
56 rows in set (0.00 sec)

mysql> select *from emp,dept where emp.deptno =dept.deptno;
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | dname      | loc      |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
14 rows in set (0.00 sec)

mysql> select *from emp,dept where emp.deptno =dept.deptno group by emp.deptno;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'scott.emp.empno' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select count(*) from emp,dept where emp.deptno =dept.deptno group by emp.deptno;
+----------+
| count(*) |
+----------+
|        3 |
|        5 |
|        6 |
+----------+
3 rows in set (0.00 sec)

mysql> select deptno, count(*) from emp,dept where emp.deptno =dept.deptno group by emp.deptno;
ERROR 1052 (23000): Column 'deptno' in field list is ambiguous
mysql> select emp.deptno, count(*) from emp,dept where emp.deptno =dept.deptno group by emp.deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|     10 |        3 |
|     20 |        5 |
|     30 |        6 |
+--------+----------+
3 rows in set (0.00 sec)

mysql> select dept.dname,emp.deptno, count(*) from emp,dept where emp.deptno =dept.deptno group by emp.deptno;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'scott.dept.dname' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select dept.dname,emp.deptno, count(*) from emp,dept where emp.deptno =dept.deptno group by emp.deptno,dept.dname ;
+------------+--------+----------+
| dname      | deptno | count(*) |
+------------+--------+----------+
| ACCOUNTING |     10 |        3 |
| RESEARCH   |     20 |        5 |
| SALES      |     30 |        6 |
+------------+--------+----------+
3 rows in set (0.00 sec)

mysql> select dept.dname,emp.deptno, count(*),dept.loc from emp,dept where emp.deptno =dept.deptno group by emp.deptno,dept.dname ;
ERROR 1055 (42000): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'scott.dept.loc' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select dept.dname,emp.deptno, count(*),dept.loc from emp,dept where emp.deptno =dept.deptno group by emp.deptno,dept.dname,dept.loc ;
+------------+--------+----------+----------+
| dname      | deptno | count(*) | loc      |
+------------+--------+----------+----------+
| ACCOUNTING |     10 |        3 | NEW YORK |
| RESEARCH   |     20 |        5 | DALLAS   |
| SALES      |     30 |        6 | CHICAGO  |
+------------+--------+----------+----------+
3 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> select dept.dname,emp.deptno, count(*) total,dept.loc from emp,dept where emp.deptno =dept.deptno group by emp.deptno,dept.dname,,dept.loc ;
+------------+--------+-------+----------+
| dname      | deptno | total | loc      |
+------------+--------+-------+----------+
| ACCOUNTING |     10 |     3 | NEW YORK |
| RESEARCH   |     20 |     5 | DALLAS   |
| SALES      |     30 |     6 | CHICAGO  |
+------------+--------+-------+----------+
3 rows in set (0.00 sec)

mysql> select dept.dname,emp.deptno, count(*) total,dept.loc from emp,dept where emp.deptno =dept.deptno group by emp.deptno,dept.dname,dept.loc order by total desc ;
+------------+--------+-------+----------+
| dname      | deptno | total | loc      |
+------------+--------+-------+----------+
| SALES      |     30 |     6 | CHICAGO  |
| RESEARCH   |     20 |     5 | DALLAS   |
| ACCOUNTING |     10 |     3 | NEW YORK |
+------------+--------+-------+----------+
3 rows in set (0.00 sec)

mysql> 

 

这一系列的 MySQL 查询演示了如何使用 COUNT() 函数结合 GROUP BY 子句来统计每个部门的员工数量，并通过连接 emp 和 dept 表来获取更丰富的部门信息。
 

首先，查询了 emp 表中的总员工数。
 

然后，通过对 emp 表使用 GROUP BY deptno 子句，查询了每个部门的员工数量。
 

通过将 deptno 和员工数量同时选择，明确了每个部门编号及其对应的员工数量。
 

接着，尝试将 emp 表和 dept 表进行笛卡尔积查询，然后限制了结果集，只显示那些部门编号匹配的记录，从而提供了每个部门的详细信息及其员工数量。
 

通过对连接查询的结果应用 GROUP BY 子句，错误地尝试了将非聚合列包含在选择列表中，但没有将它们加入 GROUP BY 子句，这违反了启用 only_full_group_by SQL 模式时的要求。
 

修正了上述问题，通过在 GROUP BY 子句中明确指定所有非聚合列，成功地获取了每个部门的名称、编号、员工数量和位置。
 

最后，通过修改选择列表和 GROUP BY 子句，按员工总数降序排列了结果，从而可以快速识别员工数量最多和最少的部门。
 

小结论
 

解决多表问题的本质：想办法将多表转化成为单表，所以mysql中，所有select的问题全部都可以转成单表问题！！
 

结尾
 

最后，感谢您阅读我的文章，希望这些内容能够对您有所启发和帮助。如果您有任何问题或想要分享您的观点，请随时在评论区留言。
 

 

同时，不要忘记订阅我的博客以获取更多有趣的内容。在未来的文章中，我将继续探讨这个话题的不同方面，为您呈现更多深度和见解。
 

 

谢谢您的支持，期待与您在下一篇文章中再次相遇！

                

        

        

    

  


    

      

        

            

              
                
                  妖精七七_
                
              
            

            

                关注
              
            

        

        

          
      

      

            

                专栏目录
          









                



	

		

			

				
					
SQL查询：如何在where条件中使用子查询

				
			

			

				

					sg_knight的专栏
				

				

					03-28
					
					1847
					
				

			

		

		

			
				
子查询在 WHERE 条件中的使用能够极大地增强 SQL 查询的灵活性和表达能力，可以满足复杂查询需求。但是在使用子查询时，需要注意查询效率和结果集的大小，避免性能问题和不必要的复杂性。

			
		

	



                

            
              



	

		

			

				
					
SQL Server 子查询

				
			

			

				

					藜笙的博客
				

				

					03-25
					
					1144
					
				

			

		

		

			
				
目录

SQL Server 子查询

SQL Server嵌套子查询

SQL Server相关子查询








	SQL Server 子查询
	
 子查询是嵌套在另一个语句(如：[SELECT]，[INSERT]，[UPDATE]或[DELETE])中的查询。



以下语句显示如何在 SELECT 语句的[WHERE]子句中使用子查询来查找位于纽约( New York )的客户的销

售订单：


SELECT

 order_id,

 order_date,

 cust...

			
		

	



              


  
              

                


	

		

			

				
					
sql子查询之from后面的子查询

				
			

			

				

					youhebuke225的博客
				

				

					08-31
					
					2113
					
				

			

		

		

			
				
查询人员性别的平均年龄躲在的年龄水平。

			
		

	





	

		

			

				
					
My sql   from子查询 师生系统题

				
			

			

				

					luckyGirlIT的博客
				

				

					06-15
					
					512
					
				

			

		

		

			
				
mysql   C级 from子查询师生系统题
mysql   C级 from子查询师生系统题
1、查询“001”课程比“002”课程成绩高的所有学生的学号；
2、查询学生平均成绩及其名次
3、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
4、按各科平均成绩从低到高和及格率的百分数从高到低顺序
5、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”...

			
		

	



		

			
		



	

		

			

				
					
SQL中的子查询

					
热门推荐

				
			

			

				

					C2774593599的博客
				

				

					04-17
					
					1万+
					
				

			

		

		

			
				
当我们进⾏语句查询的时候，总会遇到我们需要的条件需要通过另⼀个查询语句查询出来后才能进⾏，就是说A 查询语句需要依赖B 查询语句的查询结果，B 查询就是⼦查询，A 查询语句就是主查询，⼀个SQL语句可以包含多个⼦查询。

			
		

	





	

		

			

				
					
SQL——子查询

				
			

			

				

					小平凡的记录的博客
				

				

					04-14
					
					2296
					
				

			

		

		

			
				
当查询需分步时,只能用子查询,如例56; 不能将列值与统计结果值进行比较的条件写在WHERE中,只能用子查询; 查询目标列来自多个表时,只能用多表连接实现.

			
		

	





	

		

			

				
					
SQL子查询语句简单示例

				
			

			

				

					01-19
				

			

		

		

			
				
SQL 中的 SELECT 命令不仅能够直接用来查询数据库中的数据，还能够嵌入到 WHERE 中作为子查询语句，或者将查询结果插入到另一个表中。 	　将子查询用于 WHERE 子句 	　子查询能够与 SELECT、INSERT、UPDATE 和 ...

			
		

	





	

		

			

				
					
sql子查询总结

				
			

			

				

					11-05
				

			

		

		

			
				
在 Transact-SQL 中，包含子查询的语句和语义上等效的不包含子查询的语句在性能上通常没有差别。但是，在一些必须检查存在性的情况中，使用联接会产生更好的性能。  子查询的 advantages包括：  * 可以用在使用...

			
		

	





	

		

			

				
					
sql子查询说明

				
			

			

				

					10-25
				

			

		

		

			
				
### SQL子查询详解  #### 一、子查询概念与应用场景  **子查询**是指在一个查询语句中嵌套另一个查询语句，被嵌套的查询称为子查询，而包含子查询的查询则被称为外层查询或主查询。子查询通常用于获取更复杂的数据...

			
		

	





	

		

			

				
					
SQL子查询和嵌套查询[收集].pdf

				
			

			

				

					10-12
				

			

		

		

			
				
SQL子查询和嵌套查询是数据库管理中非常重要的概念，特别是在软件开发领域，它们被广泛应用于数据查询和处理。子查询是指在SQL语句中嵌入另一条完整的SQL查询，通常用于获取临时结果或者作为主查询的一部分来过滤...

			
		

	





	

		

			

				
					
MySQL的子查询中FROM和EXISTS子句的使用教程

				
			

			

				

					12-15
				

			

		

		

			
				
FROM 子查询
FROM 子句中的子查询
MySQL FROM 子查询是指 FROM 的子句作为子查询语句，主查询再到子查询结果中获取需要的数据。FROM 子查询语法如下：


SELECT ... FROM (subquery) AS name ...

子查询会生成一个临时表，由于 FROM 子句中的每个表必须有一个名称，因此 AS name 是必须的。FROM 子查询也称为衍生数据表子查询。
FROM 子查询实例
table1:


s1 s2
1  5
2  12
3  20

FROM 子查询 SQL 如下：
SELECT s1,s2 FROM (SELECT s1, s2*2 A

			
		

	





	

		

			

				
					
SQL子查询介绍

				
			

			

				

					weixin_63624644的博客
				

				

					10-05
					
					145
					
				

			

		

		

			
				
【代码】SQL子查询介绍。

			
		

	





	

		

			

				
					
sql子查询

					
最新发布

				
			

			

				

					weixin_51900414的博客
				

				

					06-12
					
					507
					
				

			

		

		

			
				
子查询又称嵌套查询，指嵌套在sql语句中使用的查询，它可以嵌套在主查询中的SELECT，INSERT，UPDATE或DELETE语句中，或者在另一个子查询中。子查询可以是一个完整的SELECT语句（也可以包括ORDER BY, GROUP BY等子句），或者只是SELECT语句的一部分（如WHERE子句中的条件）。

			
		

	





	

		

			

				
					
MySQL from 部分的子查询

				
			

			

				

					hKY0fB8kZ6xY
				

				

					11-26
					
					7421
					
				

			

		

		

			
				
摘要:
  
  MySQL 语句中 from 部分的子查询

			
		

	





	

		

			

				
					
SQL 嵌套子查询

				
			

			

				

					Alkali!的博客
				

				

					01-16
					
					1653
					
				

			

		

		

			
				
SQL 嵌套子查询概述

			
		

	





	

		

			

				
					
My SQL 子查询

				
			

			

				

					汉南最後の読書人
				

				

					10-06
					
					1166
					
				

			

		

		

			
				
1.使用IN关键字的子查询
    例：查询游戏类型是“棋牌类”的游戏的分数信息
    1.1过滤掉游戏分数表中未包含游戏类型的玩家
    1.2思路一：采用连接查询
    1.3思路二：分两步进行，首先找到所有“棋牌类”游戏的编号，再以这组编号为查询依据得到对应的玩家分数信息，完成查询
SELECT *FROM scores WHERE gno IN //得到对应的玩家分数信息（

			
		

	





	

		

			

				
					
from 子查询

				
			

			

				

					Aps__的博客
				

				

					12-03
					
					1841
					
				

			

		

		

			
				
10-3 5-3 查询生产最高速度的计算机(PC或便携式电脑)厂商 (20 分)
本题目要求编写SQL语句， 查询生产最高速度的计算机(PC或便携式电脑)厂商，查询结果按照厂商升序排列。
提示：请使用SELECT语句作答。
CREATE TABLE product
( maker CHAR(20) ,          --制造商
  model CHAR(20) NOT NULL,  --产品型号
  type CHAR(20),            --产品类型
  PRIMARY KEY(model

			
		

	





	

		

			

				
					

				
			

			

				

					
				

			

		

		

			
				

			
		

	



              




          




        



    





    



      

      

        
      

      





	

		
评论 2

	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  





	
打赏作者

	

		

		
			
		
		

		

			
妖精七七_

			
你的鼓励将是我创作的最大动力

		

	

	

			

	

	

    ¥1
    ¥2
    ¥4
    ¥6
    ¥10
    ¥20
	

	

		

			

				

					扫码支付：¥1
				

				

					

					
					获取中
					

				

				

					
					
					扫码支付
				

			

		

		

			
您的余额不足，请更换扫码支付或充值

			
打赏作者

		

	



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值