哦 明白了 select a.* from biao as a where exist (select top 1 * from biao as b where a.id =b.id group by c.id order by c.id )这样?
declare @t table (id int, t_1 int, t_2 int)insert into @t select 1, 1, 1 union all select 1, 2, 2 union all select 2, 3, 3 union all select 2, 4, 4 union all select 2, 5, 5 union all select 3, 6, 6 union all select 3, 7, 7select distinct id, (select top 1 t_1 from @t where id = t.id) as t_1, (select top 1 t_2 from @t where id = t.id) as t_2 from @t t
declare @t table (id int, t1 int, t2 int) insert @t values(1, 1, 1) insert @t values(1, 2, 2) insert @t values(2, 3, 3) insert @t values(2, 4, 4) insert @t values(2, 5, 5) insert @t values(3, 6, 6) insert @t values(3, 7, 7)if object_id('tempdb..#t') is not null drop table tempdb..#t select identity(int,1,1)as i,* into #t from @tselect id,t1,t2 from #t where i in(select min(i) as i from #t group by id) id t1 t2 ----------- ----------- ----------- 1 1 1 2 3 3 3 6 6(所影响的行数为 3 行)
create table #ta1 ([ID] int, t_1 int, t_2 int ) insert into #ta1 select 1,1,1 union all select 1,2,2 union all select 2,3,3 union all select 2,4,4 union all select 2,5,5 union all select 3,6,6 union all select 3,7,7select A.[ID],A.t_1,A.t_2 from #ta1 A where not exists(select * from #ta1 B where A.[ID]=B.[ID] and B.t_1<A.t_1)drop table #ta1
哦 明白了 select a.* from biao as a where exist (select top 1 * from biao as b where a.id =b.id group by c.id order by c.id )这样? 怎么有C的??
create table #ta1 ([ID] int, t_1 int, t_2 int ) insert into #ta1 select 1,1,1 union all select 1,2,2 union all select 2,3,3 union all select 2,4,4 union all select 2,5,5 union all select 3,6,6 union all select 3,7,7select A.[ID],A.t_1,A.t_2 from #ta1 A where not exists(select * from #ta1 B where A.[ID]=B.[ID] and B.t_1<A.t_1)用这个方法试试!
我只是当ID相同时,取此ID相同的一条..别的记录还要取啊
ID t_1 t_2
1 1 1
1 2 2
2 3 3
2 4 4
2 5 5
3 6 6
3 7 7要得到的是
1 1 1
2 3 3
3 6 6
这样的记录
select a.* from biao as a where exist (select top 1 * from biao as b where a.id =b.id group by c.id order by c.id )这样?
select 1, 1, 1
union all select 1, 2, 2
union all select 2, 3, 3
union all select 2, 4, 4
union all select 2, 5, 5
union all select 3, 6, 6
union all select 3, 7, 7select distinct id,
(select top 1 t_1 from @t where id = t.id) as t_1,
(select top 1 t_2 from @t where id = t.id) as t_2
from @t t
insert @t values(1, 1, 1)
insert @t values(1, 2, 2)
insert @t values(2, 3, 3)
insert @t values(2, 4, 4)
insert @t values(2, 5, 5)
insert @t values(3, 6, 6)
insert @t values(3, 7, 7)if object_id('tempdb..#t') is not null
drop table tempdb..#t
select identity(int,1,1)as i,* into #t from @tselect id,t1,t2 from #t where i in(select min(i) as i from #t group by id)
id t1 t2
----------- ----------- -----------
1 1 1
2 3 3
3 6 6(所影响的行数为 3 行)
([ID] int,
t_1 int,
t_2 int
)
insert into #ta1
select 1,1,1
union all
select 1,2,2
union all
select 2,3,3
union all
select 2,4,4
union all
select 2,5,5
union all
select 3,6,6
union all
select 3,7,7select A.[ID],A.t_1,A.t_2 from #ta1 A
where not exists(select * from #ta1 B where A.[ID]=B.[ID] and B.t_1<A.t_1)drop table #ta1
select a.* from biao as a where exist (select top 1 * from biao as b where a.id =b.id group by c.id order by c.id )这样?
怎么有C的??
([ID] int,
t_1 int,
t_2 int
)
insert into #ta1
select 1,1,1
union all
select 1,2,2
union all
select 2,3,3
union all
select 2,4,4
union all
select 2,5,5
union all
select 3,6,6
union all
select 3,7,7select A.[ID],A.t_1,A.t_2 from #ta1 A
where not exists(select * from #ta1 B where A.[ID]=B.[ID] and B.t_1<A.t_1)用这个方法试试!