推荐订阅源

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.