我有个语句不知道怎么实现,请各位帮帮忙
语句要求:要求把下面两句话合并成一句,条件的差别只有一个,就是flag字段。
select sum(a.TOTALFEE) as TOTALFEE,d4.username as username,count(a.flag) as flag
from biz_bill_compaplist a
LEFT OUTER JOIN BIZ_SYS_COMP d0 ON a.COMPID = d0.COMPID
LEFT OUTER JOIN BIZ_SYS_DD d3 ON to_char(d0.CITYCODE) = d3.LISTVALUE AND d3.LISTKEY = 'COMP_CITYCODE'
LEFT OUTER JOIN biz_sys_ap d4 ON a.userid = d4.userid where a.flag=0
group by username,flag
------------------------------------------------
select sum(b.TOTALFEE) as TOTALFEE2,d4.username as username,count(b.flag) as flag2
from biz_bill_compaplist b
LEFT OUTER JOIN BIZ_SYS_COMP d0 ON a.COMPID = d0.COMPID
LEFT OUTER JOIN BIZ_SYS_DD d3 ON to_char(d0.CITYCODE) = d3.LISTVALUE AND d3.LISTKEY = 'COMP_CITYCODE'
LEFT OUTER JOIN biz_sys_ap d4 ON a.userid = d4.userid where b.flag=1
group by username,flag
语句要求:要求把下面两句话合并成一句,条件的差别只有一个,就是flag字段。
select sum(a.TOTALFEE) as TOTALFEE,d4.username as username,count(a.flag) as flag
from biz_bill_compaplist a
LEFT OUTER JOIN BIZ_SYS_COMP d0 ON a.COMPID = d0.COMPID
LEFT OUTER JOIN BIZ_SYS_DD d3 ON to_char(d0.CITYCODE) = d3.LISTVALUE AND d3.LISTKEY = 'COMP_CITYCODE'
LEFT OUTER JOIN biz_sys_ap d4 ON a.userid = d4.userid where a.flag=0
group by username,flag
------------------------------------------------
select sum(b.TOTALFEE) as TOTALFEE2,d4.username as username,count(b.flag) as flag2
from biz_bill_compaplist b
LEFT OUTER JOIN BIZ_SYS_COMP d0 ON a.COMPID = d0.COMPID
LEFT OUTER JOIN BIZ_SYS_DD d3 ON to_char(d0.CITYCODE) = d3.LISTVALUE AND d3.LISTKEY = 'COMP_CITYCODE'
LEFT OUTER JOIN biz_sys_ap d4 ON a.userid = d4.userid where b.flag=1
group by username,flag
TOTALFEE USERNAME FLAG
1 30 企业名称 5
2 40 企业信息 7
第二句的结果
TOTALFEE2 USERNAME FLAG2
1 30 企业名称 6
2 40 企业信息 6
3 40 测试 6
要求合并后的结果:
TOTALFEE USERNAME FLAG2 FLAG
1 60 企业名称 6 6
2 80 企业信息 6 7
3 40 测试 6 0
from biz_bill_compaplist a
LEFT OUTER JOIN BIZ_SYS_COMP d0 ON a.COMPID = d0.COMPID
LEFT OUTER JOIN BIZ_SYS_DD d3 ON to_char(d0.CITYCODE) = d3.LISTVALUE AND d3.LISTKEY = 'COMP_CITYCODE'
LEFT OUTER JOIN biz_sys_ap d4 ON a.userid = d4.userid where a.flag=0
group by username