首页 > 数据库 >mysql-----------------------------------------------testdata

mysql-----------------------------------------------testdata

时间：2023-12-20 10:44:38浏览次数：30  
标签：name employees SQL ----------------------------------------------- emp mysql NUL

6种SQL数据去重技巧大揭秘！

原创 测试开发成长录 测试开发成长录 2023-12-17 14:08 发表于广东 图片你终于来了，戳蓝一键关注 测试开发成长录不负时光，遇见每一次成长

 

图片

 

 

在上一期中，我们学习了SQL基本语法｜查询语句的使用方法和技巧。接下来，我们将重点学习SQL中去重数据的6种方法。

 

当面试官询问你如何在SQL中去除重复的记录，只保留独一无二的值时，你是否只能想到使用DISTINCT关键字呢？别担心，今天，我将分享给你6种去重方法，让你在面试中脱颖而出。毕竟，只有一个DISTINCT也太单调了嘛！

 

首先，我们创建2个表并插入些数据，用于演示去重方法。

-- 创建员工表
CREATE TABLE `employees` (
 `emp_id` INT ( 11 ) NOT NULL AUTO_INCREMENT,-- 员工ID，主键，自增
 `name` VARCHAR ( 60 ) NOT NULL COMMENT '员工名字',
 `position` VARCHAR ( 100 ) DEFAULT NULL COMMENT '员工职位',
 `department` VARCHAR ( 100 ) DEFAULT NULL COMMENT '员工所属部门',
 `age` INT(3) COMMENT '员工年龄',
 `hire_date` DATE DEFAULT NULL COMMENT '入职日期',
 `birth_date` DATE DEFAULT NULL COMMENT '出生日期',
 `address` VARCHAR ( 255 ) DEFAULT NULL COMMENT '家庭住址',
 `gmt_create` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `gmt_modified` DATETIME DEFAULT NULL COMMENT '修改时间',
 PRIMARY KEY ( `emp_id` ) -- 主键设置为员工ID

) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '员工信息表';-- 使用InnoDB引擎，字符集为utf8mb4，表注释为“员工信息表”

-- 创建员工工资表
CREATE TABLE `salaries` (
 `salary_id` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT '工资记录ID',-- 主键，自增
 `emp_id` INT ( 11 ) NOT NULL COMMENT '员工ID',-- 外键，指向employees表
 `name` VARCHAR ( 60 ) NOT NULL COMMENT '员工名字',
 `salary_amount` DECIMAL ( 10, 2 ) NOT NULL COMMENT '工资总额',
 `payment_date` DATE NOT NULL COMMENT '发放日期',
 `deductions` DECIMAL ( 10, 2 ) DEFAULT '0.00' COMMENT '扣款金额',
 `net_salary` DECIMAL ( 10, 2 ) DEFAULT '0.00' COMMENT '实发工资',
 `gmt_create` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 PRIMARY KEY ( `salary_id` ),
 FOREIGN KEY ( `emp_id` ) REFERENCES `employees` ( `emp_id` ) -- 外键约束，确保员工ID在employees表中存在

) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '员工工资信息表';

-- 插入员工数据
INSERT INTO `employees` (`name`, `position`, `department`, `age`, `hire_date`, `birth_date`, `address`, `gmt_create`, `gmt_modified`) VALUES  
('员工A', '经理', '销售部门', '35', '2021-01-01', '1990-01-01', '北京', NOW(), NULL),  
('员工B', '员工', '人力资源', '25', '2022-02-02', '1992-02-02', '广东', NOW(), NULL),  
('员工C', '员工', '人力资源', '22', '2022-03-03', '1999-03-03', '上海', NOW(), NULL),  
('员工D', '员工', '技术部门', '35', '2022-04-04', '1998-04-04', '山东', NOW(), NULL),
('员工D', '员工', '技术部门', '35', '2022-04-04', '1998-03-04', '上海', NOW(), NULL);

