2006年5月12日随笔档案 - fengzhimei

2006年5月12日

Here is the thing, if we have a table with the following structure, there are thousands of records in this table, and probably some of which is duplicated. Now we need to delete those duplications by a sql query, what we should do?

CREATE TABLE [dbo].[postings] (

[ID] [int] IDENTITY (1, 1) NOT NULL ,

[postingname] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

[postedby] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL

)

There are many different ways can accomplish this, like cursor, tempary table, concatenate columns together and then use distinct operator etc. But here I'm gonna tell you another easy way.

DELETE FROM postings

WHERE EXISTS

(

SELECT NULL FROM postings b

WHERE

b.[postingname] = postings.[postingname] AND

b.[postedby] = postings.[postedby]

GROUP BY

b.[postingname], b.[postedby]

HAVING

postings.[id] < MAX(b.[id])

)

Hope it helps.

posted @ 2006-05-12 15:28 fengzhimei 阅读(583) 评论(0) 编辑

Remove duplicate items from collection

public void RemoveDuplicatesByName()

{

List<Posting> postings = GetAllPostings();

postings.Sort();

for (int i = 1; i < postings.Count; i++)

{

if (postings[i].Name == postings[i - 1].Name)

{

postings.RemoveAt(i);

i--;

}

public List<Posting> GetAllPostings()

{

List<Posting> postings = new List<Posting>();

Posting posting;

posting = new Posting("post1", "2005-5-11");

postings.Add(posting);

posting = new Posting("post1", "2005-5-11");

postings.Add(posting);

posting = new Posting("post2", "2005-6-11");

postings.Add(posting);

posting = new Posting("post2", "2005-5-11");

postings.Add(posting);

posting = new Posting("post3", "2005-8-11");

postings.Add(posting);

posting = new Posting("post2", "2005-7-11");

postings.Add(posting);

posting = new Posting("post1", "2005-5-12");

postings.Add(posting);

posting = new Posting("post2", "2005-8-11");

postings.Add(posting);

return postings;

}

public class Posting : IComparable<Posting>

{

private string name;

private string lastModifiedDate;

public Posting(string name, string lastModifiedDate)

{

this.name = name;

this.lastModifiedDate = lastModifiedDate;

}

public string Name

{

get { return name; }

set { name = value; }

}

public string LastModifiedDate

{

get { return lastModifiedDate; }

set { lastModifiedDate = value; }

}

public int CompareTo(Posting other)

{

return (String.Compare(this.Name, other.Name));

}

posted @ 2006-05-12 13:14 fengzhimei 阅读(635) 评论(0) 编辑