在SQL SERVER 2005中的联合查询出来的数据有重叠数据能否将其合并。
a 表有
id name
1 frank
2 jpst
3 frnkme
b表有
id fid other
1 1 llll
2 1 kkjfkl
联合查询(select a.id,a.name,b.other from a join b on a.id=b.fid)后的数据为
a.id,a.name,b.other
1 frank llll
1 frank kkjfkl
我只相要第一条但不要用top用SQL语句。
a 表有
id name
1 frank
2 jpst
3 frnkme
b表有
id fid other
1 1 llll
2 1 kkjfkl
联合查询(select a.id,a.name,b.other from a join b on a.id=b.fid)后的数据为
a.id,a.name,b.other
1 frank llll
1 frank kkjfkl
我只相要第一条但不要用top用SQL语句。
from (select min(id)id,name from a group by name)a
join b on a.id=b.fid
select 1 as id, 'frank' as name into #tb_A
union all select 2,'jpst'
union all select 3,'frnkme' select 1 as id, 1 as fid, 'llll' as other into #tb_B
union all select 2,1,'kkjfkl'
-- 测试
select a.id,a.name,b.other
from #tb_A a
join (select fid,max(other) as other from #tb_B group by fid) b
on a.id=b.fid-- 结果
/*
id name other
----------- ------ ------
1 frank llll(1 行受影响)*/