--测试环境
declare @表 table(id int identity(1,1),name varchar(10),age int)
insert into @表 select 'james',20
union all select 'lee',30
union all select 'jack',23
union all select 'james',25--查询
select * from @表 A
where not exists
(select 1 from @表 where name=A.name and Id<A.id)
--结果
id name age
----------- ---------- -----------
1 james 20
2 lee 30
3 jack 23(所影响的行数为 3 行)
declare @表 table(id int identity(1,1),name varchar(10),age int)
insert into @表 select 'james',20
union all select 'lee',30
union all select 'jack',23
union all select 'james',25--查询
select * from @表 A
where not exists
(select 1 from @表 where name=A.name and Id<A.id)
--结果
id name age
----------- ---------- -----------
1 james 20
2 lee 30
3 jack 23(所影响的行数为 3 行)
select * from a t,(select min(id),name from a group by name) x where t.id=x.id and t.name=x.name
insert into @表 select 'james',20
union all select 'lee',30
union all select 'jack',23
union all select 'james',25select * from @表 a
where not exists(select 1 from @表 where name=a.name and id<a.id)
可惜,都有问题啊,怪。完整的表结构:
表a:
id name age tag code
1 james 20 1 1001
2 lee 30 1 2003
3 jack 23 1 3008
4 james 25 1 1003
5 tom 50 0 9002想得到:
1 james
2 lee
3 jack即:重复的james 只显示一个,tag=0 以及 code 第一位是9的不显示,如何实现?
insert into @表 select 'james',20,1,'1001'
union all select 'lee',30,1,'2003'
union all select 'jack',23,1,'3008'
union all select 'james',25,1,'1003'
union all select 'tom',50,0,'9003'select min(id) as id,name
from
@表
where tag<>0 or left(code,1)<>'9'
group by name
order by id