-- 插入工资数据  
INSERT INTO `salaries` (`emp_id`, `name`, `salary_amount`, `payment_date`, `deductions`, `net_salary`, `gmt_create`) VALUES
(1, '员工A', 9000.00, '2023-06-30', 500.00, 8500.00, NOW()),
(2, '员工B', 4500.00, '2023-07-01', 450.00, 4050.00, NOW()),
(3, '员工C', 5900.00, '2023-06-30', 100.00, 5800.00, NOW()),
(4, '员工D', 8300.00, '2023-07-01', 200.00, 8100.00, NOW()),
(5, '员工D', 8300.00, '2023-06-30', NULL, NULL, NOW());

 

其中，employees表查询结果如下：

图片

 

salaries表查询结果如下：

图片

 

 

 01. 

使用DISTINCT关键字去重

 

DISTINCT关键字是SQL中常用的去重工具。当我们使用它时，后面需明确指定要去重的字段。这样，它将对指定的字段进行去重操作，并返回唯一的值。

 

1. 对单列数据去重

如果我们想要获取"employees"表中不重复的name字段，可以使用以下SQL语句：

SELECT  DISTINCT `name` FROM employees 

 

查询结果如下：

图片

 

对单列使用distinct去除重复值时，会过滤掉多余重复相同的值，只返回唯一的值。

 

 

2. 对多列数据去重

如果需要对多列数据进行去重处理，只需在DISTINCT关键字后依次列出需要去重的字段名，并用英文逗号隔开即可。

 

例如，我们想要对"employees"表中name、position、department和hire_date字段去重，可以使用以下SQL语句。

SELECT DISTINCT `name`,`position`,department ,hire_date FROM employees

 

查询结果如下：

图片

可以看到department的值是有重复的，这是因为DISTINCT其实是对后面所有列名的组合进行去重。也就是name+position+department+hire_date组合成的一行在整张表中都不重复的记录；在这里，因为name+position+department+hire_date有2个相同的数据，则过滤了一行。

 

使用DISTINCT关键字进行去重是相对简单的。然而，需要注意的是，DISTINCT关键字仅对指定的字段进行去重，如果需要返回其他字段的信息，这种方法可能会受到限制。

 

 

 02. 

使用GROUP BY子句去重

 

GROUP BY关键字是另一种常用的去重方法。它可以将相同的值分组，并只返回每组中的一个值。同时，它还可以返回其他字段信息，实现去重的同时提供更多相关信息。以下是GROUP BY子句的2种常见去重方法：

 

1. 对单列数据去重

如果我们想要获取"employees"表中不重复的name字段，可以使用以下SQL语句：

SELECT  `name` FROM employees GROUP BY name

 

查询结果如下：

图片

 

 

2. 对多列数据去重

倘若我们想要对"employees"表中name、position、department和hire_date字段去重，我们尝试使用GROUP BY子句如下：

SELECT `name`, `position`, `department`, `hire_date`   
FROM employees   
GROUP BY `name`

-- 执行结果如下
SQL 错误 [1055] [42000]: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tb_users.employees.position' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with 

 

在SQL 查询时，若是启用了only_full_group_by 规则，那么，当在 GROUP BY 子句中没有列出的字段，又在 SELECT 中出现且没有使用聚合函数，就会导致错误。简单来说，SELECT 中的字段要么是 GROUP BY 里的，要么就得用聚合函数处理，否则查询会失败。

 

正确语法如下：

SELECT `name`, `position`, `department`, `hire_date`   
FROM employees   
GROUP BY `name`, `position`, `department`, `hire_date`;

 

查询结果如下：

图片

 

3. 结合聚合函数

如果我们不仅想对name字段去重，还想获取每个员工的最早出生日期，可以这样写：

SELECT name ,MIN(birth_date)
FROM employees e 
GROUP BY e.name

 

查询结果如下：

图片

这个查询返回了name字段的唯一值和与之相关的birth_date字段的最小值。

 

