วิธีลบแถวที่ซ้ำกันจากตารางใน SQL Server

การแปลบทความ การแปลบทความ
หมายเลขบทความ (Article ID): 139444 - ผลิตภัณฑ์ที่เกี่ยวข้องในบทความนี้
ขยายทั้งหมด | ยุบทั้งหมด

สรุป

ตาราง Microsoft SQL Server ไม่ควรประกอบด้วยแถวที่ซ้ำกัน หรือคีย์หลักซ้ำกัน สำหรับ brevity เราจะบางครั้งอ้างถึงคีย์หลักเป็น "คีย์" หรือ "PK" ในบทความนี้ แต่นี้เสมอจะ denote "คีย์หลัก PKs ที่ซ้ำกันจะมีการละเมิดความสมบูรณ์ของเอ็นติตี้ และควรจะไม่ได้รับอนุญาตในระบบเกี่ยว sql Server ได้กลไกต่าง ๆ สำหรับการบังคับใช้ความสมบูรณ์ของเอ็นติตี้ การรวมทั้งดัชนี ข้อจำกัดที่ไม่ซ้ำกัน ข้อจำกัด KEY หลัก และทริกเกอร์

despite นี้ ภายใต้สถานการณ์ unusual คีย์หลักที่ซ้ำกันอาจเกิดขึ้น และถ้าใช่ พวกเขาต้องถูกตัด วิธีหนึ่งที่อาจเกิดขึ้นได้คือ ถ้า PKs ที่ซ้ำกันที่มีอยู่ในข้อมูลที่ไม่เกี่ยวภายนอกของ sql server และข้อมูลมีการนำเข้าในขณะที่ PK uniqueness จะไม่มีการบังคับใช้ วิธีอื่นที่อาจเกิดขึ้นได้คือผ่านผิดเป็นฐานข้อมูลการออกแบบพลาด เช่นไม่บังคับให้ใช้ความสมบูรณ์ของเอ็นติตี้บนแต่ละตาราง

บ่อยครั้ง PKs ซ้ำจะ noticed เมื่อคุณพยายามที่จะสร้างเฉพาะดัชนี ซึ่งจะยกเลิกหากพบแป้นที่ซ้ำกัน ข้อความนี้คือ:
msg 1505 ระดับ 16 สถานะ 1 สร้างเฉพาะดัชนีถูกยกเลิกแป้นที่ซ้ำกัน
ถ้าคุณใช้ SQL Server 2000 หรือ SQL Server 2005 คุณอาจได้รับข้อความแสดงข้อความแสดงข้อผิดพลาดต่อไปนี้:
msg 1505 ระดับ 16 สถานะ 1 สร้างเฉพาะ INDEX จบการทำงานเนื่องจากการตรวจพบคีย์ที่ซ้ำกันสำหรับวัตถุที่ชื่อ ' % * ls 'และการจัดทำดัชนีชื่อ' % * ls' ค่าของคีย์ซ้ำกันอยู่% ls
บทความนี้อธิบายถึงวิธีการค้นหา และเอาคีย์หลักที่ซ้ำกันจากตาราง อย่างไรก็ตาม คุณควรตรวจกขนาดสอบกระบวนการที่อนุญาตให้ใช้รายการที่ซ้ำกันจะเกิดขึ้นเพื่อ ป้องกันการเกิดซ้ำ

ข้อมูลเพิ่มเติม

ตัวอย่างนี้ เราจะใช้ตารางต่อไปนี้ ด้วยค่า PK ที่ซ้ำกัน ในตารางนี้ คีย์หลักเป็นคอลัมน์ที่สอง (col1, col2) เราไม่สามารถสร้างดัชนีที่ไม่ซ้ำกันหรือจำกัด KEY หลักเนื่องจากแถวที่สองมี PKs ที่ซ้ำกัน ขั้นตอนนี้แสดงให้เห็นถึงวิธีการระบุ และเอาข้อมูลซ้ำ
create table t1(col1 int, col2 int, col3 char(50))
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 2, 'data value two')
				
ขั้นแรกคือเพื่อ ระบุแถวที่มีค่าคีย์หลักที่ซ้ำกัน:
SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
				
ซึ่งจะส่งกลับหนึ่งบรรทัดสำหรับแต่ละชุดของค่า PK ที่ซ้ำกันในตาราง คอลัมน์สุดท้ายในผลลัพธ์นี้มีหมายเลขของรายการที่ซ้ำกันสำหรับค่า PK เฉพาะ

ยุบตารางนี้ขยายตารางนี้
col1col2
112


ถ้ามีเพียงสองสามชุดของค่า PK ซ้ำ กระบวนการที่ดีที่สุดคือการที่ ลบด้วยตนเองในเกณฑ์แต่ละข้อ ตัวอย่าง::
set rowcount 1
delete from t1
where col1=1 and col2=1
				
ค่า rowcount ควร n-1 หมายเลขของรายการที่ซ้ำกันสำหรับค่าคีย์ที่กำหนด ในตัวอย่างนี้ มีสำเนาที่ 2 เพื่อ rowcount มีการตั้งค่าเป็น 1 ค่า col1/col2 ถูกนำมาจากการข้างต้น GROUP BY ผลลัพธ์การสอบถาม ถ้าแบบสอบถาม GROUP BY หลายแถวที่ส่งคืน การสอบถาม "เซ็ต rowcount" จะต้องถูกเรียกใช้ครั้งเดียวสำหรับแต่ละแถวเหล่านี้ แต่ละครั้งจะมีรัน กำหนด rowcount n-1 หมายเลขของรายการที่ซ้ำกันของค่า PK เฉพาะ

