求SQL语句，删除重复的数据只留第一笔
请问如何将表A，同一ID删除重复的数据，只留下第一笔数据，并得到如表B的结果
表A:
ID DATE I1 I2 I3 I4 I5
A1 02/06 (857)
A1 06/11 (857)
A1 08/02 (855) (857) (855,857)
A1 08/05 (851) (857) (874) (851,857),(851,874),(857,874) (851,857,874)
A1 10/21 (855) (857) (873) (855,857),(855,873),(857,873) (855,857,873)
A1 10/25 (855) (857) (873) (855,857),(855,873),(857,873) (855,857,873)
B1 ....
结果如下：
ID DATE I1 I2 I3 I4 I5
A1 02/06 (857)
A1 06/11
A1 08/02 (855) (855,857)
A1 08/05 (851) (874) (851,857),(851,874),(857,874) (851,857,874)
A1 10/21 (873) (855,873),(857,873) (855,857,873)
A1 10/25
B1 ...
[解决办法]
create table tb(ID varchar(10),DATE varchar(10),I1 varchar(10),I2 varchar(10),I3 varchar(10),I4 varchar(30),I5 varchar(30))
insert into tb values( 'A1 ', '02/06 ', '(857) ', ' ' , ' ' , ' ' , ' ')
insert into tb values( 'A1 ', '06/11 ', '(857) ', ' ' , ' ' , ' ' , ' ')
insert into tb values( 'A1 ', '08/02 ', '(855) ', '(857) ', ' ' , '(855,857) ', ' ')
insert into tb values( 'A1 ', '08/05 ', '(851) ', '(857) ', '(874) ', '(851,857),(851,874),(857,874) ', '(851,857,874) ')
insert into tb values( 'A1 ', '10/21 ', '(855) ', '(857) ', '(873) ', '(855,857),(855,873),(857,873) ', '(855,857,873) ')
insert into tb values( 'A1 ', '10/25 ', '(855) ', '(857) ', '(873) ', '(855,857),(855,873),(857,873) ', '(855,857,873) ')
GO
SELECT TB.ID , TB.DATE , ISNULL(T1.I1, ' ') I1 , ISNULL(T2.I2, ' ') I2 , ISNULL(T3.I3, ' ') I3 , I4,I5 FROM TB
LEFT JOIN (select ID,I1,min(DATE) DATE from tb GROUP BY ID,I1) T1 ON TB.ID = T1.ID AND TB.DATE = T1.DATE
LEFT JOIN (select ID,I2,min(DATE) DATE from tb GROUP BY ID,I2) T2 ON TB.ID = T2.ID AND TB.DATE = T2.DATE
LEFT JOIN (select ID,I3,min(DATE) DATE from tb GROUP BY ID,I3) T3 ON TB.ID = T3.ID AND TB.DATE = T3.DATE
drop table tb
/*
ID DATE I1 I2 I3 I4 I5
-- ----- ----- ----- ----- ------------------------------ -------------
A1 02/06 (857)
A1 06/11
A1 08/02 (855) (857) (855,857)
A1 08/05 (851) (874) (851,857),(851,874),(857,874) (851,857,874)
A1 10/21 (873) (855,857),(855,873),(857,873) (855,857,873)
A1 10/25 (855,857),(855,873),(857,873) (855,857,873)
（所影响的行数为 6 行）
*/
[解决办法]
/*
不用改代码，把原始数据将(855,857)挪到I4就行。
*/
declare @A table(ID varchar(2),DATE varchar(5),I1 varchar(5),I2 varchar(5),I3 varchar(9),I4 varchar(29),I5 varchar(13))
insert @A
select 'A1 ', '02/06 ', '(857) ',null,null,null,null union all
select 'A1 ', '06/11 ', '(857) ',null,null,null,null union all
select 'A1 ', '08/02 ', '(855) ', '(857) ',null, '(855,857) ',null union all
select 'B1 ', '08/05 ', '(851) ', '(857) ', '(874) ', '(851,857),(851,874),(857,874) ', '(851,857,874) ' union all
select 'B1 ', '10/21 ', '(855) ', '(857) ', '(873) ', '(855,857),(855,873),(857,873) ', '(855,857,873) ' union all
select 'B1 ', '10/25 ', '(855) ', '(857) ', '(873) ', '(855,857),(855,873),(857,873) ', '(855,857,873) '
declare @TMP table(ID varchar(2),DATE varchar(5),I varchar(100))
insert @TMP
select ID,DATE,I1 from @A where I1 is not null union all
select ID,DATE,I2 from @A where I2 is not null union all
select ID,DATE,I3 from @A where I3 is not null union all
select ID,DATE,I4 from @A where I4 is not null union all
select ID,DATE,I5 from @A where I5 is not null
order by ID,DATE
update a set a.I=replace(a.I,b.I, ' ') from @TMP a join @TMP b on a.ID=b.ID where len(a.I)> len(b.I) and charindex(b.I, a.I)> 0
while exists (select 1 from @TMP where charindex( ',, ',I)> 0) update @TMP set I=replace(I, ',, ', ', ') where charindex( ',, ',I)> 0
update @TMP set I=right(I,len(I)-1) where left(I,1)= ', '
declare @B table(ID varchar(2),DATE varchar(5),I1 varchar(5),I2 varchar(5),I3 varchar(9),I4 varchar(29),I5 varchar(13))
insert @B select a.ID,a.DATE,
I1=case when charindex(b.I,a.I1)> 0 then b.I end,
I2=case when charindex(b.I,a.I2)> 0 then b.I end,
I3=case when charindex(b.I,a.I3)> 0 then b.I end,
I4=case when charindex(b.I,a.I4)> 0 then b.I end,
I5=case when charindex(b.I,a.I5)> 0 then b.I end
from @A a left join
(select * from @TMP a where DATE=(select top 1 DATE from @TMP where ID=a.ID and I=a.I order by DATE)) b
on a.ID=b.ID and a.DATE=b.DATE
select a.ID,a.DATE,
I1=isnull((select I1 from @B where ID=a.ID and DATE=a.DATE and I1 is not null), ' '),
I2=isnull((select I2 from @B where ID=a.ID and DATE=a.DATE and I2 is not null), ' '),
I3=isnull((select I3 from @B where ID=a.ID and DATE=a.DATE and I3 is not null), ' '),
I4=isnull((select I4 from @B where ID=a.ID and DATE=a.DATE and I4 is not null), ' '),
I5=isnull((select I5 from @B where ID=a.ID and DATE=a.DATE and I5 is not null), ' ')
from @A a
/*
A102/06(857)
A106/11
A108/02(855)(855,857)
B108/05(851)(857)(874)(851,857),(851,874),(857,874)(851,857,874)
B110/21(855)(873)(855,857),(855,873),(857,873)(855,857,873)
B110/25
*/
