Database deletes duplicate data
When there is a large amount of data in the database, it is very annoying to delete duplicate elements, and sometimes I can't write them correctly. I would like to sort out the sql that deletes the duplicate data. The data table is the material for watching mysql teaching videos before, but I can't find it anywhere. Please leave a message if the original author sees it. -- create data tables CREATE TABLE IF NOT EXISTS tdb_goods (goods_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, goods_name VARCHAR (150) NOT NULL, goods_cate VARCHAR (40) NOT NULL, brand_name VARCHAR (40) NOT NULL, goods_price DECIMAL (15Ling 3) UNSIGNED NOT NULL DEFAULT 0, is_show BOOLEAN NOT NULL DEFAULT 1, is_saleoff BOOLEAN NOT NULL DEFAULT 0) -- write record INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('R510VC 15.6in Notebook', 'Notebook', 'Asustek', '3399 recording dome defaults) INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('Y400N 14.0inch notebook', 'notebook', 'Lenovo', '4899mm notebook DEFAULT, default); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' G150TH 15.6in game book', 'game book', 'Raytheon', '8499' recording device design default) INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('X550CC 15.6in Notebook', 'Notebook', 'Asustek', '27995th ultrabook'); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('X240 (20ALA0EYCD) 12.5in ultrabook', 'Ultrabook', 'Lenovo', '4999' INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('U330P 13.3-inch ultrabook', 'ultrabook', 'Lenovo', '4299 ultrabook'); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('SVP13226SCB 13.3-inch touch ultrabook', 'ultrabook', 'Sony', '7999 ultrabook') INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('iPad mini MD531CH/A 7.9in tablet', 'tablet', 'Apple', '1998 calendar defaults) INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('iPad Air MD788CH/A 9.7in tablet (16G WiFi version)', 'tablet', 'Apple', '3388') INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('iPad mini ME279CH/An equipped with Retina display 7.9in tablet (16G WiFi version)', 'tablet', 'Apple', '2788' INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('IdeaCentre C340 20-inch all-in-one computer', 'desktop computer', 'Lenovo', '3499 desktop computer'); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('Vostro 3800-R1206 desktop computer', 'desktop computer', 'Dell', '2899 desktop computer', '2899 desktop computer') INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('iMac ME086CH/A 21. 5-inch all-in-one computer', 'desktops', 'Apple', '9188 calendar defaults) INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('AT7-7414LP desktop computer (i5-3450 quad-core 4G 500G 2G DVD mouse Linux)', 'desktop computer', 'Acer', '3699 memory defaults INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('Z220SFF F4F06PA workstation', 'server / workstation', 'Hewlett-Packard', '4288'); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('PowerEdge × × × 10 server', 'server / workstation', 'Dell', '5388') INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('Mac Pro MD878CH/A professional desktop computer', 'server / workstation', 'Apple', '28888 desktop, DEFAULT,DEFAULT) INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('HMZ-T3W head-mounted display device', 'Notebook Accessories', 'Sony', '6999DFAULTMagi default); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' Business backpack', 'Notebook Accessories', 'Sony','99 'DFULTMAG defaults) INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('X3250 M4 Rack Server 2583i14' Server / Workstation', 'IBM','6888',DEFAULT,DEFAULT) INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('Xuanlong Elite Edition Notebook Radiator', 'Notebook Accessories', 'Kyushu Fengshen',''DEFAULT,DEFAULT) INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('HMZ-T3W head-mounted display device', 'Notebook Accessories', 'Sony', '6999DFAULTMagi default); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' Business backpack', 'Notebook Accessories', 'Sony','99 'DFULTMAG defaults) -- insert duplicate data INSERT tdb_goods (goods_name,cate_id,brand_id) SELECT goods_name,cate_id,brand_id,goods_price FROM tdb_goods WHERE goods_id IN (19LJ20)-- query duplicate data SELECT goods_id,goods_name,goods_price FROM tdb_goods GROUP BY goods_name,goods_price HAVING count (goods_name) > = 2-- query the id to be deleted and query select t1.goods_id from tdb_goods as T1 left JOIN (SELECT goods_id) Goods_name,goods_price FROM tdb_goods GROUP BY goods_name,goods_price HAVING count (goods_name) > = 2) T2 on t1.goods_name = t2.goods_name where t1.goods_id > t2.goods_id-- perform deletion of delete T1 from tdb_goods as T1 left JOIN (SELECT goods_id,goods_name,goods_price FROM tdb_goods GROUP BY goods_name,goods_price HAVING count (goods_name) > = 2) T2 on t1.goods_name = t2.goods_name where t1.goods_id > t2.goods_id