ก่อนที่จะลบแถว คุณควรตรวจสอบว่า แถวทั้งหมดที่ไม่ซ้ำกัน ในขณะที่ unlikely เป็นไปได้ว่า ค่า PK ยังซ้ำ ได้แถวเป็นจำนวนเต็มไม่ ตัวอย่างนี้จะเป็นตารางที่ มีหมายเลขการรักษาความปลอดภัย Social เป็นคีย์หลัก และมีสองแตกต่างกันผู้อื่น (หรือแถว) ด้วยหมายเลขเดียวกัน แต่ละมีแอตทริบิวต์ที่ไม่ซ้ำกัน ในกรณีเช่น คีย์ที่ซ้ำกันที่ทำให้เกิดความผิดปกติใด ๆ ก็ตามอาจมีสาเหตุยังมาข้อมูลเฉพาะที่ถูกต้องจะถูกวางในแถว ข้อมูลนี้จะถูกคัดลอกออก และรักษาไว้สำหรับการกระทบยอดการศึกษาและเป็นไปได้ก่อนที่จะลบข้อมูล

ถ้าไม่มีชุดที่แตกต่างกันหลาย ๆ ค่า PK ซ้ำกันในตาราง คุณอาจใช้เวลานานเกินกว่าที่จะเอาแฟ้มออกแต่ละรายการ ในกรณีนี้ ขั้นตอนต่อไปนี้สามารถใช้ได้:
  1. ขั้นแรก รันสอบข้างต้น GROUP BY ถามเพื่อดูว่ามีการตั้งค่าจำนวนของค่า PK ซ้ำ และจำนวนของรายการที่ซ้ำสำหรับแต่ละชุด
  2. เลือกค่าคีย์ที่ซ้ำกันในตาราง holding ตัวอย่าง::
    SELECT col1, col2, col3=count(*)
    INTO holdkey
    FROM t1
    GROUP BY col1, col2
    HAVING count(*) > 1
    					
  3. เลือกแถวที่ซ้ำกันในตาราง holding รายการที่ซ้ำในขั้นตอนการตัดออก ตัวอย่าง::
    SELECT DISTINCT t1.*
    INTO holddups
    FROM t1, holdkey
    WHERE t1.col1 = holdkey.col1
    AND t1.col2 = holdkey.col2
    					
  4. ณจุดนี้ ตาราง holddups ควรมีเฉพาะ PKs อย่างไรก็ตาม ซึ่งจะไม่เกิดขึ้นในกรณีถ้า t1 PKs ซ้ำ ได้เฉพาะแถว (เหมือนในตัว SSN อย่างข้างต้น) Verify that each key in holddups is unique, and that you do not have duplicate keys, yet unique rows. If so, you must stop here and reconcile which of the rows you wish to keep for a given duplicate key value. For example, the query:
    SELECT col1, col2, count(*)
    FROM holddups
    GROUP BY col1, col2
    						
    should return a count of 1 for each row. If yes, proceed to step 5 below. If no, you have duplicate keys, yet unique rows, and need to decide which rows to save. This will usually entail either discarding a row, or creating a new unique key value for this row. Take one of these two steps for each such duplicate PK in the holddups table.
  5. Delete the duplicate rows from the original table. ตัวอย่าง::
    DELETE t1
    FROM t1, holdkey
    WHERE t1.col1 = holdkey.col1
    AND t1.col2 = holdkey.col2
    					
  6. Put the unique rows back in the original table. ตัวอย่าง::
    INSERT t1 SELECT * FROM holddups
    					

คุณสมบัติ

หมายเลขบทความ (Article ID): 139444 - รีวิวครั้งสุดท้าย: 17 กันยายน 2554 - Revision: 3.0
ใช้กับ
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL 2005 Server Enterprise
  • Microsoft SQL 2005 Server Workgroup
Keywords: 
kbinfo kbusage kbmt KB139444 KbMtth
แปลโดยคอมพิวเตอร์
ข้อมูลสำคัญ: บทความนี้แปลโดยซอฟต์แวร์การแปลด้วยคอมพิวเตอร์ของ Microsoft แทนที่จะเป็นนักแปลที่เป็นบุคคล Microsoft มีบทความที่แปลโดยนักแปลและบทความที่แปลด้วยคอมพิวเตอร์ เพื่อให้คุณสามารถเข้าถึงบทความทั้งหมดในฐานความรู้ของเรา ในภาษาของคุณเอง อย่างไรก็ตาม บทความที่แปลด้วยคอมพิวเตอร์นั้นอาจมีข้อบกพร่อง โดยอาจมีข้อผิดพลาดในคำศัพท์ รูปแบบการใช้ภาษาและไวยากรณ์ เช่นเดียวกับกรณีที่ชาวต่างชาติพูดผิดเมื่อพูดภาษาของคุณ Microsoft ไม่มีส่วนรับผิดชอบต่อความคลาดเคลื่อน ความผิดพลาดหรือความเสียหายที่เกิดจากการแปลเนื้อหาผิดพลาด หรือการใช้บทแปลของลูกค้า และ Microsoft มีการปรับปรุงซอฟต์แวร์การแปลด้วยคอมพิวเตอร์อยู่เป็นประจำ
ต่อไปนี้เป็นฉบับภาษาอังกฤษของบทความนี้:139444

ให้ข้อเสนอแนะ

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com