【二】【SQL】去重表数据及分组聚合查询

已于 2024-02-28 19:17:50 修改
阅读量730 收藏 10
点赞数 20
分类专栏： 数据库SQL 文章标签： 数据库 sql
于 2024-02-28 16:36:28 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_74163972/article/details/136349843
版权

去重表数据

表的准备工作

去除表中重复的数据，重复的数据只留一份。

 

mysql> create table duplicate_table (
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into duplicate_table values
    -> (100,'aaa'),
    -> (100,'aaa'),
    -> (200,'bbb'),
    -> (200,'bbb'),
    -> (200,'bbb'),
    -> (300,'ccc');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select *from duplicate_table;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  100 | aaa  |
|  200 | bbb  |
|  200 | bbb  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
6 rows in set (0.00 sec)

mysql> 

 

去除表中重复的数据，重复的数据只留一份
 

 

mysql> create table no_duplicate_table like duplicate_table;
Query OK, 0 rows affected (0.03 sec)

mysql> desc no_duplicate_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc duplicate_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select distinct *from no_duplicate_table ;
Empty set (0.00 sec)

mysql> select distinct *from duplicate_table ;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
3 rows in set (0.00 sec)

mysql> insert into no_duplicate_table select distinct *from duplicate_table;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select *from no_duplicate_table;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
3 rows in set (0.00 sec)

mysql> rename table duplicate_table to old_duplicate_table ;
Query OK, 0 rows affected (0.04 sec)

mysql> rename table no_duplicate_table to duplicate_table ;
Query OK, 0 rows affected (0.01 sec)

mysql> select *from duplicate_table ;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
3 rows in set (0.00 sec)

mysql> 

 

 

聚合统计
 

 
函数
 		 
说明
 
 
COUNT（DISTINCT）
 		 
返回查询到的数据的 数量
 
 
SUM（DISTINCT）
 		 
返回查询到的数据的 总和，不是数字没有意义
 
 
AVG（DISTINCT）
 		 
返回查询到的数据的 平均值，不是数字没有意义
 
 
MAX（DISTINCT）
 		 
返回查询到的数据的 最大值，不是数字没有意义
 
 
MIN（DISTINCT）
 		 
返回查询到的数据的 最小值，不是数字没有意义
 
 

count简单使用
 

 

mysql> select *from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |     134 |   98 |      56 |
|  3 | 猪悟能    |     176 |   98 |      90 |
|  4 | 曹孟德    |     140 |   90 |      67 |
|  6 | 孙权      |     140 |   73 |      78 |
|  7 | 宋公明    |     150 |   95 |      30 |
+----+-----------+---------+------+---------+
5 rows in set (0.00 sec)

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

mysql> select count(1) 总数 from exam_result;
+--------+
| 总数   |
+--------+
|      5 |
+--------+
1 row in set (0.00 sec)

mysql> select count(2) 总数 from exam_result;
+--------+
| 总数   |
+--------+
|      5 |
+--------+
1 row in set (0.00 sec)

mysql> select math from exam_result;
+------+
| math |
+------+
|   98 |
|   98 |
|   90 |
|   73 |
|   95 |
+------+
5 rows in set (0.00 sec)

mysql> select count(math) from exam_result;
+-------------+
| count(math) |
+-------------+
|           5 |
+-------------+
1 row in set (0.00 sec)

mysql> select count(math) as res from exam_result;
+-----+
| res |
+-----+
|   5 |
+-----+
1 row in set (0.00 sec)

mysql> 

 

统计本次考试的数学成绩分数个数
 

 

mysql> select math from exam_result;
+------+
| math |
+------+
|   98 |
|   98 |
|   90 |
|   73 |
|   95 |
+------+
5 rows in set (0.00 sec)

mysql> select distinct count(math) as res from exam_result;
+-----+
| res |
+-----+
|   5 |
+-----+
1 row in set (0.01 sec)

mysql> select count(distinct math) as res from exam_result;
+-----+
| res |
+-----+
|   4 |
+-----+
1 row in set (0.00 sec)

mysql> 

 

数学英语平均分，不及格个数，不及格总分，不及格平均分
 

 

mysql> select sum(math) from exam_result;
+-----------+
| sum(math) |
+-----------+
|       454 |
+-----------+
1 row in set (0.00 sec)

mysql> select sum(math)/count(*) from exam_result;
+--------------------+
| sum(math)/count(*) |
+--------------------+
|               90.8 |
+--------------------+
1 row in set (0.00 sec)

mysql> select sum(english)/count(*) from exam_result;
+-----------------------+
| sum(english)/count(*) |
+-----------------------+
|                  64.2 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select count(*) from exam_result where math<60;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from exam_result where english<60;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> select *from exam_result where english<60;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |     134 |   98 |      56 |
|  7 | 宋公明    |     150 |   95 |      30 |
+----+-----------+---------+------+---------+
2 rows in set (0.00 sec)

mysql> select sum(english) from exam_result where english<60;
+--------------+
| sum(english) |
+--------------+
|           86 |
+--------------+
1 row in set (0.00 sec)

mysql> select sum(english)/count(english) from exam_result where english<60;
+-----------------------------+
| sum(english)/count(english) |
+-----------------------------+
|                          43 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> 

 

统计平均总分
 

 

mysql> select sum(math)/count(*) from exam_result;
+--------------------+
| sum(math)/count(*) |
+--------------------+
|               90.8 |
+--------------------+
1 row in set (0.00 sec)

mysql> select avg(math) from exam_result;
+-----------+
| avg(math) |
+-----------+
|      90.8 |
+-----------+
1 row in set (0.00 sec)

mysql> select avg(math+chinese+english) from exam_result;
+---------------------------+
| avg(math+chinese+english) |
+---------------------------+
|                       303 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select name,math+english+chinese from exam_result;
+-----------+----------------------+
| name      | math+english+chinese |
+-----------+----------------------+
| 唐三藏    |                  288 |
| 猪悟能    |                  364 |
| 曹孟德    |                  297 |
| 孙权      |                  291 |
| 宋公明    |                  275 |
+-----------+----------------------+
5 rows in set (0.00 sec)

mysql> select max(english) from exam_result;
+--------------+
| max(english) |
+--------------+
|           90 |
+--------------+
1 row in set (0.00 sec)

mysql> select name ,max(english) from exam_result;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list containode=only_full_group_by
mysql> 

 

返回>70分以上的数学最低分
 

 

mysql> select math from exam_result;
+------+
| math |
+------+
|   98 |
|   98 |
|   90 |
|   73 |
|   95 |
+------+
5 rows in set (0.00 sec)

mysql> select math from exam_result where math>70;
+------+
| math |
+------+
|   98 |
|   98 |
|   90 |
|   73 |
|   95 |
+------+
5 rows in set (0.00 sec)

mysql> select min(math) from exam_result where math>70;
+-----------+
| min(math) |
+-----------+
|        73 |
+-----------+
1 row in set (0.00 sec)

mysql> 

 

分组聚合查询
 

导入数据库样例sql文件
 

scott_data.sql（文件名）（下面是文件内容）
 

      
DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `scott`;

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);


DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);


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 '此等级最高工资'
);


insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

    
 

 rz选择scott_data.sql文件导入数据
 

[root@VM-8-12-centos d1]# cd /var/lib/mysql
[root@VM-8-12-centos mysql]# ls
auto.cnf         d1              ibtmp1              private_key.pem
ca-key.pem       ib_buffer_pool  mysql               public_key.pem
ca.pem           ibdata1         mysql.sock          server-cert.pem
client-cert.pem  ib_logfile0     mysql.sock.lock     server-key.pem
client-key.pem   ib_logfile1     performance_schema  sys
[root@VM-8-12-centos mysql]# rz     

[root@VM-8-12-centos mysql]# ls
auto.cnf         d1              ibtmp1              private_key.pem  sys
ca-key.pem       ib_buffer_pool  mysql               public_key.pem
ca.pem           ibdata1         mysql.sock          scott_data.sql
client-cert.pem  ib_logfile0     mysql.sock.lock     server-cert.pem
client-key.pem   ib_logfile1     performance_schema  server-key.pem

 

 

mysql> source /var/lib/mysql/scott_data.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept            |
| emp             |
| salgrade        |
+-----------------+
3 rows in set (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| d1                 |
| mysql              |
| performance_schema |
| scott              |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> 

 

展示样例sql数据表
 

 

mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept            |
| emp             |
| salgrade        |
+-----------------+
3 rows in set (0.00 sec)

mysql> desc dept;
+--------+--------------------------+------+-----+---------+-------+
| Field  | Type                     | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+-------+
| deptno | int(2) unsigned zerofill | NO   |     | NULL    |       |
| dname  | varchar(14)              | YES  |     | NULL    |       |
| loc    | varchar(13)              | YES  |     | NULL    |       |
+--------+--------------------------+------+-----+---------+-------+
3 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> desc emp;
+----------+--------------------------+------+-----+---------+-------+
| Field    | Type                     | Null | Key | Default | Extra |
+----------+--------------------------+------+-----+---------+-------+
| empno    | int(6) unsigned zerofill | NO   |     | NULL    |       |
| ename    | varchar(10)              | YES  |     | NULL    |       |
| job      | varchar(9)               | YES  |     | NULL    |       |
| mgr      | int(4) unsigned zerofill | YES  |     | NULL    |       |
| hiredate | datetime                 | YES  |     | NULL    |       |
| sal      | decimal(7,2)             | YES  |     | NULL    |       |
| comm     | decimal(7,2)             | YES  |     | NULL    |       |
| deptno   | int(2) unsigned zerofill | YES  |     | NULL    |       |
+----------+--------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

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

mysql> desc salgrade;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| grade | int(11) | YES  |     | NULL    |       |
| losal | int(11) | YES  |     | NULL    |       |
| hisal | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select *from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

mysql> 

 

如何显示每个部门的平均工资和最高工资
 

 

mysql> select max(sal) 最高,avg(sal) 平均 from emp;
+---------+-------------+
| 最高    | 平均        |
+---------+-------------+
| 5000.00 | 2073.214286 |
+---------+-------------+
1 row in set (0.00 sec)

mysql> select max(sal) 最高,avg(sal) 平均 from emp group by deptno;
+---------+-------------+
| 最高    | 平均        |
+---------+-------------+
| 5000.00 | 2916.666667 |
| 3000.00 | 2175.000000 |
| 2850.00 | 1566.666667 |
+---------+-------------+
3 rows in set (0.00 sec)

mysql> select deptno,max(sal) 最高,avg(sal) 平均 from emp group by deptno;
+--------+---------+-------------+
| deptno | 最高    | 平均        |
+--------+---------+-------------+
|     10 | 5000.00 | 2916.666667 |
|     20 | 3000.00 | 2175.000000 |
|     30 | 2850.00 | 1566.666667 |
+--------+---------+-------------+
3 rows in set (0.00 sec)

mysql> 

 

显示每个部门的每种岗位的平均工资和最低工资
 

 

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

mysql> select deptno,avg(sal) 平均,min(sal) 最低 from emp group by deptno,job;
+--------+-------------+---------+
| deptno | 平均        | 最低    |
+--------+-------------+---------+
|     10 | 1300.000000 | 1300.00 |
|     10 | 2450.000000 | 2450.00 |
|     10 | 5000.000000 | 5000.00 |
|     20 | 3000.000000 | 3000.00 |
|     20 |  950.000000 |  800.00 |
|     20 | 2975.000000 | 2975.00 |
|     30 |  950.000000 |  950.00 |
|     30 | 2850.000000 | 2850.00 |
|     30 | 1400.000000 | 1250.00 |
+--------+-------------+---------+
9 rows in set (0.00 sec)

mysql> select deptno,job, avg(sal) 平均,min(sal) 最低 from emp group by deptno,job;
+--------+-----------+-------------+---------+
| deptno | job       | 平均        | 最低    |
+--------+-----------+-------------+---------+
|     10 | CLERK     | 1300.000000 | 1300.00 |
|     10 | MANAGER   | 2450.000000 | 2450.00 |
|     10 | PRESIDENT | 5000.000000 | 5000.00 |
|     20 | ANALYST   | 3000.000000 | 3000.00 |
|     20 | CLERK     |  950.000000 |  800.00 |
|     20 | MANAGER   | 2975.000000 | 2975.00 |
|     30 | CLERK     |  950.000000 |  950.00 |
|     30 | MANAGER   | 2850.000000 | 2850.00 |
|     30 | SALESMAN  | 1400.000000 | 1250.00 |
+--------+-----------+-------------+---------+
9 rows in set (0.01 sec)

mysql> select ename,deptno,job, avg(sal) 平均,min(sal) 最低 from emp group by deptno,job;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'scott.emp.ename' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> 

 

显示平均工资低于2000的部门和它的平均工资
 

 

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

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

mysql> select deptno,avg(sal) deptavg from emp group by deptno having deptavg<2000;
+--------+-------------+
| deptno | deptavg     |
+--------+-------------+
|     30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)

