inner join 重复记录如何处理。
这是现在用inner join连接两个表查询出来的数据：
bhxmgzclgzz
123丽丽 118.595a
123丽丽 118.595b
123丽丽 118.595c
456峰 3004.81548a
456峰 3004.81548b
456峰 3004.81548c
456峰 3004.81548d
这是表a：
bhxmgzz
123丽丽 a
123丽丽 b
123丽丽 c
456峰 a
456峰 b
456峰 c
456峰 d
这是表b
bh xm gz cl
123丽丽 118.595
456峰 3004.81548
我现在想要这样的结果：
bhxmgzclgzz
123丽丽 118.595a
123丽丽 b
123丽丽 c
456峰 3004.81548a
456峰 b
456峰 c
456峰 d
不知道怎么实现，谢谢指教！
[解决办法]
select a.bh,a.xm,(case when a.gzz= 'a ' then b.gz else ' ' end ) as gz, (case when a.gzz= 'a ' then b.cl else ' ' end ) as cl,a.gzz
from a inner join b
on a.bh=b.bh
[解决办法]
如果gzz列不固定。可以这样
select a.bh,a.xm,(case when not exists (select 1 from a c where a.gzz> c.gzz) then b.gz else ' ' end ) as gz, (case when not exists (select 1 from a c where a.gzz> c.gzz) then b.cl else ' ' end ) as cl,a.gzz
from a inner join b
on a.bh=b.bh
[解决办法]
select b.*,a1.gzz from a a1,b
where a1.bh=b.bh
and not exists (select 1 from a
where bh=a1.bh and gzz <a1.gzz
)
union all
select bh,xm,null as gz,null as cl,gzz
from a a2
and exists (select 1 from a
where bh=a2.bh and gzz <a2.gzz
)
