select
Name,
sum(Money(应该交)) as Money(应该交的总和),
sum(Money(实际交的总和)) as Money(实际交的总和),
sum(Money(应该交)) - sum(Money(实际交的总和)) as Money(欠的总和)
from
表
group by
Name
Name,
sum(Money(应该交)) as Money(应该交的总和),
sum(Money(实际交的总和)) as Money(实际交的总和),
sum(Money(应该交)) - sum(Money(实际交的总和)) as Money(欠的总和)
from
表
group by
Name
nvl(sum(Money(应该交)),0) Money(应该交的总和),
nvl(sum(Money(实际交)),0) Money(实际交的总和).
nvl(sum(Money(应该交)),0)-nvl(sum(Money(实际交)),0) Money(欠的总和)
from (select * from 表1,表2 where 表1.Bumen=表2.Bumen)
group by name;
b.Name,
sum(a.Money(应该交)) as Money(应该交的总和),
sum(a.Money(实际交的总和)) as Money(实际交的总和),
sum(a.Money(应该交)) - sum(a.Money(实际交的总和)) as Money(欠的总和)
from
表1 a , 表2 b
where
a.BuMen = b.BuMen
group by
b.Name
-----------------------------
select b.name 部门,
a.应缴合计
a.实缴合计
(a.应缴合计-a.实缴合计) 欠款合计
from (select bumen,nvl(sum(应缴),0) 应缴合计,nvl(sum(实缴),0) 实缴合计
from table1
group by bumen) a,
table2 b
where a.bumen=b.bumen
b.Name,
sum(a.Money(应该交)) as Money(应该交的总和),
sum(a.Money(实际交的总和)) as Money(实际交的总和),
sum(a.Money(应该交)) - sum(a.Money(实际交的总和)) as Money(欠的总和)
from
表1 a , 表2 b
where
a.BuMen = b.BuMen and (a.Time between 日期1 and 日期2)
group by
b.Name
--try 没测试
select b.name,a.sum(应该交) 应该交,a.sum(实际交) 实际交,a.sum(应该交-实际交) 欠的总和
from 表1 a,表2 b
where a.bumen=b.bumen and a.time between '2004-1-5' and '2004-1-12';
from 表1 a,表2 b
where a.bumen=b.bumen and a.time between '2004-1-5' and '2004-1-12'
group by a.bumen;
nvl(sum(Money(应该交)),0) Money(应该交的总和),
nvl(sum(Money(实际交)),0) Money(实际交的总和).
nvl(sum(Money(应该交)),0)-nvl(sum(Money(实际交)),0) Money(欠的总和)
from 表1 a ,表2 b where a.Bumen=b.Bumen)
group by b.Bumen;