จะลบแถวที่ซ้ำกันออกจากตาราง SQL Server ได้อย่างไร?

เมื่อออกแบบออบเจ็กต์ใน SQL Server เราต้องปฏิบัติตามแนวทางปฏิบัติที่ดีที่สุดบางประการ ตัวอย่างเช่นตารางควรมีคีย์หลักคอลัมน์ข้อมูลประจำตัวดัชนีที่คลัสเตอร์และไม่รวมกลุ่มความสมบูรณ์ของข้อมูลและข้อ จำกัด ด้านประสิทธิภาพ ตาราง SQL Server ไม่ควรมีแถวที่ซ้ำกันตามแนวทางปฏิบัติที่ดีที่สุดในการออกแบบฐานข้อมูล อย่างไรก็ตามบางครั้งเราจำเป็นต้องจัดการกับฐานข้อมูลที่ไม่ปฏิบัติตามกฎเหล่านี้หรือในกรณีที่มีข้อยกเว้นที่เป็นไปได้เมื่อกฎเหล่านี้ถูกข้ามโดยเจตนา แม้ว่าเราจะปฏิบัติตามแนวทางปฏิบัติที่ดีที่สุด แต่เราอาจประสบปัญหาเช่นแถวที่ซ้ำกัน

ตัวอย่างเช่นเราสามารถรับข้อมูลประเภทนี้ได้ในขณะที่นำเข้าตารางกลางและเราต้องการลบแถวที่ซ้ำซ้อนก่อนที่จะเพิ่มลงในตารางการใช้งานจริง ยิ่งไปกว่านั้นเราไม่ควรทิ้งผู้มีโอกาสเป็นลูกค้าในการทำซ้ำแถวเนื่องจากข้อมูลที่ซ้ำกันทำให้สามารถจัดการคำขอได้หลายรายการผลการรายงานที่ไม่ถูกต้องและอื่น ๆ อย่างไรก็ตามหากเรามีแถวที่ซ้ำกันในคอลัมน์แล้วเราจำเป็นต้องปฏิบัติตามวิธีการเฉพาะเพื่อล้างข้อมูลที่ซ้ำกัน ลองดูวิธีการบางอย่างในบทความนี้เพื่อลบการทำซ้ำข้อมูล

จะลบแถวที่ซ้ำกันออกจากตาราง SQL Server ได้อย่างไร?

มีหลายวิธีใน SQL Server ในการจัดการเร็กคอร์ดที่ซ้ำกันในตารางตามสถานการณ์เฉพาะเช่น:

การลบแถวที่ซ้ำกันออกจากตารางดัชนี SQL Server ที่ไม่ซ้ำกัน

คุณสามารถใช้ดัชนีเพื่อจำแนกข้อมูลที่ซ้ำกันในตารางดัชนีที่ไม่ซ้ำกันจากนั้นลบระเบียนที่ซ้ำกัน อันดับแรกเราต้อง สร้างฐานข้อมูล ชื่อ“ test_database” จากนั้นสร้างตาราง“ พนักงาน” ที่มีดัชนีเฉพาะโดยใช้รหัสที่ระบุด้านล่าง

ใช้ต้นแบบ GO สร้างฐานข้อมูล test_database GO ใช้ [test_database] ไปสร้างพนักงานตาราง ([ID] INT NOT NULL IDENTITY (1,1), [Dep_ID] INT, [Name] varchar (200), [email] varchar (250) NULL , [เมือง] varchar (250) NULL, [ที่อยู่] varchar (500) NULL CONSTRAINT Primary_Key_ID คีย์หลัก (ID))

ผลลัพธ์จะเป็นดังนี้

ตอนนี้ใส่ข้อมูลลงในตาราง เราจะแทรกแถวที่ซ้ำกันด้วย “ Dep_ID” 003,005 และ 006 เป็นแถวที่ซ้ำกันซึ่งมีข้อมูลคล้ายกันในทุกช่องยกเว้นคอลัมน์ข้อมูลประจำตัวที่มีดัชนีคีย์ที่ไม่ซ้ำกัน ดำเนินการตามรหัสที่ระบุด้านล่าง

