sql server - मैं डुप्लिकेट पंक्तियों को कैसे हटा सकता हूं?
sql-server tsql (20)
काफी बड़ी SQL Server
तालिका (यानी 300,000+ पंक्तियों) से डुप्लिकेट पंक्तियों को निकालने का सबसे अच्छा तरीका क्या है?
RowID
पहचान 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 सर्वर 2005 में, एक इंडेक्स को केवल 900 बाइट्स होने की अनुमति है, और मेरा वर्कर (2048) फ़ील्ड उस पर उड़ाता है।
मुझे पता नहीं है कि यह कितना अच्छा प्रदर्शन करेगा, लेकिन मुझे लगता है कि आप इसे लागू करने के लिए एक ट्रिगर लिख सकते हैं, भले ही आप इसे सीधे इंडेक्स के साथ नहीं कर सके। कुछ इस तरह:
-- given a table stories(story_id int not null primary key, story varchar(max) not null)
CREATE TRIGGER prevent_plagiarism
ON stories
after INSERT, UPDATE
AS
DECLARE @cnt AS INT
SELECT @cnt = Count(*)
FROM stories
INNER JOIN inserted
ON ( stories.story = inserted.story
AND stories.story_id != inserted.story_id )
IF @cnt > 0
BEGIN
RAISERROR('plagiarism detected',16,1)
ROLLBACK TRANSACTION
END
इसके अलावा, वर्कर (2048) मेरे लिए फिश लगता है (जीवन में कुछ चीजें 2048 बाइट हैं, लेकिन यह बहुत असामान्य है); क्या यह वास्तव में वर्चर (अधिकतम) नहीं होना चाहिए?
एक ही संरचना के साथ नई खाली तालिका बनाएँ
इस तरह की क्वेरी निष्पादित करें
INSERT INTO tc_category1 SELECT * FROM tc_category GROUP BY category_id, application_id HAVING count(*) > 1
फिर इस क्वेरी को निष्पादित करें
INSERT INTO tc_category1 SELECT * FROM tc_category GROUP BY category_id, application_id HAVING count(*) = 1
इस क्वेरी ने मेरे लिए बहुत अच्छा प्रदर्शन दिखाया:
DELETE tbl
FROM
MyTable tbl
WHERE
EXISTS (
SELECT
*
FROM
MyTable tbl2
WHERE
tbl2.SameValue = tbl.SameValue
AND tbl.IdUniqueValue < tbl2.IdUniqueValue
)
इसने 2 एम (50% डुप्लिकेट) की तालिका से 30 एमसी से थोड़ा अधिक में 1 एम पंक्तियों को हटा दिया
इसे इस्तेमाल करो
WITH tblTemp as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name,Department ORDER BY Name)
As RowNumber,* FROM <table_name>
)
DELETE FROM tblTemp where RowNumber >1
ऐसा करने का एक और संभावित तरीका है
;
--Ensure that any immediately preceding statement is terminated with a semicolon above
WITH cte
AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3
ORDER BY ( SELECT 0)) RN
FROM #MyTable)
DELETE FROM cte
WHERE RN > 1;
मैं ऊपर ORDER BY (SELECT 0)
का उपयोग कर रहा हूं क्योंकि यह एक टाई की स्थिति में संरक्षित करने के लिए मनमानी है।
उदाहरण के लिए RowID
ऑर्डर में नवीनतम को संरक्षित करने के लिए आप ORDER BY RowID DESC
उपयोग कर सकते हैं
निष्पादन योजनाएं
इसके लिए निष्पादन योजना स्वीकार्य उत्तर में उससे अधिक सरल और अधिक कुशल होती है क्योंकि इसे स्वयं में शामिल होने की आवश्यकता नहीं होती है।
हालांकि यह हमेशा मामला नहीं है। एक स्थान जहां GROUP BY
समाधान को प्राथमिकता दी जा सकती है वह ऐसी परिस्थितियां हैं जहां एक हैश कुल को प्राथमिकता में प्राथमिकता में चुना जाएगा।
ROW_NUMBER
समाधान हमेशा एक ही योजना प्रदान करेगा जबकि GROUP BY
रणनीति अधिक लचीला है।
हैश समग्र दृष्टिकोण का पक्ष लेने वाले कारक होंगे
- विभाजन कॉलम पर कोई उपयोगी अनुक्रमणिका नहीं
- प्रत्येक समूह में अपेक्षाकृत अधिक डुप्लिकेट वाले अपेक्षाकृत कम समूह
इस दूसरे मामले के चरम संस्करणों में (यदि प्रत्येक में कई डुप्लिकेट वाले बहुत कम समूह हैं) तो कोई भी नई तालिका में रखने के लिए पंक्तियों को सम्मिलित करने पर विचार कर सकता है, फिर मूल को ट्रंकेट कर सकता है और उन्हें हटाने की तुलना में लॉगिंग को कम करने के लिए उन्हें वापस कॉपी कर सकता है पंक्तियों का एक बहुत अधिक अनुपात।
कोई नल मानते हुए, आप अद्वितीय कॉलम से GROUP BY
करते हैं, और रखने के लिए पंक्ति के रूप में MIN (or MAX)
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
यदि आपके पास पूर्णांक की बजाय GUID है, तो आप प्रतिस्थापित कर सकते हैं
MIN(RowId)
साथ में
CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn)))
डुप्लिकेट रिकॉर्ड को हटाने का यह सबसे आसान तरीका है
DELETE FROM tblemp WHERE id IN
(
SELECT MIN(id) FROM tblemp
GROUP BY title HAVING COUNT(id)>1
)
http://askme.indianyouth.info/details/how-to-dumplicate-record-from-table-in-using-sql-105
दूसरी तरफ एक ही फ़ील्ड और अद्वितीय इंडेक्स के साथ एक नई टेबल बनाएं । फिर पुरानी तालिका से सभी तालिका में सभी डेटा ले जाएं । स्वचालित रूप से SQL सर्वर अनदेखा करता है (डुप्लिकेट मान होने पर अनदेखा करने, बाधित करने या sth) होने पर क्या करना है इसके बारे में एक विकल्प भी है। तो हमारे पास डुप्लिकेट पंक्तियों के बिना एक ही टेबल है। यदि आप ट्रांसफर डेटा के बाद अनन्य इंडेक्स नहीं चाहते हैं तो आप इसे छोड़ सकते हैं ।
विशेष रूप से बड़ी तालिकाओं के लिए आप अपने नए विशिष्ट अनुक्रमित तालिका में तेजी से सभी डेटा स्थानांतरित करने के लिए डीटीएस (एसएसआईएस पैकेज आयात / निर्यात डेटा) का उपयोग कर सकते हैं। 7 मिलियन पंक्ति के लिए इसमें कुछ ही मिनट लगते हैं।
फिर भी चिपकाए गए लिंक पर एक और आसान समाधान मिल सकता http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server । यह समझने में आसान है और इसी तरह की अधिकांश समस्याओं के लिए प्रभावी प्रतीत होता है। यह SQL सर्वर के लिए है, लेकिन उपयोग की गई अवधारणा स्वीकार्य से अधिक है।
लिंक किए गए पृष्ठ से प्रासंगिक भाग यहां दिए गए हैं:
इस डेटा पर विचार करें:
EMPLOYEE_ID ATTENDANCE_DATE
A001 2011-01-01
A001 2011-01-01
A002 2011-01-01
A002 2011-01-01
A002 2011-01-01
A003 2011-01-01
तो हम उन डुप्लिकेट डेटा को कैसे हटा सकते हैं?
सबसे पहले, निम्न कोड का उपयोग करके उस तालिका में एक पहचान कॉलम डालें:
ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)
इसे हल करने के लिए निम्न कोड का प्रयोग करें:
DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)
मेरे पास एक टेबल था जहां मुझे गैर-डुप्लिकेट पंक्तियों को संरक्षित करने की आवश्यकता थी। मुझे गति या दक्षता पर यकीन नहीं है।
DELETE FROM myTable WHERE RowID IN (
SELECT MIN(RowID) AS IDNo FROM myTable
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) = 2 )
मैं इस दृष्टिकोण का उल्लेख करता हूं और साथ ही यह सहायक भी हो सकता है, और सभी SQL सर्वरों में काम करता है: अक्सर अक्सर केवल एक-दो डुप्लीकेट होते हैं, और आईडी और डुप्लिकेट की गिनती ज्ञात होती है। इस मामले में:
SET ROWCOUNT 1 -- or set to number of rows to be deleted
delete from myTable where RowId = DuplicatedID
SET ROWCOUNT 0
मैं एसक्यूएल सर्वर तालिका से डुप्लिकेट पंक्तियों को हटाने के लिए सीटीई पसंद करूंगा
दृढ़ता से इस आलेख का पालन करने की अनुशंसा करें :: http://dotnetmob.com/sql-server-article/delete-duplicate-rows-in-sql-server/
मूल रखकर
WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY col1,col2,col3) AS RN
FROM MyTable
)
DELETE FROM CTE WHERE RN<>1
मूल रखने के बिना
WITH CTE AS
(SELECT *,R=RANK() OVER (ORDER BY col1,col2,col3)
FROM MyTable)
DELETE CTE
WHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)>1)
यह पहली पंक्ति को छोड़कर डुप्लिकेट पंक्तियों को हटा देगा
DELETE
FROM
Mytable
WHERE
RowID NOT IN (
SELECT
MIN(RowID)
FROM
Mytable
GROUP BY
Col1,
Col2,
Col3
)
देखें ( http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server डुप्लिकेट- पंक्तियां- से-टेबल-in-SQL- सर्वर)
सटीक डुप्लिकेट पंक्तियों को हटाने के लिए त्वरित और गंदा (छोटी तालिकाओं के लिए):
select distinct * into t2 from t1;
delete from t1;
insert into t1 select * from t2;
drop table t2;
हा ज़रूर। एक अस्थायी तालिका का प्रयोग करें। यदि आप एक एकल, बहुत ही निष्पादित कथन चाहते हैं जो "काम करता है" तो आप इसके साथ जा सकते हैं:
DELETE FROM MyTable WHERE NOT RowID IN
(SELECT
(SELECT TOP 1 RowID FROM MyTable mt2
WHERE mt2.Col1 = mt.Col1
AND mt2.Col2 = mt.Col2
AND mt2.Col3 = mt.Col3)
FROM MyTable mt)
असल में, तालिका में प्रत्येक पंक्ति के लिए, उप-चयन उन सभी पंक्तियों के शीर्ष पंक्ति को पाता है जो बिल्कुल पंक्ति के समान हैं। तो आप RowIDs की एक सूची के साथ समाप्त होते हैं जो "मूल" गैर-डुप्लिकेट पंक्तियों का प्रतिनिधित्व करते हैं।
डुप्लीकेट हटाने पर एक और अच्छा लेख है।
यह चर्चा करता है कि इसकी कड़ी क्यों है: " एसक्यूएल रिलेशनल बीजगणित पर आधारित है, और डुप्लिकेट संबंधपरक बीजगणित में नहीं हो सकता है, क्योंकि सेट में डुप्लिकेट की अनुमति नहीं है। "
अस्थायी तालिका समाधान, और दो mysql उदाहरण।
भविष्य में आप इसे डेटाबेस स्तर पर या एप्लिकेशन परिप्रेक्ष्य से रोकने जा रहे हैं। मैं डेटाबेस स्तर का सुझाव दूंगा क्योंकि आपका डेटाबेस संदर्भित अखंडता को बनाए रखने के लिए ज़िम्मेदार होना चाहिए, डेवलपर्स केवल समस्याएं पैदा करेंगे;)
CREATE TABLE car(Id int identity(1,1), PersonId int, CarId int)
INSERT INTO car(PersonId,CarId)
VALUES(1,2),(1,3),(1,2),(2,4)
--SELECT * FROM car
;WITH CTE as(
SELECT ROW_NUMBER() over (PARTITION BY personid,carid order by personid,carid) as rn,Id,PersonID,CarId from car)
DELETE FROM car where Id in(SELECT Id FROM CTE WHERE rn>1)
DELETE
FROM
table_name T1
WHERE
rowid > (
SELECT
min(rowid)
FROM
table_name T2
WHERE
T1.column_name = T2.column_name
);
DELETE LU
FROM (SELECT *,
Row_number()
OVER (
partition BY col1, col1, col3
ORDER BY rowid DESC) [Row]
FROM mytable) LU
WHERE [row] > 1
SELECT DISTINCT *
INTO tempdb.dbo.tmpTable
FROM myTable
TRUNCATE TABLE myTable
INSERT INTO myTable SELECT * FROM tempdb.dbo.tmpTable
DROP TABLE tempdb.dbo.tmpTable