我有以下查询:
select tb1.accountnum, to_char(tb3.month, 'MON, YYYY'),
sum(tb3.amt_due)
from db_table1 tb1, db_table2 tb2, db_table3 tb3
where tb1.acctnum = tb2.acctnum and
tb2.acctcode = tb3.acctcode and
tb3.type_id = 10 and
tb1.status = 'YES'
group by tb1.accountnum, (tb3.month, 'MON, YYYY'),
having sum(tb3.amt_due) < 0;
此查询将分别汇总每个月的应付金额,如果为负数,则返回帐号。例如:
accountnum | Month | Amt_Due
---------- | --------- | ---------
1 | Jan | 15
---------- | --------- | ---------
1 | Jan | -20
---------- | --------- | ---------
1 | Mar | 3
---------- | --------- | ---------
2 | Aug | 13
---------- | --------- | ---------
2 | Dec | -25
---------- | --------- | ---------
2 | Dec | 40
---------- | --------- | ---------
将给出结果:
accountnum | Month | Amt_Due
---------- | --------- | ---------
1 | Jan | -5
---------- | --------- | ---------
我现在想添加一个额外的表格(待定4),并像我们刚才所做的那样对费用进行汇总。请考虑新表中的这些行(tb4)。
accountnum | Month | misc_charge
---------- | --------- | ---------
1 | Jan | -45
---------- | --------- | ---------
1 | Jan | 25
---------- | --------- | ---------
2 | Sep | -7
---------- | --------- | ---------
对指控进行汇总会得到一个结果:
accountnum | Month | misc_charge
---------- | --------- | ---------
1 | Jan | -20
---------- | --------- | ---------
2 | Sep | -7
---------- | --------- | ---------
现在,我想将第一个查询的结果与第二个查询结果相加。因此:
accountnum | Month | Amt_Due
---------- | --------- | ---------
1 | Jan | -5
---------- | --------- | ---------
汇总
accountnum | Month | misc_charge
---------- | --------- | ---------
1 | Jan | -20
---------- | --------- | ---------
2 | Sep | -7
---------- | --------- | ---------
给出最终结果:
accountnum | Month | sum(sum(tb3.amt_due) + sum(tb4.misc_charge))
---------- | --------- | ---------
1 | Jan | -25
---------- | --------- | ---------
2 | Sep | -7
---------- | --------- | ---------
我修改了原始查询以包含tb4,但Oracle给了我错误:ORA-00935:组函数嵌套太深
select tb1.accountnum, to_char(tb3.month, 'MON, YYYY'),
to_char(tb4.month, 'MON, YYY'),
sum(sum(tb3.amt_due) + sum(tb4.misc_charge))
from db_table1 tb1, db_table2 tb2, db_table3 tb3, db_table4 tb4
where tb1.acctnum = tb2.acctnum and
tb2.acctcode = tb3.acctcode and
tb3.acctcode = tb4.acctcode and
tb3.type_id = 10 and
tb1.status = 'YES'
group by tb1.accountnum, to_char(tb3.month, 'MON, YYYY'),
to_char(tb4.month, 'MON, YYY')
having sum(sum(tb3.amt_due) + sum(tb4.misc_charge)) < 0;
有人能帮我把最后一张表包括在内的语法吗?
谢谢!
我认为您需要这样的东西。我还没有用你的数据/表测试过,所以看看这是否有效。
select accountnum, month, sum(amt)
from
(
select tb1.accountnum, to_char(tb3.month, 'MON, YYYY') month,
sum(tb3.amt_due) amt
from db_table1 tb1, db_table2 tb2, db_table3 tb3
where tb1.acctnum = tb2.acctnum and
tb2.acctcode = tb3.acctcode and
tb3.type_id = 10 and
tb1.status = 'YES'
group by tb1.accountnum, (tb3.month, 'MON, YYYY')
union all
select tb4.accountnum, to_char(tb4.month, 'MON, YYYY'),
sum(tb4.misc_charge)
from tb4
group by tb4.accountnum, to_char(tb4.month, 'MON, YYYY')
)
group by accountnum, month
having sum(amt) < 0