实现功能
"select a.编码,a.名称,(select sum(金额) from b where b.编码=a.编码) as 金额1,(select sum(金额) from c where c.编码=a.编码) as 金额2 from a"
我应该如何实现对嵌套sql的加减运算呢,就是上面的语句,我想在体现个金额3,金额3=金额1-金额2,怎么在一天语句中实现呢,有什么好方法没?
"select a.编码,a.名称,(select sum(金额) from b where b.编码=a.编码) as 金额1,(select sum(金额) from c where c.编码=a.编码) as 金额2 from a"
我应该如何实现对嵌套sql的加减运算呢,就是上面的语句,我想在体现个金额3,金额3=金额1-金额2,怎么在一天语句中实现呢,有什么好方法没?
(select sum(金额) from b where b.编码=a.编码) as 金额1,
(select sum(金额) from c where c.编码=a.编码) as 金额2,
(select sum(金额) from b where b.编码=a.编码)-(select sum(金额) from c where c.编码=a.编码) as 金额2
from a
select a.编码,a.名称,
(select sum(金额) from b where b.编码=a.编码) as 金额1,
(select sum(金额) from c where c.编码=a.编码) as 金额2,
(select sum(金额) from b where b.编码=a.编码)-(select sum(金额) from c where c.编码=a.编码) as 金额3
from a
SELECT a.编码, a.名称, 金额3=ISNULL(SUM(b.金额),0)-ISNULL(SUM(a.金额),0)
FROM a
LEFT JOIN b
ON b.编码 = a.编码
LEFT JOIN c
ON c.编码 = a.编码
GROUP BY a.编码,a.名称
select a.编码,a.名称,sum(b.金额) as 金额1,sum(c.金额) as 金额2,
sum(isnull(b.金额,0))- sum(isnull(c.金额,0)) as 金额3
from a
left join b on b.编码=a.编码
left join c on c.编码=a.编码
group by a.编码,a.名称
from zyc_hzjbb a left join zyc_hzyjj b on b.住院号=a.住院号 left join zyc_cfk c on c.住院号=a.住院号
group by a.住院号
order by a.住院号住院号 (无列名) (无列名) (无列名)
0100043 8637000.00 286344.70 8350655.30
0100062 560657.92 18768.03 541889.89
0100373 255000.00 22211.30 232788.70
0100468 891000.00 3929.75 887070.25
0100510 NULL 8857.02 -8857.02
0101079 306000.00 10185.59 295814.41
0101270 1170000.00 8294.95 1161705.05
0101914 211000.00 2828.89 208171.11
0202285 48500.00 5828.97 42671.03
0202549 2424800.00 77671.56 2347128.44我按照上面写的,结果出来不对,这个住院号的,金额1仅为3000
b表数据
住院号 预交款
0100043 3000.00
0100043 -350.00
0100043 350.00
0100043 -3000.00
0100043 3000.00
c表数据,sum后的
住院号 (无列名)
0100043 57268.94
from zyc_hzjbb a left join zyc_hzyjj b on b.住院号=a.住院号 left join zyc_cfk c on c.住院号=a.住院号
group by a.住院号
order by a.住院号
这个语句和这种方式统计出来对不上
select 住院号,(select isnull(SUM(预交款),0) from zyc_hzyjj where 住院号=zyc_hzjbb.住院号) as 押金总额,(select isnull(SUM(处方金额),0) from zyc_cfk where 住院号=zyc_hzjbb.住院号) as 处方金额,
((select isnull(SUM(预交款),0) from zyc_hzyjj where 住院号=zyc_hzjbb.住院号)-(select isnull(SUM(处方金额),0) from zyc_cfk where 住院号=zyc_hzjbb.住院号)) as 押金余额
from zyc_hzjbb
order by 住院号
from (select sum(金额) as 金额1 from b) as a1,(select sum(金额) as 金额2 from c) as a2,a
where a.编码=a1.编码 and a.编码=a2.编码)
select 编码,编码,金额1,金额2,金额1-金额2 as 金额3 from(
select a.编码,a.编码,(select sum(金额) from b where b.编码=a.编码) as 金额1,
(select sum(金额) from c where c.编码=a.编码) as 金额2 from a) t
((select isnull(SUM(预交款),0) from zyc_hzyjj where 住院号=zyc_hzjbb.住院号)-(select isnull(SUM(处方金额),0) from zyc_cfk where 住院号=zyc_hzjbb.住院号)) as 押金余额
from zyc_hzjbb group by 住院号
order by 住院号
这样两种的结果应该是一样的了。