mysql> select deptno,avg(sal) deptavg from emp group by deptno where deptavg<2000;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where deptavg<2000' at line 1
mysql> select *from emp having ename='SMITH';
+--------+-------+-------+------+---------------------+--------+------+--------+
| empno  | ename | job   | mgr  | hiredate            | sal    | comm | deptno |
+--------+-------+-------+------+---------------------+--------+------+--------+
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL |     20 |
+--------+-------+-------+------+---------------------+--------+------+--------+
1 row in set (0.00 sec)

mysql> 

 

（SMITH不参加统计）显示平均工资低于2000的部门和它的平均工资
 

 

mysql> select deptno, job from emp where ename!='SMITH' group by deptno ,job;
+--------+-----------+
| deptno | job       |
+--------+-----------+
|     10 | CLERK     |
|     10 | MANAGER   |
|     10 | PRESIDENT |
|     20 | ANALYST   |
|     20 | CLERK     |
|     20 | MANAGER   |
|     30 | CLERK     |
|     30 | MANAGER   |
|     30 | SALESMAN  |
+--------+-----------+
9 rows in set (0.00 sec)

mysql> select deptno, job ,max(sal) 最高,min(sal) 最低 from emp where ename!='SMITH' group by deptno ,job;
+--------+-----------+---------+---------+
| deptno | job       | 最高    | 最低    |
+--------+-----------+---------+---------+
|     10 | CLERK     | 1300.00 | 1300.00 |
|     10 | MANAGER   | 2450.00 | 2450.00 |
|     10 | PRESIDENT | 5000.00 | 5000.00 |
|     20 | ANALYST   | 3000.00 | 3000.00 |
|     20 | CLERK     | 1100.00 | 1100.00 |
|     20 | MANAGER   | 2975.00 | 2975.00 |
|     30 | CLERK     |  950.00 |  950.00 |
|     30 | MANAGER   | 2850.00 | 2850.00 |
|     30 | SALESMAN  | 1600.00 | 1250.00 |
+--------+-----------+---------+---------+
9 rows in set (0.00 sec)

