如何得到如下结果
create   table   #tmp
(
      a   int,
      b   varchar(50),
      c   varchar(50)
)

insert   into   #tmp   (a,b,c)
select   1, 'a ', 'b '   union
select   2, 'a ', 'c '   union
select   3, 'a ', 'b '   union
select   4, 'd ', 'v '   union
select   5, 'd ', 'v '

select   *   from   #tmp  


怎样才能得到

1   a   b
2   a   c
4   d   v      

就是字段b，c值同时一样的只取一个，a字段必须得取

数据量很大的，不用存储过程怎么处理

最好不要写成这样
select   b,c,(select   top   1   a   from   #tmp   where   b=p.b   and   c=p.c)   from   #tmp   p   group   by   b,c




------解决方案--------------------
select * from #tmp t where not exists(select 1 from #tmp where a <t.a and b=t.b and c=t.c)
------解决方案--------------------
select * from #tmp _t
where not exists(select 1 from #tmp where _t.b = b and _t.c = c and _t.a > a)
------解决方案--------------------
select *
from #tmp as t
where a=(select min(a) from #tmp where b=t.b and c=t.c)

------解决方案--------------------
declare @t table(a int,b varchar(8),c varchar(8))

insert into @t (a,b,c)
select 1, 'a ', 'b ' union
select 2, 'a ', 'c ' union
select 3, 'a ', 'b ' union
select 4, 'd ', 'v ' union
select 5, 'd ', 'v '

select * from @t t where not exists(select 1 from @t where a <t.a and b=t.b and c=t.c)

/*
a b c
----------- -------- --------
1 a b
2 a c
4 d v
*/
------解决方案--------------------
是不是都考慮複雜了？

Select
Min(a) As a, b, c
From
#tmp
Group By b,c
------解决方案--------------------
SELECT * FROM #TMP I
WHERE 1> (SELECT COUNT(*) FROM #TMP J WHERE I.B=J.B AND I.C=J.C AND I.A> J.A)
------解决方案--------------------

create table #tmp
(
a int,
b varchar(50),
c varchar(50)
)

insert into #tmp (a,b,c)
select 1, 'a ', 'b ' union
select 2, 'a ', 'c ' union
select 3, 'a ', 'b ' union
select 4, 'd ', 'v ' union
select 5, 'd ', 'v '

Select
Min(a) As a, b, c
From
#tmp
Group By b,c

drop table #tmp
--Result
/*
a b c
1 a b
2 a c
4 d v
*/
------解决方案--------------------
是不是都考慮複雜了？

Select
Min(a) As a, b, c
From
#tmp
Group By b,c

真是这样！
------解决方案--------------------
--呵呵，最简单的


Select Min(a) As a, b, c
From #tmp
Group By b,c

------解决方案--------------------