如何得到如下结果
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
/*
abc
1ab
2ac
4dv
*/
[解决办法]
是不是都考了？

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

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

select *
from #tmp as t
where a=(select min(a) from #tmp where b=t.b and c=t.c)
[解决办法]
wen1818(冷月孤心) ( ) 信誉：100 Blog 2007-03-22 15:18:39 得分: 0


group by不好的 我需要取20个字段的

那样要group by后面跟20个阿


---------

那就不能直接group by了

如果a是不重的，也可以

Select * From #tmp Where a In (Select Min(a) From #tmp Group By b, c)