SQL 去除表内重复数据留下字段值全的数据（单条数据优先保留多字段有值到多字段部分有值到多字段无值）的情况

本文链接：https://blog.csdn.net/vransy/article/details/120756754

SQL想法

先把没有地址电话和没有银行账户的数据插入到临时表
把有 DZDH(地址电话)和没有 YHZH(银行账户)的 KHSH(客户税号)和USER_SH(用户税号)提取出来在临时表中删掉临时表中的数据取title 中最新的数据插入到临时表中
把没有 DZDH(地址电话)和有 YHZH(银行账户)的 KHSH(客户税号)和USER_SH(用户税号)提取出来在临时表中删掉临时表中的数据取title 中最新的数据插入到临时表中
把有 DZDH(地址电话)和有 YHZH(银行账户)的 KHSH(客户税号)和USER_SH(用户税号)提取出来在临时表中删掉临时表中的数据取title 中最新的数据插入到临时表中

CREATE TABLE temp_20211014 
SELECT 
ID, USER_SH, KHSH,KHMC,DZDH,YHZH,KJJ,ISDELETE 
FROM title_info  
WHERE DZDH = '' AND YHZH = ''
;

-- 去掉表内已有重复
DELETE FROM temp_20211014 WHERE 
EXISTS(
			SELECT * FROM title_info 
			WHERE 
			temp_20211014.USER_SH = title_info.USER_SH
			AND
			temp_20211014.KHSH = title_info.KHSH
			AND 
			DZDH != '' AND YHZH = ''
			)
			
insert into temp_20211014
SELECT 
ID, USER_SH, KHSH,KHMC,DZDH,YHZH,KJJ,ISDELETE 
FROM title_info  
WHERE DZDH !='' AND YHZH = '';

-- 删除左空
DELETE FROM temp_20211014 WHERE 
EXISTS(
			SELECT * FROM title_info 
			WHERE 
			temp_20211014.USER_SH = title_info.USER_SH
			AND
			temp_20211014.KHSH = title_info.KHSH
			AND 
			DZDH = '' AND YHZH != ''
			)

insert into temp_20211014
SELECT 
ID, USER_SH, KHSH,KHMC,DZDH,YHZH,KJJ,ISDELETE 
FROM title_info  
WHERE DZDH = '' AND YHZH != '';


-- 删除全有
DELETE FROM temp_20211014 WHERE 
EXISTS(
			SELECT * FROM title_info 
			WHERE 
			temp_20211014.USER_SH = title_info.USER_SH
			AND
			temp_20211014.KHSH = title_info.KHSH
			AND 
			DZDH != '' AND YHZH != ''
			)

insert into temp_20211014
SELECT 
ID, USER_SH, KHSH,KHMC,DZDH,YHZH,KJJ,ISDELETE 
FROM title_info  
WHERE DZDH != '' AND YHZH != '';



-- ****格式化 title_info 表****--
DROP TABLE IF EXISTS `title_info`;

CREATE TABLE `title_info` (
  `ID` varchar(40) NOT NULL,
  `USER_SH` varchar(50) DEFAULT NULL,
  `KHSH` varchar(50) DEFAULT NULL,
  `KHMC` varchar(100) DEFAULT NULL COMMENT '名称',
  `DZDH` varchar(200) DEFAULT NULL COMMENT '地址电话',
  `YHZH` varchar(200) DEFAULT NULL COMMENT '银行账号',
  `KJJ` varchar(10) DEFAULT NULL COMMENT '快捷键',
  `ISDELETE` varchar(1) DEFAULT '0' COMMENT '是否作废，0为未作废，1为已作废',
  PRIMARY KEY (`ID`) USING BTREE,
  KEY `ID` (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

insert into title_info 
SELECT 
ID, USER_SH, KHSH,KHMC,DZDH,YHZH,KJJ,ISDELETE 
FROM temp_20211014 

DROP TABLE IF EXISTS `temp_20211014`;