也就是说，我们可以使用GROUP BY返回分组字段或其他字段的聚合信息。

 

 

 03. 

使用NOT EXISTS子查询去重

 

NOT EXISTS是一种逻辑运算符，用于判断一个子查询是否返回结果。如果子查询没有返回结果，则返回TRUE；否则返回FALSE。我们可以利用这个特性来去除重复的记录。

 

倘若我们想要获取"employees"表中重复名字中第一个出现的员工，可以使用以下SQL语句：

SELECT
 e.emp_id , e.name, e.birth_date
FROM
 employees e
WHERE
 NOT EXISTS (
 SELECT
  1
 FROM
  employees
 WHERE
  name = e.name
  AND emp_id  < e.emp_id 
);

 

这个查询将返回employees表中emp_id, name和birth_date列，且排除其他员工名与当前员工相同，且他的emp_id小于当前员工的emp_id。换句话说，将返回重复名字中emp_id最小的那个员工信息。

 

查询结果如下：

图片

 

 

 04. 

使用LAG和LEAD函数去重

 

在SQL中，LAG和LEAD函数允许我们访问结果集中的前一行和后一行的数据，这在处理时间序列数据或比较当前行与相邻行数据时非常有用。我们可以巧妙地使用这些函数与其他SQL功能(如：GROUP BY、HAVING和DISTINCT) 结合起来实现去重的目的。

 

如果我们想要获取"employees"表中不重复的emp_id、name字段，可以使用以下SQL语句：

SELECT
 DISTINCT emp_id,
 name
FROM
 (
 SELECT
  emp_id,
  name,
  LAG(name, 1, '') OVER (
  order by emp_id ) AS prev_name
 FROM
  employees  
) AS t
WHERE
 prev_name IS NULL
 OR prev_name <> name;

 

这个语句是从employees表中选择唯一的emp_id和name。内部查询使用LAG函数来获取每个emp_id的前一个name（按照emp_id排序），如果前一个name不存在，则默认为''（空字符串）。最后，在外部查询中，我们筛选出prev_name为NULL或者prev_name与当前name不相等的记录。这种方式可以找出名字在员工列表中发生变化的员工的emp_id和name。 

 

查询结果如下：

图片

 

若将上述SQL语句中的LAG函数替换为LEAD函数后，我们可以访问结果集中的后一行数据，而不是前一行数据。因此，执行结果将与原始SQL语句相反。

图片

 

 

 05. 

使用IN去重

 

使用"IN"操作可以找到一组数据中不重复的特征，然后基于这些特征来获取数据。这样，我们能够更精确地筛选出具有特定属性的数据，确保数据的唯一性。

 

倘若我们想要获取"employees"表中具有相同名字的最大"emp_id"的员工信息，可以使用以下SQL语句：

select
 e.emp_id ,
 e.name,
 e.birth_date
from
 employees e
where
 emp_id in (
 select
  max(emp_id)
 from
  employees
 group by
  name
);

 

查询结果如下：

图片

 

可以看到返回了emp_id值为5的员工信息，而不是emp_id为4的员工信息。

 

然而，这种方法的可行性取决于表中是否存在一个唯一标识每条记录的字段，也就是，一个数据不重复的字段，例如employees表中的emp_id字段。若表中不存在此类字段，该方法则无法适用。

 

 

 06. 

使用UNION去重

 

UNION 是 SQL 中用于合并两个或多个 SELECT 语句的结果集的操作符。当使用 UNION 时，结果集会自动去重，即重复的行只会出现一次。这与INNER JOIN类似，都是求并集，但INNER JOIN是根据两个或多个表的共同列来合并数据，只返回匹配的行。

 

倘如，我们想要获取"employees"表中不重复的name字段，可以使用以下SQL语句：

SELECT `name` FROM employees  
UNION  
SELECT `name` FROM salaries;

 

这条语句会从 "employees" 表和 "salaries" 表中选取 "name" 字段，并通过 UNION 操作符合并结果集，确保结果中的 "name" 值是唯一的。

 

