2.1w+
社区成员
- 主页
求一SQL语句
语句如下：
select dh,Savedata as a1,'' as a2 from savetable where savename='A'
union all
select dh,'' as A1,Savedata as a2 from savetable where savename='B'
假设得到的返回结果为：
DH a1 a2
001 A-500 空格 注：第一条语句返回 每个DH只会有一条记录
001 空格 B-300 注：第二条语句返回 每个DH只会有一条记录
我想实现的结果为
DH A1 A2
001 A-500 B-300
如何使单号相同，数据自动摆在一起。
select dh,Savedata as a1,'' as a2 from savetable where savename='A'
union all
select dh,'' as A1,Savedata as a2 from savetable where savename='B'
假设得到的返回结果为：
DH a1 a2
001 A-500 空格 注：第一条语句返回 每个DH只会有一条记录
001 空格 B-300 注：第二条语句返回 每个DH只会有一条记录
我想实现的结果为
DH A1 A2
001 A-500 B-300
如何使单号相同，数据自动摆在一起。
...全文
请发表友善的回复…
发表回复
rmini 2011-10-20
需求真是多样化啊，收藏了
中国风 2011-10-20
直接用case when +Max
楼主语句改为
楼主语句改为
select dh,max(a1) as a1,max(a2) as a2
from (select dh,Savedata as a1,'' as a2 from savetable where savename='A'
union all
select dh,'' as A1,Savedata as a2 from savetable where savename='B')t
group by dh中国风 2011-10-20
select dh,max(case when savename = 'A' then Savedata end) as a1,
max(case when savename = 'B' then Savedata end) as a2
from savetable
where savename in('A','B')
group by dhykwang 2011-10-20
问题是 这个不是数值呢。 不能使用 sum
唐诗三百首 2011-10-20
select dh,sum(a1) 'A1',sum(a2) 'A2'
from
(select dh,Savedata as a1,0 as a2 from savetable where savename='A'
union all
select dh,0 as A1,Savedata as a2 from savetable where savename='B'
) t
group by dh