sql server如何查询查询多列重复 sql查询多行多列

转载

mob64ca13ff9303 2024-04-11 06:37:17

文章标签 mysql 运维多列 文章分类 SQL Server 数据库

子查询

子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式

子查询返回的数据分类

单行单列：返回的是一个具体列的内容，可以理解为一个单值数据
单行多列：返回一行数据中多个列的内容
多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围
多行多列：查询返回的结果是一张临时表

子查询常出现的位置

select之后：仅支持单行单列
from之后：支持多行多列
where或having之后：支持单行单列、单行多列、多行单列

子查询实例

单行单列

查询运维部所有员工信息

分析：
首先从departments表中查出运维部的编号

mysql> select dept_id from departments where dept_name='运维部';
+---------+
| dept_id |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

再从employees表中查找该部门编号和运维部编号相同的员工

mysql> select *
    -> from employees
    -> where dept_id=(
    ->   select dept_id from departments where dept_name='运维部'
    -> );

查询2018年12月所有比100号员工基本工资高的工资信息

分析：
首先查到2018年12月100号员工的基本工资

mysql> select basic from salary
    -> where year(date)=2018 and month(date)=12 and employee_id=100;
+-------+
| basic |
+-------+
| 14585 |
+-------+
1 row in set (0.00 sec)

再查询2018年12月所有比100号员工基本工资高的工资信息

mysql> select * from salary
    -> where year(date)=2018 and month(date)=12 and basic>(
    ->   select basic from salary
    ->   where year(date)=2018 and month(date)=12 and employee_id=100
    -> );

查询部门员工人数比开发部人数少的部门

分析：
查询开发部部门编号

mysql> select dept_id from departments where dept_name='开发部';
+---------+
| dept_id |
+---------+
|       4 |
+---------+
1 row in set (0.00 sec)

查询开发部人数

mysql> select count(*) from employees
    -> where dept_id=(
    ->   select dept_id from departments where dept_name='开发部'
    -> );
+----------+
| count(*) |
+----------+
|       55 |
+----------+
1 row in set (0.00 sec)

分组查询各部门人数

mysql> select count(*), dept_id from employees group by dept_id;
+----------+---------+
| count(*) | dept_id |
+----------+---------+
|        8 |       1 |
|        5 |       2 |
|        6 |       3 |
|       55 |       4 |
|       12 |       5 |
|        9 |       6 |
|       35 |       7 |
|        3 |       8 |
+----------+---------+
8 rows in set (0.01 sec)

查询部门员工人数比开发部人数少的部门

mysql> select count(*), dept_id from employees group by dept_id
    -> having count(*)<(
    ->   select count(*) from employees
    ->   where dept_id=(
    ->     select dept_id from departments where dept_name='开发部'
    ->   )
    -> );
+----------+---------+
| count(*) | dept_id |
+----------+---------+
|        8 |       1 |
|        5 |       2 |
|        6 |       3 |
|       12 |       5 |
|        9 |       6 |
|       35 |       7 |
|        3 |       8 |
+----------+---------+
7 rows in set (0.00 sec)

查询每个部门的人数

分析：
查询所有部门的信息

mysql> select d.* from departments as d;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 人事部    |
|       2 | 财务部    |
|       3 | 运维部    |
|       4 | 开发部    |
|       5 | 测试部    |
|       6 | 市场部    |
|       7 | 销售部    |
|       8 | 法务部    |
|       9 | NULL      |
+---------+-----------+
9 rows in set (0.00 sec)

查询每个部门的人数

mysql> select d.*, (
    ->  select count(*) from employees as e
    ->   where d.dept_id=e.dept_id
    -> ) as amount
    -> from departments as d;
+---------+-----------+--------+
| dept_id | dept_name | amount |
+---------+-----------+--------+
|       1 | 人事部    |      8 |
|       2 | 财务部    |      5 |
|       3 | 运维部    |      6 |
|       4 | 开发部    |     55 |
|       5 | 测试部    |     12 |
|       6 | 市场部    |      9 |
|       7 | 销售部    |     35 |
|       8 | 法务部    |      3 |
|       9 | NULL      |      0 |
+---------+-----------+--------+
9 rows in set (0.00 sec)

多行单列

查询人事部和财务部员工信息

分析：
查询人事部和财务部编号

mysql> select dept_id from departments
    -> where dept_name in ('人事部', '财务部');
+---------+
| dept_id |
+---------+
|       1 |
|       2 |
+---------+
2 rows in set (0.00 sec)