查询结果如下：

图片

 

标签：name,employees,SQL,-----------------------------------------------,emp,mysql,NUL
From： https://www.cnblogs.com/cherishthepresent/p/17915971.html

相关文章

  • KingIOServer连接S7-200 SMART PLC
    硬件：西门子200SMARTPLCSR30软件环境：1，PLC编程软件Step7Micro/WINSMART2，组态王KingIOServer具体步骤：1,打开KingIOServer工程设计器，然后新建应用/工程，输入应用名称，然后确定2，左侧项目树，设备-右键 3，左侧项目树，变量-右键，新建变量4，编译，运行 5，在弹出的窗口中，启动......
  • 最高法--票据权利时效均系可中断时间，对票据时效起诉后再撤诉也应当视为中断。
    1.（２０２２）最高法民申727号  陕西能源凉水井矿业有限责任公司、陕西华山创业有限公司等票据追索权纠纷民事申请再审审查民事裁定书申请人主张：凉水井公司、华山创业公司、陕西能源公司依据《中华人民共和国民事诉讼法》第二百零七条规定申请再审，请求：1.裁定中止本案原审判决执行；2.撤销......
  • 15.高阶定位-CSS 定位与原生定位
    目录原生定位cssselector定位原生定位官网地址元素属性定位组合定位#ID定位driver.find_element_by_android_uiautomator('\newUiSelector().resourceId("<element-ID>")')#组合定位driver.find_element_by_android_uiautomator('\newUiS......
  • iTOP-RK3568开发板实时系统编译,Preemption系统/Xenomai系统编译,获取Linux源码包
    1获取Linux源码包编译环境说明：本手册使用的是迅为提供的编译环境ubuntu20.04，在网盘资料“iTOP-3568开发板\03_【iTOP-RK3568开发板】指南教程\05_NPU开发配套资料\03_RKNN_Toolkit2环境搭建\01课程用到的资料\01_初始Ubuntu20虚拟机”路径下。这里的ubuntu20默认开发......
  • 无涯教程-Go - 作用域
    任何编程中的作用域是程序中可以存在定义的变量并且不能访问该变量的区域，在三个地方可以使用Go编程语言声明变量-在函数或块内(local变量)所有函数之外(global变量)在函数参数中(formal参数)让我们找出什么是local和global变量以及什么是formal参数。局部变量在......
  • 解决MyBatis-Plus 更新字段为null 不生效
    1.异常说明：mapper.updateById()时，set为null未生效，其他字段更新periodRecordOriginal.setSettleTime(null);periodRecordOriginal.setActualSettleTime(null);periodRecordOriginal.setSettleStatus(0);i......
  • js实现el-select选中的文本，一键复制
    <divclass="invite-buttom">请选择要复制的网站：<el-selectv-model="webValue"placeholder="请选择要复制的网站"><el-optionv-for="iteminformUrl.webInviteUrl"......
  • mysql union all、union、join
    union和join是需要联合多张表时常见的关联词，join:两张表做交连后里面条件相同的部分记录产生一个记录集，union:union是产生的两个记录集(字段要一样的)并在一起，成为一个新的记录集。二者区别：join和union的主要区别就一条，join是将拼接内容变成一行（左右拼接），根据共同字段将数据拼接成......
  • 【2023-12-19】连岳摘抄
    23:59我以为人们在每一个时期都可以过有趣而且有用的生活。我们应该不虚度一生，应该能够说，“我已经做了我能做的事”，人们只能要求我们如此，而且只有这样我们才能有一点欢乐。                                ......
  • 十六、组件-通用属性-位置设置
    位置设置设置组件对齐方式、布局方向和显示位置。align：设置元素内容在元素绘制区域内的对齐方式。direction：设置元素水平方向的布局。position:基于父容器的定位markAnchor：相对于自身的定位，x正数代表左移，负数代表右移；y正数代表上移，负数代表下移。offset:相对于自身的定......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99