ใช้ [test_database] GO INSERT INTO Employee (Dep_ID, ชื่อ, อีเมล, เมือง, ที่อยู่) VALUES (001, 'Aaaronboy Gutierrez', '[email protected]', 'HILLSBORO', '5840 Ne Cornell Rd Hillsboro หรือ 97124 '), (002,' Aabdi Maghsoudi ',' [email protected] ',' BRENTWOOD ',' 987400 Nebraska Medical Center Omaha Ne 681987400 '), (003,' Aabharana, Sahni ',' abharana.sahni@gmail com ',' HYATTSVILLE ',' 2 Barlo Circle Suite A Dillsburg Pa 170191 '), (003,' Aabharana, Sahni ',' [email protected] ',' HYATTSVILLE ',' 2 Barlo Circle Suite A Dillsburg Pa 170191 '), (004,' Aabish Mughal ',' [email protected] ',' OMAHA ',' 2975 Crouse Lane Burlington Nc 272150000 '), (005,' Aabram Howell ','[email protected] ',' DILLSBURG ',' 868 York Ave Atlanta Ga 303102750 '), (005,' Aabram Howell ',' [email protected] ',' DILLSBURG ',' 868 York Ave Atlanta Ga 303102750 '), (006 , 'Humbaerto Acevedo', '[email protected]', 'SAINT PAUL', '895 E 7th St Saint Paul Mn 551063852'), (006, 'Humbaerto Acevedo', 'humbaerto.ac [email protected] ',' SAINT PAUL ',' 895 E 7th St Saint Paul Mn 551063852 '), (007,' Pilar Ackaerman ',' [email protected] ',' ATLANTA ',' 5813 Eastern Ave ไฮแอทส์วิลล์ Md 207822201 '); เลือก * จากพนักงาน

ผลลัพธ์จะเป็นดังนี้

ตอนนี้หาแถวในตารางโดยเรียกใช้รหัสต่อไปนี้ ฟังก์ชัน count (*) จะนับจำนวนแถว

SELECT Dep_ID, ชื่อ, อีเมล, เมือง, ที่อยู่, COUNT (*) AS duplicate_rows_count จากกลุ่มพนักงาน BY Dep_ID, ชื่อ, อีเมล, เมือง, ที่อยู่

ผลลัพธ์จะเป็นดังนี้ แถวที่ (3, 4), (6, 7), (8, 9) ที่ไฮไลต์ในกล่องสีแดงเป็นแถวที่ซ้ำกัน

งานของเราคือการบังคับใช้ความเป็นเอกลักษณ์โดยการลบรายการที่ซ้ำกันสำหรับคอลัมน์ที่ซ้ำกัน การลบค่าที่ซ้ำกันออกจากตารางด้วยดัชนีเฉพาะนั้นง่ายกว่าการลบแถวออกจากตารางโดยไม่มี ด้านล่างนี้เป็นสองวิธีในการบรรลุเป้าหมายนี้ วิธีแรกให้คุณทำซ้ำแถวจากตารางโดยใช้ฟังก์ชัน“ row_number ()” ในขณะที่วิธีที่สองใช้ฟังก์ชัน“ NOT IN” สองวิธีนี้มีต้นทุนของตัวเองซึ่งจะกล่าวถึงในภายหลัง

วิธีที่ 1: การเลือกระเบียนที่ซ้ำกันโดยใช้ฟังก์ชัน“ ROW_NUMBER ()”

เลือก * จาก (SELECT Dep_ID, Name, email, city, address, ROW_NUMBER () OVER (PARTITION BY Dep_ID, Name, email, city, address ORDER BY Dep_ID, Name, email, city, address) row_no FROM test_database.dbo.Employee ) x โดยที่ row_no> 1

วิธีที่ 2: การเลือกเรกคอร์ดที่ซ้ำกันโดยใช้ฟังก์ชัน“ NOT IN ()”

SELECT * FROM test_database.dbo พนักงาน WHERE ID NOT IN (SELECT MAX (ID) FROM test_database.dbo. Employee GROUP BY Dep_ID, Name, email, city, address)

ดำเนินการตามโค้ดด้านบนและคุณจะเห็นผลลัพธ์ต่อไปนี้ ทั้งสองวิธีให้ผลลัพธ์เหมือนกัน แต่มีต้นทุนที่แตกต่างกัน

ตอนนี้เราจะลบแถวที่ซ้ำกันที่เลือกไว้ด้านบนโดยใช้“ CTE” โดยใช้รหัสต่อไปนี้ โค้ดต่อไปนี้กำลังเลือกแถวที่ซ้ำกันที่จะลบโดยใช้ฟังก์ชัน“ ROW_NUMBER ()”

วิธีที่ 1: การลบระเบียนที่ซ้ำกันโดยใช้ฟังก์ชัน“ ROW_NUMBER ()”

