刪除重複值

-- =============================================================================
-- 標題: 刪除重複值
-- 整理: takako_mu
-- 时间: 2009-12-09
-- 地点: 昆山
-- =============================================================================

------------------------------------------------------------
-- 1.數據庫自帶自增ID.利用此ID刪除重複資料
create table #tb1(ID int identity(1,1) not null,StringTest nvarchar(10))
insert into #tb1 
select 'aaa' union all
select 'bbb' union all
select 'bbb' union all
select 'ccc' 
go
--        select * from #tb1
--        ID          StringTest
--        ----------- ----------
--        1           aaa
--        2           bbb
--        3           bbb
--        4           ccc
--        
--        (4 個資料列受到影響)

delete #tb1 
    where id not in 
                (
                    select min(id) from #tb1 group by stringTest
                )
--        select * from #tb1
--        ID          StringTest
--        ----------- ----------
--        1           aaa
--        2           bbb
--        4           ccc
--
--        (3 個資料列受到影響)



------------------------------------------------------------
-- 2.無自增列,利用sql2005的row_number()刪除重複子資料
create table #tb2(StringTest nvarchar(10))
insert into #tb2
select 'aaa' union all
select 'bbb' union all
select 'bbb' union all
select 'ccc' 
go
--        select * from #tb2
--        StringTest
--        ----------
--        aaa
--        bbb
--        bbb
--        ccc
--
--        (4 個資料列受到影響)

-- 2.1
with A as 
(
    select row_number() over(order by StringTest) as ID,StringTest from #tb2
)
delete A where ID not in
    (
        select min(ID) from A group by StringTest
    )        


-- 2.2 利用partition by函數
with A as
(
    select row_number() over(partition by StringTest order by StringTest) as rn from #tb2 
)
delete A where rn>1