表结构如下
ID ZiMu Shuzi
1 A 1
2 B 1
3 C 1
4 A 2
5 B 2
6 C 2
7 A 3
8 B 3
9 C 3要求查询到以下结果ID ZiMu Shuzi
1 A 1
4 A 2
7 A 3
ID ZiMu Shuzi
1 A 1
2 B 1
3 C 1
4 A 2
5 B 2
6 C 2
7 A 3
8 B 3
9 C 3要求查询到以下结果ID ZiMu Shuzi
1 A 1
4 A 2
7 A 3
declare @t table (ID int,ZiMu varchar(1),Shuzi int)
insert into @t
select 1,'A',1 union all
select 2,'B',1 union all
select 3,'C',1 union all
select 4,'A',2 union all
select 5,'B',2 union all
select 6,'C',2 union all
select 7,'A',3 union all
select 8,'B',3 union all
select 9,'C',3select * from @t t where ID=(select min(ID) from @t where Shuzi=t.Shuzi)
/*
ID ZiMu Shuzi
----------- ---- -----------
1 A 1
4 A 2
7 A 3
*/
--其实就是一句话
select * from 你的表名 t
where ID=(select min(ID) from 你的表名 where Shuzi=t.Shuzi)
declare @t table (ID int,ZiMu varchar(1),Shuzi int)
insert into @t
select 1,'A',1 union all
select 2,'B',1 union all
select 3,'C',1 union all
select 4,'A',2 union all
select 5,'B',2 union all
select 6,'C',2 union all
select 7,'A',3 union all
select 8,'B',3 union all
select 9,'C',3select * from @t t
where not exists
(select top 1 * from @t where Shuzi=t.Shuzi and ID<t.ID)
/*
ID ZiMu Shuzi
----------- ---- -----------
1 A 1
4 A 2
7 A 3
*/
谢了,确实是我想要的
还有一个不解的地方
我用以下方法竟然查询出相同的结果Select Top 1 * From biao Where Order By ZiMu本人愚见一直以为top 1 只能查询到一条记录!
在这里竟然也查询到3条记录额
应该是:Select Top 1 * From biao Order By ZiMu
好像只要select * from table where ZiMu='A' order by Shuzi
如果你要得到ZiMu =A的直接where 条件就ok了。