sql server मैं डुप्लिकेट पंक्तियों को कैसे हटा सकता हूं?
sql-server tsql (24)
काफी बड़ी 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
इस क्वेरी ने मेरे लिए बहुत अच्छा प्रदर्शन दिखाया:
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 एम पंक्तियों को हटा दिया
DELETE
FROM MyTable
WHERE NOT EXISTS (
SELECT min(RowID)
FROM Mytable
WHERE (SELECT RowID
FROM Mytable
GROUP BY Col1, Col2, Col3
))
);
DELETE LU
FROM (SELECT *,
Row_number()
OVER (
partition BY col1, col1, col3
ORDER BY rowid DESC) [Row]
FROM mytable) LU
WHERE [row] > 1
हा ज़रूर। एक अस्थायी तालिका का प्रयोग करें। यदि आप एक एकल, बहुत ही निष्पादित कथन चाहते हैं जो "काम करता है" तो आप इसके साथ जा सकते हैं:
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 की एक सूची के साथ समाप्त होते हैं जो "मूल" गैर-डुप्लिकेट पंक्तियों का प्रतिनिधित्व करते हैं।
फिर भी चिपकाए गए लिंक पर एक और आसान समाधान मिल सकता 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)
डुप्लिकेट पंक्तियां प्राप्त करने के लिए:
SELECT
name, email, COUNT(*)
FROM
users
GROUP BY
name, email
HAVING COUNT(*) > 1
डुप्लिकेट पंक्तियों को हटाने के लिए:
DELETE users
WHERE rowid NOT IN
SELECT MIN(rowid)
FROM users
GROUP BY name, email);
delete t1
from table t1, table t2
where t1.columnA = t2.columnA
and t1.rowid>t2.rowid
postgres:
delete
from table t1
using table t2
where t1.columnA = t2.columnA
and t1.rowid > t2.rowid
इसे इस्तेमाल करो
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
कोई नल मानते हुए, आप अद्वितीय कॉलम से 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
table_name T1
WHERE
rowid > (
SELECT
min(rowid)
FROM
table_name T2
WHERE
T1.column_name = T2.column_name
);
मैं subquery \ को आंतरिक शामिल होने के लिए गिनती (*)> 1 समाधान पसंद करता हूं क्योंकि मुझे इसे पढ़ने में आसान लगता है और इसे चलाने से पहले हटाए जाने वाले सत्यापन को सत्यापित करने के लिए एक चयन कथन में बदलना बहुत आसान था।
--DELETE FROM table1
--WHERE id IN (
SELECT MIN(id) FROM table1
GROUP BY col1, col2, col3
-- could add a WHERE clause here to further filter
HAVING count(*) > 1
--)
नीचे दी गई क्वेरी का उपयोग करके हम एकल कॉलम या एकाधिक कॉलम के आधार पर डुप्लिकेट रिकॉर्ड को हटाने में सक्षम हो सकते हैं। नीचे क्वेरी दो कॉलम के आधार पर हटा रही है। तालिका का नाम है: testing और कॉलम नाम empno,empname
DELETE FROM testing WHERE empno not IN (SELECT empno FROM (SELECT empno, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno)
AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)
or empname not in
(select empname from (select empname,row_number() over(PARTITION BY empno ORDER BY empno)
AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)
मैं एसक्यूएल सर्वर तालिका से डुप्लिकेट पंक्तियों को हटाने के लिए सीटीई पसंद करूंगा
दृढ़ता से इस आलेख का पालन करने की अनुशंसा करें :: 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)
मैं इस दृष्टिकोण का उल्लेख करता हूं और साथ ही यह सहायक भी हो सकता है, और सभी SQL सर्वरों में काम करता है: अक्सर अक्सर केवल एक-दो डुप्लीकेट होते हैं, और आईडी और डुप्लिकेट की गिनती ज्ञात होती है। इस मामले में:
SET ROWCOUNT 1 -- or set to number of rows to be deleted
delete from myTable where RowId = DuplicatedID
SET ROWCOUNT 0
सटीक डुप्लिकेट पंक्तियों को हटाने के लिए त्वरित और गंदा (छोटी तालिकाओं के लिए):
select distinct * into t2 from t1;
delete from t1;
insert into t1 select * from t2;
drop table t2;
ऐसा करने का एक और संभावित तरीका है
;
--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 रणनीति अधिक लचीला है।
हैश समग्र दृष्टिकोण का पक्ष लेने वाले कारक होंगे
- विभाजन कॉलम पर कोई उपयोगी अनुक्रमणिका नहीं
- प्रत्येक समूह में अपेक्षाकृत अधिक डुप्लिकेट वाले अपेक्षाकृत कम समूह
इस दूसरे मामले के चरम संस्करणों में (यदि प्रत्येक में कई डुप्लिकेट वाले बहुत कम समूह हैं) तो कोई भी नई तालिका में रखने के लिए पंक्तियों को सम्मिलित करने पर विचार कर सकता है, फिर मूल को ट्रंकेट कर सकता है और उन्हें हटाने की तुलना में लॉगिंग को कम करने के लिए उन्हें वापस कॉपी कर सकता है पंक्तियों का एक बहुत अधिक अनुपात।
एक ही संरचना के साथ नई खाली तालिका बनाएँ
इस तरह की क्वेरी निष्पादित करें
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
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- सर्वर)
आवेदन स्तर से (दुर्भाग्य से)। मैं मानता हूं कि डुप्लिकेशंस को रोकने का उचित तरीका डेटाबेस स्तर पर एक अद्वितीय इंडेक्स के उपयोग के माध्यम से है, लेकिन 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 बाइट हैं, लेकिन यह बहुत असामान्य है); क्या यह वास्तव में वर्चर (अधिकतम) नहीं होना चाहिए?
मैंने सोचा कि मैं अपना समाधान साझा करूंगा क्योंकि यह विशेष परिस्थितियों में काम करता है। मैं अपने मामले में डुप्लिकेट मानों वाली तालिका में एक विदेशी कुंजी नहीं थी (क्योंकि मान किसी अन्य डीबी से डुप्लिकेट किए गए थे)।
begin transaction
-- create temp table with identical structure as source table
Select * Into #temp From tableName Where 1 = 2
-- insert distinct values into temp
insert into #temp
select distinct *
from tableName
-- delete from source
delete from tableName
-- insert into source from temp
insert into tableName
select *
from #temp
rollback transaction
-- if this works, change rollback to commit and execute again to keep you changes!!
पीएस: इस तरह की चीजों पर काम करते समय मैं हमेशा एक लेनदेन का उपयोग करता हूं, यह न केवल सब कुछ पूरी तरह से निष्पादित किया जाता है, बल्कि मुझे कुछ भी जोखिम के बिना परीक्षण करने की अनुमति देता है। लेकिन निश्चित रूप से आपको सुनिश्चित करने के लिए वैसे भी बैकअप लेना चाहिए ...
दूसरी तरफ एक ही फ़ील्ड और अद्वितीय इंडेक्स के साथ एक नई टेबल बनाएं । फिर पुरानी तालिका से सभी तालिका में सभी डेटा ले जाएं । स्वचालित रूप से SQL सर्वर अनदेखा करता है (डुप्लिकेट मान होने पर अनदेखा करने, बाधित करने या sth) होने पर क्या करना है इसके बारे में एक विकल्प भी है। तो हमारे पास डुप्लिकेट पंक्तियों के बिना एक ही टेबल है। यदि आप ट्रांसफर डेटा के बाद अनन्य इंडेक्स नहीं चाहते हैं तो आप इसे छोड़ सकते हैं ।
विशेष रूप से बड़ी तालिकाओं के लिए आप अपने नए विशिष्ट अनुक्रमित तालिका में तेजी से सभी डेटा स्थानांतरित करने के लिए डीटीएस (एसएसआईएस पैकेज आयात / निर्यात डेटा) का उपयोग कर सकते हैं। 7 मिलियन पंक्ति के लिए इसमें कुछ ही मिनट लगते हैं।
डुप्लिकेट रिकॉर्ड को हटाने का यह सबसे आसान तरीका है
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
डुप्लिकेट पंक्तियों को हटाने के लिए निम्न क्वेरी उपयोगी है। इस उदाहरण में तालिका में पहचान कॉलम के रूप में ID और जिन स्तंभों में डुप्लिकेट डेटा है, वे कॉलम 1, कॉलम 2 और कॉलम Column3 ।
DELETE FROM TableName
WHERE ID NOT IN (SELECT MAX(ID)
FROM TableName
GROUP BY Column1,
Column2,
Column3
/*Even if ID is not null-able SQL Server treats MAX(ID) as potentially
nullable. Because of semantics of NOT IN (NULL) including the clause
below can simplify the plan*/
HAVING MAX(ID) IS NOT NULL)
निम्न स्क्रिप्ट GROUP BY , HAVING , ORDER BY एक क्वेरी में उपयोग दिखाती है, और परिणाम डुप्लिकेट कॉलम और इसकी गिनती के साथ देता है।
SELECT YourColumnName,
COUNT(*) TotalCount
FROM YourTableName
GROUP BY YourColumnName
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
सीटीई का उपयोग करना विचार एक या अधिक स्तंभों में शामिल होना है जो एक डुप्लिकेट रिकॉर्ड बनाते हैं और फिर जो भी आपको पसंद करते हैं उसे हटा दें:
;with cte as (
select
min(PrimaryKey) as PrimaryKey
UniqueColumn1,
UniqueColumn2
from dbo.DuplicatesTable
group by
UniqueColumn1, UniqueColumn1
having count(*) > 1
)
delete d
from dbo.DuplicatesTable d
inner join cte on
d.PrimaryKey > cte.PrimaryKey and
d.UniqueColumn1 = cte.UniqueColumn1 and
d.UniqueColumn2 = cte.UniqueColumn2;
