如何得到如下结果
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)
