假设我们有日志条目表,如下所示
create table jour_entries
(jseq number,
j_date date,
Eseq number,
account_no varchar2(32),
debit number,
credit number,
note varchar2(256) );如何构建最佳性能报告,以便在SQL中进行试算?报表列为
我试过这样的选择:
select account_number
, debit_within_month
, credit_within_month
, debit_till_this_day
, credit_till_this_day
from jour_entries j,
(select account_number, sum(debit) debit_within_month,
sum(credit) credit_within_month
from jour_entries
where j_date between trunc(given_date, 'month') and given_date
group by account_number
) j1,
(select account_number, sum(debit) debit_till_this_day,
sum(credit) credit_till_this_day
from jour_entries
where j_date between trunc(given_date, 'year') and given_date
group by account_number
) j2
wherer j.account_number = j1.account_number
and j.account_number = j2.account_number但我正在寻找其他的解决方案(也许通过使用解析函数),以获得最佳的性能。
发布于 2013-01-18 21:24:31
我将使用SQL*Plus替换变量语法来指示given_month。无论您实际使用的是什么客户端,都需要对此进行更改。
select account_number
, sum ( case when J_date between trunc(&given_date, 'mm')
and least(sysdate, last_day(&given_date))
then debit else 0 end ) as debit_within_month
, sum ( case when J_date between trunc(&given_date, 'mm')
and least(sysdate, last_day(&given_date))
then credit else 0 end ) as credit_within_month
, sum ( case when J_date between trunc(&given_date, 'yyyy') and sysdate)
then debit else 0 end ) as debit_til_this_day
, sum ( case when J_date between trunc(&given_date, 'yyyy') and sysdate)
then credit else 0 end ) as credit_til_this_day
from jour_entries
group by account_number解释
trunc()将其截断到给定的格式掩码。因此,trunc(sysdate, 'mm')给出了一个月的第一天,而'yyyy‘面具则是一年中的第一个。last_day()给了,呃,这个月的最后一天。发布于 2013-01-18 21:09:50
如果我理解你的问题,听起来像这样的事情应该对你有用:
SELECT JE.Account_no as Account__Number,
SUM(JE2.debit) as Debit_Within_Month,
SUM(JE2.credit) as Credit_Within_Month,
SUM(JE.debit) as Debit_Till_This_Day,
SUM(JE.credit) as Credit_Till_This_Day
FROM Jour_Entries JE
LEFT JOIN (
SELECT jseq, Account_No, Debit, Credit
FROM Jour_Entries
WHERE to_char(j_date, 'YYYY') = 2013 AND to_char(j_date, 'mm') = 2
) JE2 ON JE.jseq = JE2.jseq
WHERE to_char(j_date, 'YYYY') = 2013
GROUP BY JE.Account_no这是sql小提琴。
祝好运。
https://stackoverflow.com/questions/14407196
复制相似问题