mysql> select deptno, job ,avg(sal) myavg from emp where ename!='SMITH' group by deptno ,job;
+--------+-----------+-------------+
| deptno | job       | myavg       |
+--------+-----------+-------------+
|     10 | CLERK     | 1300.000000 |
|     10 | MANAGER   | 2450.000000 |
|     10 | PRESIDENT | 5000.000000 |
|     20 | ANALYST   | 3000.000000 |
|     20 | CLERK     | 1100.000000 |
|     20 | MANAGER   | 2975.000000 |
|     30 | CLERK     |  950.000000 |
|     30 | MANAGER   | 2850.000000 |
|     30 | SALESMAN  | 1400.000000 |
+--------+-----------+-------------+
9 rows in set (0.00 sec)

mysql> select deptno, job ,avg(sal) myavg from emp where ename!='SMITH' group by deptno ,job having mysal<2000;
ERROR 1054 (42S22): Unknown column 'mysal' in 'having clause'
mysql> select deptno, job ,avg(sal) myavg from emp where ename!='SMITH' group by deptno ,job having myavg<2000;
+--------+----------+-------------+
| deptno | job      | myavg       |
+--------+----------+-------------+
|     10 | CLERK    | 1300.000000 |
|     20 | CLERK    | 1100.000000 |
|     30 | CLERK    |  950.000000 |
|     30 | SALESMAN | 1400.000000 |
+--------+----------+-------------+
4 rows in set (0.00 sec)

