gpt4 book ai didi

sql - 6 个月或更长时间的负账款,无论交易如何

转载 作者:行者123 更新时间:2023-12-04 18:36:19 24 4
gpt4 key购买 nike

我的账户很少

Select * from Trans;

AccountID PostDate Description Amount
1 07/01/2016 deposit 10.00
1 07/09/2016 withdrawal -15.00

第二个帐户:
AccountID PostDate    Description   Amount 
2 07/01/2016 deposit 10.00
2 07/13/2016 withdrawal -20.00
2 01/05/2017 deposit 8.00

第三个账户:
AccountID PostDate    Description   Amount 
3 07/05/2016 deposit 10.00
3 07/19/2016 deposit 20.00
3 08/28/2016 withdrawal -45.00

第四个账户:
AccountID PostDate    Description   Amount 
4 01/05/2016 deposit 10.00
4 01/19/2016 withdrawal -20.00
4 09/28/2016 deposit 40.00
4 10/01/2016 withdrawal -50

我正在寻找所有连续 6 个月或更长时间为负的账户,无论是否有存款进入并且账户是否保持为负。如果存款使运行余额为正,那么显然我需要排除该帐户。

我需要一个一般查询...因为我在 trans 表中有不止上述两个帐户。

查询应选择 AccountID 1,因为它在 180 天以上为负数。它应该选择 AccountID 2,因为它自 2016 年 7 月 13 日起为负数。 7 月份是负 -10,虽然存款来了,但在 2017 年 1 月它仍然是负 -2。它不应该拿起 AccountID 3,因为余额是负的,但它在 2016 年 8 月 28 日变成负数,这意味着它是仅 148 天为阴性。我也不想拿起帐户“4”。虽然它连续 6 个月或更长时间为负,目前它也是负的......但我想获得在 2017 年 1 月 23 日至 2016 年 7 月 23 日之间所有日子的运行余额为负的帐户。

谢谢

最佳答案

Oracle 设置 :

CREATE TABLE trans ( AccountID, PostDate, Description, Amount ) AS
SELECT 1, DATE '2016-07-01', 'deposit', 10.00 FROM DUAL UNION ALL
SELECT 1, DATE '2016-07-09', 'withdrawal', -15.00 FROM DUAL UNION ALL
SELECT 2, DATE '2016-07-01', 'deposit', 10.00 FROM DUAL UNION ALL
SELECT 2, DATE '2016-07-13', 'withdrawal', -20.00 FROM DUAL UNION ALL
SELECT 2, DATE '2017-01-05', 'deposit', 8.00 FROM DUAL UNION ALL
SELECT 3, DATE '2016-07-05', 'deposit', 10.00 FROM DUAL UNION ALL
SELECT 3, DATE '2016-07-19', 'deposit', 20.00 FROM DUAL UNION ALL
SELECT 3, DATE '2016-08-28', 'withdrawal', -45.00 FROM DUAL UNION ALL
SELECT 4, DATE '2016-01-05', 'deposit', 10.00 FROM DUAL UNION ALL
SELECT 4, DATE '2016-01-19', 'withdrawal', -20.00 FROM DUAL UNION ALL
SELECT 4, DATE '2016-09-28', 'deposit', 40.00 FROM DUAL UNION ALL
SELECT 4, DATE '2016-10-01', 'withdrawal', -50.00 FROM DUAL;

查询 :
SELECT accountid
FROM (
SELECT t.*,
SUM( amount ) OVER ( PARTITION BY AccountID ORDER BY postdate )
AS balance
FROM trans t
)
GROUP BY accountid
HAVING MAX( balance ) KEEP ( DENSE_RANK LAST ORDER BY postdate ) < 0
AND ( MAX( postdate ) <= TRUNC( SYSDATE ) - 180
OR MAX( CASE WHEN postdate >= TRUNC( SYSDATE ) - 180
THEN balance - amount END ) < 0
);

输出 :
 ACCOUNTID
----------
1
2

关于sql - 6 个月或更长时间的负账款,无论交易如何,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/41817079/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com