select a.hw,a.huowname,c.sshje as '现金',d.sshje as '医保金额',b.sshje as '医保欠账金额'
from huoweizl a,
(select a.hw,sum(a.sshje) as sshje /*此条语句查询医保欠账金额*/
from retmxls a,retjels b,retmast c
where a.lshh=b.lshh
and a.lshh=c.lshh
and b.jsfsid='DJS00000014'
and c.yibaoid<>''
and a.rq>='2008-04-03'
group by a.hw) b,
(select a.hw,sum(b.sshje) as sshje /*此条语句查询现金收款金额*/
from retmxls a,retjels b
where a.lshh=b.lshh
and b.jsfsid='DJS00000001'
group by a.hw) c,
(select a.hw,sum(a.sshje) as sshje /*查询医保收款额,不包括欠账金额*/
from retmxls a,retjels b,retmast c
where a.lshh=b.lshh
and a.lshh=c.lshh
and b.jsfsid='DJS00000014'
and c.yibaoid=''
and a.rq>='2008-04-03'
group by a.hw) d
where a.hw=b.hw
and a.hw=c.hw
and a.hw=d.hw
店编码 店名 现金 医保 医保欠账
HWI00000001 店1 60.00 3.00 90.00 我这里还有9家店,但是在现金、医保和欠账中的合计数量为0时都无法显示,如何才能让他们为零
时自动显示为0,而且可显示其他的合计金额
谢谢
from huoweizl a,
(select a.hw,sum(a.sshje) as sshje /*此条语句查询医保欠账金额*/
from retmxls a,retjels b,retmast c
where a.lshh=b.lshh
and a.lshh=c.lshh
and b.jsfsid='DJS00000014'
and c.yibaoid<>''
and a.rq>='2008-04-03'
group by a.hw) b,
(select a.hw,sum(b.sshje) as sshje /*此条语句查询现金收款金额*/
from retmxls a,retjels b
where a.lshh=b.lshh
and b.jsfsid='DJS00000001'
group by a.hw) c,
(select a.hw,sum(a.sshje) as sshje /*查询医保收款额,不包括欠账金额*/
from retmxls a,retjels b,retmast c
where a.lshh=b.lshh
and a.lshh=c.lshh
and b.jsfsid='DJS00000014'
and c.yibaoid=''
and a.rq>='2008-04-03'
group by a.hw) d
where a.hw=b.hw
and a.hw=c.hw
and a.hw=d.hw
店编码 店名 现金 医保 医保欠账
HWI00000001 店1 60.00 3.00 90.00 我这里还有9家店,但是在现金、医保和欠账中的合计数量为0时都无法显示,如何才能让他们为零
时自动显示为0,而且可显示其他的合计金额
谢谢
select a.hw,a.huowname,c.sshje as '现金',d.sshje as '医保金额',b.sshje as '医保欠账金额'
from huoweizl a,
(select a.hw,isnull(sum(a.sshje),0)sshje as sshje /*此条语句查询医保欠账金额*/
from retmxls a,retjels b,retmast c
where a.lshh=b.lshh
and a.lshh=c.lshh
and b.jsfsid='DJS00000014'
and c.yibaoid <>''
and a.rq>='2008-04-03'
group by a.hw) b,
(select a.hw,isnull(sum(b.sshje),0)sshje as sshje /*此条语句查询现金收款金额*/
from retmxls a,retjels b
where a.lshh=b.lshh
and b.jsfsid='DJS00000001'
group by a.hw) c,
(select a.hw,isnull(sum(a.sshje),0)sshje as sshje /*查询医保收款额,不包括欠账金额*/
from retmxls a,retjels b,retmast c
where a.lshh=b.lshh
and a.lshh=c.lshh
and b.jsfsid='DJS00000014'
and c.yibaoid=''
and a.rq>='2008-04-03'
group by a.hw) d
where a.hw=b.hw
and a.hw=c.hw
and a.hw=d.hw
from huoweizl a,
(select a.hw,isnull(sum(a.sshje),0) as sshje /*此条语句查询医保欠账金额*/
from retmxls a,retjels b,retmast c
where a.lshh=b.lshh
and a.lshh=c.lshh
and b.jsfsid='DJS00000014'
and c.yibaoid <>''
and a.rq>='2008-04-03'
group by a.hw) b,
(select a.hw,isnull(sum(b.sshje),0) as sshje /*此条语句查询现金收款金额*/
from retmxls a,retjels b
where a.lshh=b.lshh
and b.jsfsid='DJS00000001'
group by a.hw) c,
(select a.hw,isnull(sum(a.sshje),0) as sshje /*查询医保收款额,不包括欠账金额*/
from retmxls a,retjels b,retmast c
where a.lshh=b.lshh
and a.lshh=c.lshh
and b.jsfsid='DJS00000014'
and c.yibaoid=''
and a.rq>='2008-04-03'
group by a.hw) d
where a.hw=b.hw
and a.hw=c.hw
and a.hw=d.hw
--楼主 是想要这样?select a.hw,a.huowname,c.sshje as '现金',d.sshje as '医保金额',b.sshje as '医保欠账金额'
from huoweizl a
left join (select a.hw,isnull(sum(a.sshje),0)sshje as sshje /*此条语句查询医保欠账金额*/
from retmxls a,retjels b,retmast c
where a.lshh=b.lshh
and a.lshh=c.lshh
and b.jsfsid='DJS00000014'
and c.yibaoid <>''
and a.rq>='2008-04-03'
group by a.hw) b on a.hw=b.hw
left join (select a.hw,isnull(sum(b.sshje),0)sshje as sshje /*此条语句查询现金收款金额*/
from retmxls a,retjels b
where a.lshh=b.lshh
and b.jsfsid='DJS00000001'
group by a.hw) c on a.hw=c.hw
left join (select a.hw,isnull(sum(a.sshje),0)sshje as sshje /*查询医保收款额,不包括欠账金额*/
from retmxls a,retjels b,retmast c
where a.lshh=b.lshh
and a.lshh=c.lshh
and b.jsfsid='DJS00000014'
and c.yibaoid=''
and a.rq>='2008-04-03'
group by a.hw) d on a.hw=d.hw
from huoweizl a
left join (select a.hw,isnull(sum(a.sshje),0)sshje
from retmxls a,retjels b,retmast c
where a.lshh=b.lshh
and a.lshh=c.lshh
and b.jsfsid='DJS00000014'
and c.yibaoid <>''
and a.rq>='2008-04-03'
group by a.hw) b on a.hw=b.hw
left join (select a.hw,isnull(sum(b.sshje),0)sshje
from retmxls a,retjels b
where a.lshh=b.lshh
and b.jsfsid='DJS00000001'
group by a.hw) c on a.hw=c.hw
left join (select a.hw,isnull(sum(a.sshje),0)sshje
from retmxls a,retjels b,retmast c
where a.lshh=b.lshh
and a.lshh=c.lshh
and b.jsfsid='DJS00000014'
and c.yibaoid=''
and a.rq>='2008-04-03'
group by a.hw) d on a.hw=d.hw