من یک پایگاه داده حجیم دارم که یکی از جداول اون شامل حدودا بیش از ۳۰۰ هزار رکورد هست. حالا می خواستم ردیف های تکراری رو از داخل اون پاک کنم.
جدول زیر در واقع داخل پایگاه داده من هست که شناسه اصلی هر ردیف RowID هست:
RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null
نمیدونم بهترین روش برای حذف ردیف های تکراری در SQL Server چی هست؟
با فرض اینکه هیچ یک از ID های ردیف ت خالی یا null نیست میشه این نسخه رو پیچید:
ابتدا باید ستون های unique یا یکتا رو با استفاده از دستور GROUP BY جداسازی کنی و سپس مقادیر MIN یا MAX با استفاده از دستور SELECT جدا سازی بشه.
بعد دستور DELETE رو برای هر یک از RowId ها اعمال کنی. به صورت خلاصه کدش به شکل زیر میشه:
DELETE FROM MyTable
LEFT OUTER JOIN (
SELECT MIN(RowId) as RowId, Col1, Col2, Col3
FROM MyTable
GROUP BY Col1, Col2, Col3
) as KeepRows ON
MyTable.RowId = KeepRows.RowId
WHERE
KeepRows.RowId IS NULL
از طرفی اگر به جای id عددی (Integer) از GUID استفاده می کنی، می تونی مقدار زیر رو:
MIN(RowId)
با این مقدار جایگزین کنی:
CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn)))
با فرض انکه هیچ کدام از ستون های جدول از نوع binary نباشند می توان از روش زیر استفاده کرد.
ابتدا رکوردهای تکراری را در یک جدول به نام temp قرار می دهیم.
SELECT * into temp FROM mytable GROUP BY col1, col2, col3 HAVING COUNT(*) > 1
در ادامه با استفاده از دستور delete سطرهای تکراری را از جدول اصلی (mytable) حذف می کنیم.
DELETE FROM t1 FROM mytable t1 INNER JOIN temp t2 ON (t1.col1 = t2.col1 AND t1.col2= t2.col2 AND t1.col3 = t2.col3)
در پایان داده های موجود در جدول temp را به کمک دستور insert در جدول اصلی قرار می دهیم.
INSERT INTO mytable SELECT * FROM temp
حال تمام سطرهای تکراری از جدول mytable حذف شدند.
یک روش ساده تر وجود دارد که در آن می خواهیم برای تمام رکوردهای تکراری، تنها رکوردی را حفظ کنم که مقدار فیلد RowID ان بیشینه باشد. برای درک بهتر فرض کنید که در جدول mytable سفارشات مشتریان ذخیره می شود و ما می خواهیم سفارشات قدیمی را حذف و فقط آخرین سفارش را حفظ کنیم. می توانیم از کوئری زیر استفاده کنیم.
DELETE FROM t FROM mytable AS t
WHERE RowID < (SELECT MAX(RowID) FROM mytable WHERE col1 = t.col1 AND col2 = t.col2 AND col3 = t.col3 )
یک کپی از جدول mytable به نام t ایجاد می کنیم. سپس یک پیوند طبیعی بین جدول mytable و t برقرار می نماییم. حال سطرهایی از جدول t را که مقدار RowID آنها از مقدار متناظر RowID در جدول mytable کمتر هستند را با دستور delete حذف می کنیم.
می توان از دستور NOT IN هم استفاده کرد:
DELETE FROM t FROM mytable AS t
WHERE RowID NOT IN (SELECT MAX(RowID) FROM mytable GROUP BY col1 ,col2 ,col3)
می توان از دستور NOT EXISTS هم استفاده کرد.
DELETE FROM t FROM mytable AS t
WHERE NOT EXISTS (SELECT * FROM mytable WHERE col1 = t.col1 ANDcol2 = t.col2 AND col3 = t.col3 HAVING MAX(RowID) = t.RowID)