查询部门编号是两个部门编号的员工信息

mysql> select * from employees
    -> where dept_id in (
    ->   select dept_id from departments
    ->   where dept_name in ('人事部', '财务部')
    -> );

查询人事部2018年12月所有员工工资

分析：
查询人事部部门编号

mysql> select dept_id from departments where dept_name='人事部';
+---------+
| dept_id |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

查询人事部员的编号

mysql> select employee_id from employees
    -> where dept_id=(
    ->   select dept_id from departments where dept_name='人事部'
    -> );
+-------------+
| employee_id |
+-------------+
|           1 |
|           2 |
|           3 |
|           4 |
|           5 |
|           6 |
|           7 |
|           8 |
+-------------+
8 rows in set (0.00 sec)

查询2018年12月人事部所有员工工资

mysql> select * from salary
    -> where year(date)=2018 and month(date)=12 and employee_id in (
    ->   select employee_id from employees
    ->   where dept_id=(
    ->     select dept_id from departments where dept_name='人事部'
    ->   )
    -> );
+------+------------+-------------+-------+-------+
| id   | date       | employee_id | basic | bonus |
+------+------------+-------------+-------+-------+
| 6252 | 2018-12-10 |           1 | 17016 |  7000 |
| 6253 | 2018-12-10 |           2 | 20662 |  9000 |
| 6254 | 2018-12-10 |           3 |  9724 |  8000 |
| 6255 | 2018-12-10 |           4 | 17016 |  2000 |
| 6256 | 2018-12-10 |           5 | 17016 |  3000 |
| 6257 | 2018-12-10 |           6 | 17016 |  1000 |
| 6258 | 2018-12-10 |           7 | 23093 |  4000 |
| 6259 | 2018-12-10 |           8 | 23093 |  2000 |
+------+------------+-------------+-------+-------+
8 rows in set (0.00 sec)

单行多列

查找2018年12月基本工资和奖金都是最高的工资信息

分析：
查询2018年12月最高的基本工资

mysql> select max(basic) from salary
    -> where year(date)=2018 and month(date)=12;
+------------+
| max(basic) |
+------------+
|      25524 |
+------------+
1 row in set (0.00 sec)

查询2018年12月最高的奖金

mysql> select max(bonus) from salary
    -> where year(date)=2018 and month(date)=12;
+------------+
| max(bonus) |
+------------+
|      11000 |
+------------+
1 row in set (0.00 sec)

查询

mysql> select * from salary
    -> where year(date)=2018 and month(date)=12 and basic=(
    ->   select max(basic) from salary
    ->   where year(date)=2018 and month(date)=12
    -> ) and bonus=(
    ->   select max(bonus) from salary
    ->   where year(date)=2018 and month(date)=12
    -> );
+------+------------+-------------+-------+-------+
| id   | date       | employee_id | basic | bonus |
+------+------------+-------------+-------+-------+
| 6368 | 2018-12-10 |         117 | 25524 | 11000 |
+------+------------+-------------+-------+-------+
1 row in set (0.01 sec)

多行多列

查询3号部门及其部门内员工的编号、名字和email

分析
查询3号部门和员工的所有信息

mysql> select d.dept_name, e.*
    -> from departments as d
    -> inner join employees as e
    -> on d.dept_id=e.dept_id;

将上述结果当成一张临时表，必须为其起别名。再从该临时表中查询

mysql> select dept_id, dept_name, employee_id, name, email
    -> from (
    ->   select d.dept_name, e.*
    ->   from departments as d
    ->   inner join employees as e
    ->   on d.dept_id=e.dept_id
    -> ) as tmp_table
    -> where dept_id=3;
+---------+-----------+-------------+-----------+--------------------+
| dept_id | dept_name | employee_id | name      | email              |
+---------+-----------+-------------+-----------+--------------------+
|       3 | 运维部    |          14 | 廖娜      | liaona@guodong.com  |
|       3 | 运维部    |          15 | 窦红梅    | douhongmei@guodong.com|
|       3 | 运维部    |          16 | 聂想      | niexiang@guodong.com|
|       3 | 运维部    |          17 | 陈阳      | chenyang@guodong.com|
|       3 | 运维部    |          18 | 戴璐      | dailu@guodong.com   |
|       3 | 运维部    |          19 | 陈斌      | chenbin@guodong.com |
+---------+-----------+-------------+-----------+--------------------+
6 rows in set (0.00 sec)

作者：一个小运维

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。