ด้วย cte_delete AS (SELECT Dep_ID, ชื่อ, อีเมล, เมือง, ที่อยู่, ROW_NUMBER () OVER (PARTITION BY Dep_ID, ชื่อ, อีเมล, เมือง, ที่อยู่ ORDER BY Dep_ID, ชื่อ, อีเมล, เมือง, ที่อยู่) row_no FROM test_database.dbo.Employee ) ลบจาก cte_delete WHERE row_no> 1;

ผลลัพธ์จะเป็นดังนี้

วิธีที่ 2: การลบระเบียนที่ซ้ำกันโดยใช้ฟังก์ชัน“ NOT IN ()”

ในการทดสอบวิธีอื่นเราจำเป็นต้องตัดทอนตารางซึ่งจะลบแถวทั้งหมดออกจากตาราง จากนั้นแทรกคำสั่งจะเพิ่มค่าลงในตาราง ดำเนินการรหัสต่อไปนี้ทันที

ใช้ [test_database] ไปตัดทอนตาราง test_database.dbo พนักงาน INSERT INTO พนักงาน (Dep_ID, ชื่อ, อีเมล, เมือง, ที่อยู่) VALUES (001, 'Aaaronboy Gutierrez', '[email protected]', 'HILLSBORO', ' 5840 Ne Cornell Rd Hillsboro หรือ 97124 '), (002,' Aabdi Maghsoudi ',' [email protected] ',' BRENTWOOD ',' 987400 Nebraska Medical Center Omaha Ne 681987400 '), (003,' Aabharana, Sahni ', '[email protected]', 'HYATTSVILLE', '2 Barlo Circle Suite A Dillsburg Pa 170191'), (003, 'Aabharana, Sahni', '[email protected]', 'HYATTSVILLE', ' 2 Barlo Circle Suite A Dillsburg Pa 170191 '), (004,' Aabish Mughal ',' [email protected] ',' OMAHA ',' 2975 Crouse Lane Burlington Nc 272150000 '), (005,' Aabram Howell ',' [email protected] ',' DILLSBURG ',' 868 York Ave Atlanta Ga 303102750 '), (005,' Aabram Howell ',' [email protected] ',' DILLSBURG ',' 868 York Ave Atlanta Ga 303102750 '), (006,' Humbaerto Acevedo ',' [email protected] ',' SAINT PAUL ',' 895 E 7th St Saint Paul Mn 551063852 ' ), (006, 'Humbaerto Acevedo', '[email protected]', 'SAINT PAUL', '895 E 7th St Saint Paul Mn 551063852'), (007, 'Pilar Ackaerman', 'pilar.ackaerman @ gmail.com ',' ATLANTA ',' 5813 Eastern Ave Hyattsville Md 207822201 '); เลือก * จากพนักงาน

ผลลัพธ์จะเป็นไปตามที่ระบุด้านล่าง

ดำเนินการตามรหัสที่ระบุด้านล่างเพื่อลบแถวที่ซ้ำกันทั้งหมดออกจากตาราง“ พนักงาน”

ลบ FROM test_database.dbo พนักงาน WHERE ID NOT IN (SELECT MAX (ID) FROM test_database.dbo Employee GROUP BY Dep_ID, Name, email, city, address)

ผลลัพธ์จะเป็นดังนี้

แผนการดำเนินการและต้นทุนการสืบค้นสำหรับการลบแถวที่ซ้ำกันออกจากตารางที่จัดทำดัชนี:

ตอนนี้เราต้องตรวจสอบว่าวิธีใดจะคุ้มทุนและใช้ทรัพยากรน้อยลง เลือกรหัสและคลิกที่แผนการดำเนินการ หน้าจอต่อไปนี้จะปรากฏขึ้นเพื่อแสดงแผนการดำเนินการทั้งหมดพร้อมกับเปอร์เซ็นต์ต้นทุน

เราจะเห็นว่าวิธีที่ 1“ การลบระเบียนที่ซ้ำกันโดยใช้ฟังก์ชัน“ ROW_NUMBER ()” มีต้นทุน 33% และวิธีที่ 2“ การลบระเบียนที่ซ้ำกันโดยใช้ฟังก์ชัน NOT IN () มีค่าใช้จ่าย 67% ดังนั้นวิธีที่หนึ่งจึงคุ้มค่าที่สุดเมื่อเทียบกับวิธีที่สอง

การลบรายการที่ซ้ำกันออกจากตาราง SQL Server โดยไม่มีดัชนีเฉพาะ:

