求一sql语句?解决思路
求一sql语句?
A表 字段 jydbh ,qk, xj, yj ,ywtc
A01 250 50 0 100
B表 字段 jydbh js tj zl zyf hwbh
A01 1 2.11 0 100 B01
A01 2 1.222 3.11 200 B02
现在要显示如下
hwbh js tj zl zyf qk xj yj ywtc
B01 1 2.11 0 100 250 50 0 100
B02 2 1.222 3.11 200 0 0 0 0
------解决方案--------------------
A表 字段 jydbh ,qk, xj, yj ,ywtc
A01 250 50 0 100
B表 字段 jydbh js tj zl zyf hwbh
A01 1 2.11 0 100 B01
A01 2 1.222 3.11 200 B02
现在要显示如下
hwbh js tj zl zyf qk xj yj ywtc
B01 1 2.11 0 100 250 50 0 100
B02 2 1.222 3.11 200 0 0 0 0
------解决方案--------------------
create table tablea(jydbh varchar(10),qk int,xj int,yj int,ywtc int)
insert into tablea select 'A01',250,50,0,100
create table tableb(jydbh varchar(10),js int,tj decimal(10,4),zl decimal(10,4),zyf int,hwbh varchar(20))
insert into tableb
select 'A01',1,2.11,0,100,'B01'
union all
select 'A01',2,1.222,3.11,200,'B02'
select a.hwbh,a.js,a.tj,a.zl,a.zyf,isnull(b.qk,0) qk,isnull(b.xj,0) xj,isnull(b.yj,0) yj,isnull(b.ywtc,0) ywtc
from tableb a left join tablea b on a.jydbh=b.jydbh and a.hwbh='B01'