SELECT *
from
T a
where
not exists(select 1 from T where left(tname,charindex(',',tname))=left(a.tname,charindex(',',a.tname)) and ID>a.ID)
SELECT *
from
T a
where
not exists(select 1 from T where left(tname,charindex(',',tname))=left(a.tname,charindex(',',a.tname)) and ID<a.ID)
from table a
where not exists(select 1 from table where left(tname,4) = left(a.tname,4) and id < a.id)
declare @t table(id int,tname varchar(50))
insert into @t select 10,'降魔录_sonyericsson-W958C系列游戏'
insert into @t select 11,'降魔录_Nokia-7610系列游戏'
insert into @t select 12,'降魔录_Nokia-N73系列游戏 '
insert into @t select 13,'降33魔录_Nokia-N73系列游戏 '
insert into @t select 14,'降33魔录_Nokia-N73系列游戏 '
insert into @t select 15,'降33魔录_Nokia-N73系列游戏 'select * from @t a
where not exists
(select 1 from @t where id>a.id and
substring(tname,1,charindex('_',tname)-1)=substring(a.tname,1,charindex('_',a.tname)-1))
或:
declare @t table(id int,tname varchar(50))
insert into @t select 10,'降魔录_sonyericsson-W958C系列游戏'
insert into @t select 11,'降魔录_Nokia-7610系列游戏'
insert into @t select 12,'降魔录_Nokia-N73系列游戏 '
insert into @t select 13,'降33魔录_Nokia-N73系列游戏 '
insert into @t select 14,'降33魔录_Nokia-N73系列游戏 '
insert into @t select 15,'降33魔录_Nokia-N73系列游戏 'select * from @t a
where not exists
(select 1 from @t where id<a.id and
substring(tname,1,charindex('_',tname)-1)=substring(a.tname,1,charindex('_',a.tname)-1))
SQL code
SELECT *
from
T a
where
not exists(select 1 from T where left(tname,charindex('_',tname))=left(a.tname,charindex('_',a.tname)) and ID>a.ID)
SELECT *
from
T a
where
not exists(select 1 from T where left(tname,charindex('_',tname))=left(a.tname,charindex('_',a.tname)) and ID<a.ID)
id tname
10 降魔录_sonyericsson-W958C系列游戏
11 降魔录_Nokia-7610系列游戏
12 降魔录_Nokia-N73系列游戏 select * from tb where id in
(select min(id) from tb group by left(tname,charindex('-')-1))
insert into @t select 10,'降魔录_sonyericsson-W958C系列游戏'
insert into @t select 11,'降魔录_Nokia-7610系列游戏'
insert into @t select 12,'降魔录_Nokia-N73系列游戏 '
insert into @t select 13,'降33魔录_Nokia-N73系列游戏 '
insert into @t select 14,'降33魔录_Nokia-N74系列游戏 '
insert into @t select 15,'降33魔录_Nokia-N75系列游戏 ' select distinct substring(tname,0,charindex('_',tname)) from @t---------------------------------------------
降33魔录
降魔录
insert into #t select 10,'降魔录_sonyericsson-W958C系列游戏'
insert into #t select 11,'降魔录_Nokia-7610系列游戏'
insert into #t select 12,'降魔录_Nokia-N73系列游戏 '
insert into #t select 13,'降33魔录_Nokia-N73系列游戏 '
insert into #t select 14,'降33魔录_Nokia-N74系列游戏 '
insert into #t select 15,'降33魔录_Nokia-N75系列游戏 '
select * from #t where id in (select min(id) from #t group by substring(tname,0,charindex('_',tname)))