mysql> 

 

结尾
 

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

 

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

 

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

                

        

        

    




    

      

        

            

              
                
                  妖精七七_
                
              
            

            

                关注
              
            

        

        

          
    
            
  • 
              
                
                
                
                
                    20
                
              
              
    点赞
    
            
    • 
            
  • 
              
                
                
                
              
              
    踩
    
            
    • 
            
  • 
              
                
                
                
                
                    10
                
              
              
    
                
    
                  
                    收藏
                  
                
    
              
    
              
    
                
    
                  觉得还不错?
                  
                    一键收藏
                  
                 
                
    
              
    
            
    • 
                
  • 
                  
                    打赏
                    
                  
                  
    打赏
    
                
    • 
          
  • 
            
    
              
              
            
    
              
              
              
                  1
              
            
            
    评论
    
          
    • 
          
  • 
          
    • 
          
  • 
            
              
            
              
    
            
              
                
            
    
          
    • 
        

      

      

            

                专栏目录
          










                



	

		

			

				
					
MySQL 分组查询和聚合函数

				
			

			

				

					12-14
				

			

		

		

			
				
概述

相信我们经常会遇到这样的场景：想要了解双十一天猫购买化妆品的人员中平均消费额度是多少（这可能有利于对商品价格区间的定位）；或者不同年龄段的化妆品消费占比是多少（这可能有助于对商品备货量的预估）。
这个时候就要用到分组查询，分组查询的目的是为了把数据分成多个逻辑组（购买化妆品的人员是一个组，不同年龄段购买化妆品的人员也是组），并对每个组进行聚合计算的过程:。
分组查询的语法格式如下：

 select cname, group_fun,... from tname [where condition]
 group by group_expression [having group_con

			
		

	



                

              
                



	

		

			

				
					
SqlSugar 3.分组去重

				
			

			

				

					DotNeter-Hpf的博客
				

				

					05-03
					
					6276
					
				

			

		

		

			
				
文章目录1.普通分组查询(用where)2.聚合对象条件筛选(用Having)3.Distinct使用4.分组获取第一条4.1 MySql、Sqlite写法4.2 SqlServer、Oracle写法5.特殊日期分组
1.普通分组查询(用where)
var list1 = db.Queryable<Student>().GroupBy(it => new { it.Name })
               .Where(it => it.StudentId > 2)
  

			
		

	



                


  
              

                


	

		

			

				
					
mysql表中删除重复记录，只保留一条记录的操作

				
			

			

				

					weixin_42260782的博客
				

				

					09-05
					
					5425
					
				

			

		

		

			
				
此操作找出了需要保留的记录，即按name、class进行分组，取出id最小的记录，删除id不在上面的记录即可，最终保留的记录的id为1、3、4、5、6、8，原表中的2、7、9、10均为要删除的重复记录。对比元数据表，可以看到t1表中是id较大的记录 2、7、9、10刚好就是重复的记录，所以直接使用上面的。其中name  和  class 相同的视为重复记录，需要保留一条记录，删除重复记录，这是找出两张表中的name与class相同的记录的视图，加上判断条件。运行选中的代码，此时未加条件。

			
		

	





	

		

			

				
					
sql去重；同一条数据出现多条取一条的sql语句

				
			

			

				

					q571947988的博客
				

				

					04-16
					
					3万+
					
				

			

		

		

			
				
理论上相同数据个别字段值不同重复问题

			
		

	



		

			
		



	

		

			

				
					
Sql去重查询数据

				
			

			

				

					商务合作 | 面试培训 | 职场规划 ==>主页扫码
				

				

					06-10
					
					4万+
					
				

			

		

		

			
				
最近在工作过程中，面试过程中，部分求职者或者同事，对sql怎么去重查询，不是太熟练

今天下午忙里偷闲， 整理了一下

其实sql基本的查询 ，还是蛮有意思， 下面是我大致整理的几种去重查询

1.存在2条一样的数据， 使用distinct

eg: select distinct * from table(表名) where 条件



2.存在部分字段相同(有key, id...

			
		

	





	

		

			

				
					
SQL去重的三种方法汇总​

					
热门推荐

				
			

			

				

					
				

				

					02-22
					
					6万+
					
				

			

		

		

			
				
只能一列去重，当distinct后跟大于1个参数时，他们之间的关系是&&(逻辑与)关系，只有全部条件相同才会去重。去重原理：现根据重复列进行分组，分组后再进行排序，不同的组序号为1，相同的组序号为2，排除为2的就达到了去重效果。弊端：当查询的字段比较多时，distinct会作用多个字段，导致去重条件增多。弊端：使用group by后，所有查询字段都需要使用聚合函数，比较繁琐。这里的去重是指：查询的时候, 不显示重复，并不是删除表中的重复项。去重原理：将重复的行进行分组，相同的数据只显示第一行。

			
		

	





	

		

			

				
					
SQL：数据去重的三种方法

				
			

			

				

					eagle89的专栏
				

				

					01-01
					
					5万+
					
				

			

		

		

			
				
MYSQL去重方法

			
		

	





	

		

			

				
					
聚合（分组）函数去重DISTINCT

					
最新发布

				
			

			

				

					weixin_44620012的博客
				

				

					01-15
					
					355
					
				

			

		

		

			
				
select sum(distinct 字段) from 表;都可以搭配distinct使用，统计去重后的结果。

			
		

	





	

		

			

				
					
MySQL中聚合函数及distinct去重关键字的使用

				
			

			

				

					CodeBlues的博客
				

				

					08-25
					
					1731
					
				

			

		

		

			
				
一、聚合函数（aggregation function）

（1）每个组函数接受一个参数

（2）聚合函数是汇总处理数据（会进行数学运算），不同于SELECT的检索数据。

（3）count也可以和distinct去除字段值重复的条数，eg：count（distinct 列）


			AVG（字段名）
			
			求平均值
		COUNT（字段名）
			统计行的数量
		MAX（字段名）
			求最大值
		MIN（字段名）
			求最小值
		SUM（字段名）
			求累加和
		注：

 ...

			
		

	





	

		

			

				
					
第三章SQL数据查询单表查询优秀文档.ppt

				
			

			

				

					11-30
				

			

		

		

			
				
第三章SQL数据查询单表查询优秀文档.ppt

			
		

	





	

		

			

				
					
Sequelize中用group by进行分组聚合查询

				
			

			

				

					12-23
				

			

		

		

			
				
一、SQL与Sequelize中的分组查询  1.1 SQL中的分组查询  SQL查询中，通GROUP BY语名实现分组查询。GROUP BY子句要和聚合函数配合使用才能完成分组查询，在SELECT查询的字段中，如果没有使用聚合函数就必须出现在...

			
		

	





	

		

			

				
					
浅谈sql数据库去重

				
			

			

				

					12-15
				

			

		

		

			
				
关于sql去重，我简单谈一下自己的简介，如果各位有建议或有不明白的欢迎多多指出。  　关于sql去重最常见的有两种方式：DISTINCT和ROW_NUMBER()，当然了ROW_NUMBER()除了去重还有很多其他比较重要的功能，一会我给...

			
		

	





	

		

			

				
					
MySQL数据表合并去重的简单实现方法

				
			

			

				

					12-16
				

			

		

		

			
				
爬取的数据生成数据表，结构与另一个主表相同，需要进行合并+去重 解决：（直接举例）  首先创建两个表pep，pep2，其中pep是主表  CREATE TABLE IF NOT EXISTS `pep/pep2`( `id` INT UNSIGNED AUTO_INCREMENT, `no` ...

			
		

	





	

		

			

				
					
SQL数据库高级查询语句--去重，排序，分组查询，聚合函数，分组及综合查询

				
			

			

				

					m0_68154641的博客
				

				

					07-27
					
					782
					
				

			

		

		

			
				
SQL数据库高级查询语句--去重，排序，分组查询，聚合函数，分组及综合查询

			
		

	





	

		

			

				
					
sql分组去重计数distinct&count&group by

				
			

			

				

					pscool的博客
				

				

					10-12
					
					3404
					
				

			

		

		

			
				
count 与 distinct 连用，可实现去重计数；加上group by 可实现分组去重计数。

			
		

	





	

		

			

				
					
sql 分组去重

				
			

			

				

					weixin_35750483的博客
				

				

					12-20
					
					2465
					
				

			

		

		

			
				
在 SQL 中，要对查询结果进行分组并去重，可以使用 GROUP BY 和 DISTINCT 关键字。
例如，假设有一张名为 orders 的表，其中有两个字段：customer_id 和 product_id。要查询每个客户订购的不同的产品数量，可以使用以下查询：
SELECT customer_id, COUNT(DISTINCT product_id)
FROM orders
GROUP B...

			
		

	





	

		

			

				
					
SQL 分组统计去重有条件的过滤

				
			

			

				

					奔跑吧笨笨的博客
				

				

					05-30
					
					3203
					
				

			

		

		

			
				
见字如面，如标题拆分：

分组 GROUP BYfield_name
	统计 COUNT(field_name)
	去重 DISTINCT field_name
	条件过滤  CASE WHEN age> 18THEN ageEND


示例：

前序：

表结构


CREATE TABLE `data_table_name` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键'...

			
		

	





	

		

			

				
					
sql去重查询group by

				
			

			

				

					04-07
				

			

		

		

			
				
在SQL中，可以使用GROUP BY子句对数据进行分组，并对每个组应用聚合函数来计算总计、平均值、最大值或最小值等。在分组之前，通常需要使用DISTINCT关键字来消除重复的行。  例如，假设有一个名为“orders”的表，...

			
		

	



              



  
“相关推荐”对你有帮助么？

  

      
    
          
  • 
              
    
                  
                  
              
    
              
    非常没帮助
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    没帮助
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    一般
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    有帮助
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    非常有帮助
    
          
    • 
      

      

      

          
          提交
      

      

  




          




        



    





    



      

      

        
      

      





	

		
评论 1

	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

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

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  





	
打赏作者

	

		

		
			
		
		

		

			
妖精七七_

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

		

	

	

			

	

	

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

	

		

			

				

					扫码支付：¥1
				

				

					

					
					获取中
					

				

				

					
					
					扫码支付
				

			

		

		

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

			
打赏作者

		

	



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

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

              

            

          

      

      余额充值