Mysql無主鍵重復(fù)數(shù)據(jù)篩選并刪除解決思路

有個同事有一張表，該表無主鍵，但是由于死鎖原因，產(chǎn)生部分重復(fù)數(shù)據(jù)，該部分數(shù)據(jù)重復(fù)會影響報表生成，故而需要對重復(fù)數(shù)據(jù)進行篩選并且剔除，建表腳本：

DROP TABLE IF EXISTS `T_ECFN_LIMIT_INIT`;
CREATE TABLE `T_ECFN_LIMIT_INIT` (
  `PLAT_DATE` char(8) NOT NULL,
  `MONTH` int(11) NOT NULL,
  `BILL_NO` varchar(64) NOT NULL,
  `EN_CODE` varchar(32) NOT NULL,
  `EN_NAME` varchar(255) DEFAULT NULL,
  `AREA_NO` varchar(20) NOT NULL,
  `PLAN_ID` varchar(64) DEFAULT '',
  `AMT` decimal(16,2) NOT NULL DEFAULT '0.00',
  `AWT_DATE` varchar(20) DEFAULT NULL,
  `ITEM_NO` varchar(32) NOT NULL,
  `PRJ_CODE` varchar(32) DEFAULT NULL,
  `DEPT_NO` varchar(32) DEFAULT NULL,
  `BUD_TYPE` varchar(32) DEFAULT NULL,
  `GUIDE_LINE` varchar(32) DEFAULT NULL,
  `FUND_TYPE` varchar(32) DEFAULT NULL,
  `REMAK` varchar(255) DEFAULT NULL,
  `NOTE` varchar(255) DEFAULT NULL,
  `BRCH_NO` varchar(20) DEFAULT NULL,
  `TELLER_NO` varchar(20) DEFAULT NULL,
  `REVS` varchar(255) DEFAULT NULL,
  `DAC` varchar(32) DEFAULT NULL,
  UNIQUE KEY `index_unique_limit_init_1` (`PLAT_DATE`,`BILL_NO`,`AREA_NO`,`PLAN_ID`),
  KEY `index_ecfn_limit_init_1` (`EN_CODE`,`AREA_NO`,`ITEM_NO`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
字段說明.png

現(xiàn)在插入幾條測試數(shù)據(jù)，最后兩條為重復(fù)數(shù)據(jù)：

INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000012410500005105201900258', '182001', '云南省人民檢察院', '530000', '989844', '1400000.00', '20190709', '2040499', '201600024491', null, '003', null, null, '測試', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000018710500005105201900258', '182001', '云南省人民檢察院', '530000', '989842', '50000.00', '20190709', '2040499', '201700036719', null, '003', null, null, '測試', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000025610500005105201900258', '182001', '云南省人民檢察院', '530000', '989841', '100000.00', '20190709', '2049901', '00226782', null, '003', null, null, '測試', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000028910500005105201900258', '182001', '云南省人民檢察院', '530000', '989843', '392800.00', '20190709', '2040499', '201600024491', null, '003', null, null, '測試', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000046010500005105201900259', '182001', '云南省人民檢察院', '530000', '989845', '200000.00', '20190709', '2070702', '201800054640', null, '001', null, null, '測試', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000055310500005105201900260', '101001001', '云南省發(fā)展和改革委員會(機關(guān)合并)', '530000', '989852', '30000.00', '20190709', '2010499', '201700036719', null, '003', null, null, '測試', null, '530615438', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000056910500005105201900260', '101001001', '云南省發(fā)展和改革委員會(機關(guān)合并)', '530000', '989855', '264280.00', '20190709', '2010408', '201600016316', null, '003', null, null, '測試', null, '530615438', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000065510500005105201900261', '101001001', '云南省發(fā)展和改革委員會(機關(guān)合并)', '530000', '989849', '332000.00', '20190709', '2010499', '201900057666', null, '001', null, null, '測試', null, '530615438', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000067110500005105201900261', '101001001', '云南省發(fā)展和改革委員會(機關(guān)合并)', '530000', '989847', '497500.00', '20190709', '2130599', '201700038747', null, '001', null, null, '測試', null, '530615438', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000071410500005105201900261', '101001001', '云南省發(fā)展和改革委員會(機關(guān)合并)', '530000', '989853', '80000.00', '20190709', '2010407', '201900057651', null, '001', null, null, '測試', null, '530615438', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000072810500005105201900261', '101001001', '云南省發(fā)展和改革委員會(機關(guān)合并)', '530000', '989854', '30000.00', '20190709', '2110199', '201900057663', null, '001', null, null, '測試', null, '530615438', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000074010500005105201900261', '101001001', '云南省發(fā)展和改革委員會(機關(guān)合并)', '530000', '989848', '280000.00', '20190709', '2010405', '201900057650', null, '001', null, null, '測試', null, '530615438', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000075910500005105201900261', '101001001', '云南省發(fā)展和改革委員會(機關(guān)合并)', '530000', '989846', '90000.00', '20190709', '2010404', '201900057662', null, '001', null, null, '測試', null, '530615438', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000078310500005105201900261', '101001001', '云南省發(fā)展和改革委員會(機關(guān)合并)', '530000', '989851', '3960000.00', '20190709', '2011099', '201900057652', null, '001', null, null, '測試', null, '530615438', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000079510500005105201900261', '101001001', '云南省發(fā)展和改革委員會(機關(guān)合并)', '530000', '989850', '117000.00', '20190709', '2010401', '201800041817', null, '001', null, null, '測試', null, '530615438', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000084010500005105201900268', '182001', '云南省人民檢察院', '530000', '989882', '272815.00', '20190709', '2210201', '201800041398', null, '001', null, null, '1-4月基本支出', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000084910500005105201900268', '182001', '云南省人民檢察院', '530000', '989879', '405000.00', '20190709', '2040401', '201800041398', null, '001', null, null, '1-4月基本支出', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000086310500005105201900268', '182001', '云南省人民檢察院', '530000', '989884', '3035.00', '20190709', '2040450', '201800041818', null, '001', null, null, '1-4月基本支出', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000087510500005105201900268', '182001', '云南省人民檢察院', '530000', '989878', '9190.00', '20190709', '2040401', '201800041398', null, '001', null, null, '1-4月基本支出', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000089010500005105201900268', '182001', '云南省人民檢察院', '530000', '989876', '57920.00', '20190709', '2040401', '201800041820', null, '001', null, null, '1-4月基本支出', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000090610500005105201900268', '182001', '云南省人民檢察院', '530000', '989877', '5210.00', '20190709', '2080505', '201800041398', null, '001', null, null, '1-4月基本支出', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160000091910500005105201900268', '182001', '云南省人民檢察院', '530000', '989881', '575725.00', '20190709', '2040401', '201800041818', null, '001', null, null, '1-4月基本支出', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160100001810500005105201900268', '182001', '云南省人民檢察院', '530000', '989883', '371380.00', '20190709', '2080505', '201800041398', null, '001', null, null, '1-4月基本支出', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160100003710500005105201900268', '182001', '云南省人民檢察院', '530000', '989880', '605.00', '20190709', '2040450', '201800041398', null, '001', null, null, '1-4月基本支出', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160100005310500005105201900268', '182001', '云南省人民檢察院', '530000', '989885', '11605.00', '20190709', '2080506', '201800041398', null, '001', null, null, '1-4月基本支出', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160100006610500005105201900268', '182001', '云南省人民檢察院', '530000', '989845', '90000.00', '20190709', '2070702', '201800054640', null, '001', null, null, '建行測試', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160100012010500005105201900269', '182001', '云南省人民檢察院', '530000', '989844', '630000.00', '20190709', '2040499', '201600024491', null, '003', null, null, '建行測試', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160100013510500005105201900269', '182001', '云南省人民檢察院', '530000', '989842', '22500.00', '20190709', '2040499', '201700036719', null, '003', null, null, '建行測試', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160100015510500005105201900269', '182001', '云南省人民檢察院', '530000', '989841', '45000.00', '20190709', '2049901', '00226782', null, '003', null, null, '建行測試', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160100017210500005105201900269', '182001', '云南省人民檢察院', '530000', '989843', '176760.00', '20190709', '2040499', '201600024491', null, '003', null, null, '建行測試', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160100020510500005105201900295', '101001001', '云南省發(fā)展和改革委員會(機關(guān)合并)', '530000', '989927', '3000.00', '20190709', '2010401', '201800041820', null, '001', null, null, '', null, '530615438', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160100022010500005105201900295', '101001001', '云南省發(fā)展和改革委員會(機關(guān)合并)', '530000', '989928', '2800.00', '20190709', '2010450', '201800041812', null, '001', null, null, '', null, '530615438', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160100023410500005105201900295', '101001001', '云南省發(fā)展和改革委員會(機關(guān)合并)', '530000', '989929', '11200.00', '20190709', '2010401', '201800041398', null, '001', null, null, '', null, '530615438', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160100027510500005105201900301', '182001', '云南省人民檢察院', '530000', '989879', '76950.00', '20190709', '2040401', '201800041398', null, '001', null, null, '0322額度測試', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160100029010500005105201900301', '182001', '云南省人民檢察院', '530000', '989827', '1239.00', '20190709', '2080501', '201800041818', null, '001', null, null, '0322額度測試', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160100030310500005105201900301', '182001', '云南省人民檢察院', '530000', '989884', '576.65', '20190709', '2040450', '201800041818', null, '001', null, null, '0322額度測試', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160100031910500005105201900301', '182001', '云南省人民檢察院', '530000', '989878', '1746.10', '20190709', '2040401', '201800041398', null, '001', null, null, '0322額度測試', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160100033510500005105201900301', '182001', '云南省人民檢察院', '530000', '989876', '11004.80', '20190709', '2040401', '201800041820', null, '001', null, null, '0322額度測試', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160100035010500005105201900301', '182001', '云南省人民檢察院', '530000', '989877', '989.90', '20190709', '2080505', '201800041398', null, '001', null, null, '0322額度測試', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160100036510500005105201900301', '182001', '云南省人民檢察院', '530000', '989881', '109387.75', '20190709', '2040401', '201800041818', null, '001', null, null, '0322額度測試', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160100038010500005105201900301', '182001', '云南省人民檢察院', '530000', '989883', '70562.20', '20190709', '2080505', '201800041398', null, '001', null, null, '0322額度測試', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '3', '19070910160100041410500005105201900308', '182001', '云南省人民檢察院', '530000', '989827', '999.00', '20190709', '2080501', '201800041818', null, '001', null, null, '', null, '530615536', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '5', '19070910160100044610500005105201900312', '101001001', '云南省發(fā)展和改革委員會(機關(guān)合并)', '530000', '989953', '1000.00', '20190709', '2010401', '201800041812', null, '001', null, null, '0506測試', null, '530615438', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '5', '19070910160100046010500005105201900312', '101001001', '云南省發(fā)展和改革委員會(機關(guān)合并)', '530000', '989954', '1000.00', '20190709', '2080501', '201800041818', null, '001', null, null, '0506測試', null, '530615438', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '5', '19070910160100047510500005105201900312', '101001001', '云南省發(fā)展和改革委員會(機關(guān)合并)', '530000', '989919', '1000.00', '20190709', '2080506', '201800041398', null, '001', null, null, '0506測試', null, '530615438', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '5', '19070910160100049010500005105201900312', '182002001', '云南省人民檢察院昆明鐵路運輸檢察分院', '530000', '989991', '1000.00', '20190709', '2210201', '201800041398', null, '001', null, null, '0506測試', null, '530945040', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '5', '19070910160100050210500005105201900312', '101001001', '云南省發(fā)展和改革委員會(機關(guān)合并)', '530000', '989918', '1000.00', '20190709', '2080506', '201800041398', null, '001', null, null, '0506測試', null, '530615438', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '5', '19070910160100052010500005105201900312', '182002001', '云南省人民檢察院昆明鐵路運輸檢察分院', '530000', '989992', '1000.00', '20190709', '2040401', '201800041398', null, '001', null, null, '0506測試', null, '530945040', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '5', '19070910160100053210500005105201900312', '182002001', '云南省人民檢察院昆明鐵路運輸檢察分院', '530000', '989993', '1000.00', '20190709', '2080501', '201800041818', null, '001', null, null, '0506測試', null, '530945040', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '5', '19070910160100057210500005105201900313', '182001', '云南省人民檢察院', '530000', '989885', '22222.00', '20190709', '2080506', '201800041398', null, '001', null, null, '0506測試', null, '530615536', null, null, null);



INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '5', '19070910160111153210500005105201900312', '182002001', '云南省人民檢察院昆明鐵路運輸檢察分院', '530000', '989993', '1000.00', '20190709', '2080501', '201800041818', null, '001', null, null, '重復(fù)', null, '530945040', null, null, null);
INSERT INTO `T_ECFN_LIMIT_INIT` VALUES ('20190709', '5', '19070910160122257210500005105201900313', '182001', '云南省人民檢察院', '530000', '989885', '22222.00', '20190709', '2080506', '201800041398', null, '001', null, null, '去重復(fù)', null, '530615536', null, null, null);

最后兩條數(shù)據(jù)是手工添加的重復(fù)數(shù)據(jù)，其BILL_NO分別對應(yīng)：
19070910160111153210500005105201900312
19070910160122257210500005105201900313

我們現(xiàn)在需要針對重復(fù)數(shù)據(jù)進行篩選，那么，我們首先需要考慮的一個問題就是：什么樣的數(shù)據(jù)算是重復(fù)數(shù)據(jù)？

想清楚這一個問題，我們才可以有針對性的對數(shù)據(jù)進行group by處理，所以針對該表：同一個財政級別（AREA_NO）下同一個月份（MONTH）的同一個預(yù)算單位（EN_CODE）的同一個科目（ITEM_NO）的相同金額（AMT），如果PLAN_ID是一致的，那么該數(shù)據(jù)就是重復(fù)的！

所以，確定重復(fù)數(shù)據(jù)條件，我們就可以通過COUNT函數(shù)確定重復(fù)數(shù)據(jù)。針對該表，BILL_NO可以區(qū)分重復(fù)數(shù)據(jù)，有時間戳加上憑證號生成，所以我們可以先查出全部重復(fù)數(shù)據(jù)：

SELECT * FROM T_ECFN_LIMIT_INIT li WHERE (li.AREA_NO,li.PLAN_ID,li.AMT,li.EN_CODE,li.ITEM_NO,li.MONTH) 
IN(SELECT AREA_NO,PLAN_ID,AMT,EN_CODE,ITEM_NO,MONTH FROM T_ECFN_LIMIT_INIT GROUP BY AREA_NO,PLAN_ID,AMT,EN_CODE,ITEM_NO,MONTH
HAVING COUNT(*)>1)
全部重復(fù)數(shù)據(jù).png

我們要查目標重復(fù)數(shù)據(jù)，可以選出最大BILL_NO，語句如下：

SELECT * FROM T_ECFN_LIMIT_INIT  WHERE BILL_NO IN(
    SELECT MAX(BILL_NO) FROM T_ECFN_LIMIT_INIT 
        GROUP BY AREA_NO,PLAN_ID,AMT,EN_CODE,ITEM_NO,MONTH HAVING COUNT(*)>1)

由于BILL_NO確定唯一，所以刪除數(shù)據(jù)就比較簡單啦！

知識點回顧

一、查找重復(fù)記錄
1、查找全部重復(fù)記錄

//單字段重復(fù)
Select * From Table Where 重復(fù)字段 In (Select 重復(fù)字段 From Table Group By 重復(fù)字段 Having Count(*)>1)

//多字段重復(fù)
Select * From Table Where (重復(fù)字段1,重復(fù)字段2,重復(fù)字段3) In (Select 重復(fù)字段1,重復(fù)字段2,重復(fù)字段3 From Table Group By 重復(fù)字段1,重復(fù)字段2,重復(fù)字段3 Having Count(*)>1)

2、過濾重復(fù)記錄(只顯示一條)

Select * From Table Where ID In (Select Max(ID) From Table Group By 重復(fù)字段 HAVING COUNT(*)>1)
//顯示ID最大一條記錄

二、刪除重復(fù)記錄
1、刪除全部重復(fù)記錄（慎用）

Delete Table Where 重復(fù)字段 In (Select 重復(fù)字段 From table Group By 重復(fù)字段 Having Count(*)>1)

2、保留一條（推薦）

Delete from Table Where ID Not In (Select Max(ID) From Table Group By 重復(fù)字段)

三、舉例
1、查找表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個字段（peopleId）來判斷

select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2、刪除表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個字段（peopleId）來判斷，只留有rowid最小的記錄
delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)
3、查找表中多余的重復(fù)記錄（多個字段）
select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count() > 1)
4、刪除表中多余的重復(fù)記錄（多個字段），只留有rowid最小的記錄
delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count() > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count()>1)
5、查找表中多余的重復(fù)記錄（多個字段），不包含rowid最小的記錄
select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count() > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
四、補充
有兩個以上的重復(fù)記錄，一是完全重復(fù)的記錄，也即所有字段均重復(fù)的記錄，二是部分關(guān)鍵字段重復(fù)的記錄，比如Name字段重復(fù)，而其他字段不一定重復(fù)或都重復(fù)可以忽略。
1、對于第一種重復(fù)，比較容易解決，使用

select distinct * from tableName
就可以得到無重復(fù)記錄的結(jié)果集。
如果該表需要刪除重復(fù)的記錄（重復(fù)記錄保留1條），可以按以下方法刪除
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp
發(fā)生這種重復(fù)的原因是表設(shè)計不周產(chǎn)生的，增加唯一索引列即可解決。
2、這類重復(fù)問題通常要求保留重復(fù)記錄中的第一條記錄，操作方法如下
假設(shè)有重復(fù)的字段為Name,Address，要求得到這兩個字段唯一的結(jié)果集
select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容