การลบแถวหรือตารางที่ซ้ำกันนั้นยากกว่าเล็กน้อยโดยไม่มีดัชนีที่ไม่ซ้ำกัน ในสถานการณ์นี้การใช้นิพจน์ตารางทั่วไป (CTE) และฟังก์ชัน ROW NUMBER () ช่วยเราในการลบระเบียนที่ซ้ำกัน ในการลบรายการที่ซ้ำกันออกจากตารางโดยไม่มีดัชนีเฉพาะเราจำเป็นต้องสร้างตัวระบุแถวที่ไม่ซ้ำกัน

รันโค้ดต่อไปนี้เพื่อสร้างตารางโดยไม่มีดัชนีเฉพาะ

ใช้ [test_database] ไปตั้งค่า ANSI_NULLS บน GO SET QUOTED_IDENTIFIER บน GO สร้างตาราง [dbo] [Employee_with_out_index] ([Dep_ID] [int] NULL, [ชื่อ] [varchar] (200) โมฆะ, [อีเมล] [varchar] (250 ) NULL, [เมือง] [varchar] (250) NULL, [ที่อยู่] [varchar] (500) NULL,) GO

ผลลัพธ์จะเป็นดังนี้

ตอนนี้แทรกระเบียนลงในตารางที่สร้างขึ้นชื่อ“ Employee_with_out_index” โดยเรียกใช้รหัสต่อไปนี้

ใช้ [test_database] GO INSERT INTO Employee_with_out_index (Dep_ID, ชื่อ, อีเมล, เมือง, ที่อยู่) VALUES (001, 'Aaaronboy Gutierrez', '[email protected]', 'HILLSBORO', '5840 Ne Cornell Rd Hillsboro หรือ 97124 '), (002,' Aabdi Maghsoudi ',' [email protected] ',' BRENTWOOD ',' 987400 Nebraska Medical Center Omaha Ne 681987400 '), (003,' Aabharana, Sahni ',' abharana.sahni@gmail com ',' HYATTSVILLE ',' 2 Barlo Circle Suite A Dillsburg Pa 170191 '), (003,' Aabharana, Sahni ',' [email protected] ',' HYATTSVILLE ',' 2 Barlo Circle Suite A Dillsburg Pa 170191 '), (004,' Aabish Mughal ',' [email protected] ',' OMAHA ',' 2975 Crouse Lane Burlington Nc 272150000 '), (005,' Aabram Howell ','[email protected] ',' DILLSBURG ',' 868 York Ave Atlanta Ga 303102750 '), (005,' Aabram Howell ',' [email protected] ',' DILLSBURG ',' 868 York Ave Atlanta Ga 303102750 '), (006 , 'Humbaerto Acevedo', '[email protected]', 'SAINT PAUL', '895 E 7th St Saint Paul Mn 551063852'), (006, 'Humbaerto Acevedo' , '[email protected]', 'SAINT PAUL', '895 E 7th St Saint Paul Mn 551063852'), (007, 'Pilar Ackaerman', '[email protected]', 'ATLANTA', '5813 Eastern Ave ไฮแอทส์วิลล์ Md 207822201'); เลือก * จาก Employee_with_out_index

ผลลัพธ์จะเป็นดังนี้

วิธีที่ 1: การลบแถวที่ซ้ำกันออกจากตารางโดยใช้ฟังก์ชัน“ ROW_NUMBER ()” และเข้าร่วม

รันโค้ดต่อไปนี้ซึ่งใช้ฟังก์ชัน ROW_NUMBER () และ JOIN เพื่อลบแถวที่ซ้ำกันออกจากตารางโดยไม่มีดัชนี ก่อนอื่น IT จะสร้างเอกลักษณ์เฉพาะเพื่อกำหนด row_no ให้กับแถวทั้งหมดและเก็บเฉพาะแถวเดียวที่ลบรายการที่ซ้ำกัน

ด้วย temp_tablr_with_row_ids AS (SELECT ROW_NUMBER () OVER (ORDER BY Dep_ID, Name, email, city, address) AS row_no, Dep_ID, Name, email, city, address FROM test_database.dbo.Employee_with_out_index) DELETE a FROM temp WH_tablr_with_with_with_with เลือก MAX (row_no) จาก temp_tablr_with_row_ids ฉัน WHERE a.Dep_ID = i.Dep_ID และ a.Name = i.Name และ a.email = i.email และ a.city = i.city และ a.address = i.address GROUP BY Dep_ID, ชื่อ, อีเมล, เมือง, ที่อยู่)

