作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我的账户很少
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
最佳答案
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/
所以我的问题是: 为什么(以及如何避免)C# 中的 Is 运算符的生命周期比 if 中使用的更长? 例子: Animal a = new Cat(); if (a is Cat c) { Co
我遇到了这个问题,我已经尝试解决了很长一段时间。理想情况下,我希望 particles.js 的“生成框”向下延伸到页面底部(由 fullpage.js 计算的 7 x viewport),这样当使用
我想让一个 div(我的侧边栏)延伸到页面底部。我知道我需要添加“高度:100%;”为了做到这一点。 但是当我添加 height: 100%; 时,内容少于侧边栏的页面会降低侧边栏的高度,然后您就看不
只有我这么认为吗,还是在 SQL Server 2008 Management Studio 中扩展数据库列表比在 SQL Server 2005 Management Studio 中扩展数据库列表
我是一名优秀的程序员,十分优秀!