ผลลัพธ์จะเป็นดังนี้

วิธีที่ 2: การลบแถวที่ซ้ำกันออกจากตารางโดยใช้ฟังก์ชัน“ ROW_NUMBER ()” และ PARTITION BY

ในวิธีนี้เรากำลังใช้ฟังก์ชัน ROW_NUMBER ร่วมกับพาร์ติชันทีละคำสั่งเพื่อกำหนด row_no ให้กับแถวทั้งหมดแล้วลบรายการที่ซ้ำกัน ก่อนอื่นเราต้องตัดตารางเดิมที่เราสร้างไว้ก่อนหน้านี้เพื่อให้ข้อมูลทั้งหมดถูกลบออกจากตาราง จากนั้นแทรกระเบียนลงในตารางรวมทั้งระเบียนที่ซ้ำกัน แบบสอบถามที่สามจะลบแถวที่ซ้ำกันออกจากตารางชื่อ“ Employee_with_out_index”

ตารางตัดทอน Employee_with_out_index INSERT INTO Employee_with_out_index (Dep_ID, ชื่อ, อีเมล, เมือง, ที่อยู่) VALUES (001, 'Aaaronboy Gutierrez', '[email protected]', 'HILLSBORO', '5840 Ne Cornell Rd4 Hillsboro) , (002, 'Aabdi Maghsoudi', '[email protected]', 'BRENTWOOD', '987400 Nebraska Medical Center Omaha Ne 681987400'), (003, 'Aabharana, Sahni', '[email protected]' , 'HYATTSVILLE', '2 Barlo Circle Suite A Dillsburg Pa 170191'), (003, 'Aabharana, Sahni', '[email protected]', 'HYATTSVILLE', '2 Barlo Circle Suite A Dillsburg Pa 170191' ), (004, 'Aabish Mughal', '[email protected]', 'OMAHA', '2975 Crouse Lane Burlington Nc 272150000'), (005, 'Aabram Howell', '[email protected]', 'DILLSBURG', '868 York Ave Atlanta Ga 303102750'), (005, 'Aabram Howell', '[email protected]', 'DILLSBURG', '868 York Ave Atlanta Ga 303102750'), (006, ' Humbaerto Acevedo ',' [email protected] ',' SAINT PAUL ',' 895 E 7th St Saint Paul Mn 551063852 '), (006,' Hu mbaerto Acevedo ',' [email protected] ',' SAINT PAUL ',' 895 E 7th St Saint Paul Mn 551063852 '), (007,' Pilar Ackaerman ',' [email protected] ',' ATLANTA ',' 5813 Eastern Ave Hyattsville Md 207822201 ');

การเลือกระเบียนที่ซ้ำกันในตารางชั่วคราว

; ด้วย temp_tablr_with_row_ids AS (SELECT ROW_NUMBER () OVER (PARTITION BY Dep_ID, ชื่อ, อีเมล, เมือง, ที่อยู่ ORDER BY Dep_ID, ชื่อ, อีเมล, เมือง, ที่อยู่) AS row_no, Dep_ID, ชื่อ, อีเมล, เมือง, ที่อยู่ FROM Employee_with_out_index)

การลบระเบียนที่ซ้ำกันออกจากตารางชั่วคราว

ลบจาก temp_tablr_with_row_ids a WHERE row_no> 1

ผลลัพธ์จะเป็นดังนี้

นอกจากนี้เราจำเป็นต้องทราบเกี่ยวกับค่าใช้จ่ายในการดำเนินการค้นหาเพื่อให้เข้าใจว่าวิธีใดเป็นโซลูชันที่เหมาะสมที่สุด ดังนั้นคุณต้องเลือกคำค้นหาที่เกี่ยวข้องทั้งหมดและคลิกที่แผนการดำเนินการ ภาพด้านล่างแสดงแผนการดำเนินการสำหรับแบบสอบถามพร้อมกับต้นทุนการดำเนินการ การลบคำค้นหาจะเน้นในช่องสีแดง คำค้นหาแรกที่ใช้“ ROW_NUMBER ()” และส่วนคำสั่ง JOIN มีค่าใช้จ่ายในการดำเนินการ 56% ในขณะที่การสืบค้นที่สองใช้“ ROW_NUMBER ()” และ“ PARTITION BY” มีค่าใช้จ่าย 31% ดังนั้นวิธีที่สองจึงเป็นวิธีที่เหมาะสมกว่าและเราควรปฏิบัติตามโซลูชันที่เหมาะสมที่สุด