sql - 需要帮助求和/分组值
问题描述
我不知道我是否应该在 SQL 中执行此操作。它是 SSIS 包的一部分,所以也许我应该使用脚本任务,但在我走这条路线之前,我想确定这是必需的。
我有一个小提琴设置 - http://sqlfiddle.com/#!18/f2aec/5。
这是我的数据:
create table test (
id int IDENTITY (1,1),
account int,
amount decimal(9,2),
code varchar(10)
);
insert into test (account, amount, code)
values
(0, 100, 'A')
,(0, 100, 'B')
,(1, 100, 'A')
,(1, -50, 'B')
,(2, 100, 'A')
,(2, 200, 'B')
,(2, -100, 'C')
,(3, 100, 'A')
,(3, -200, 'B')
,(3, 50, 'C')
,(4, -500, 'B')
,(4, -500, 'C')
,(5, -1200, 'B')
,(5, 1000, 'C')
,(6, 150, 'A')
,(6, -100, 'B')
,(6, 200, 'C')
,(7, 150, 'A')
,(7, 200, 'B')
,(7, -100, 'C')
,(8, 550, 'A')
,(8, -700, 'B')
,(8, 300, 'C')
,(8, -100, 'D');
这是我想要的输出:
-- desired output
-- 0 100.00 A
-- 0 100.00 B
-- 1 50.00 A
-- 2 200.00 B
-- 3 -50.00 A
-- 4 -500.00 B
-- 4 -500.00 C
-- 5 -200.00 B
-- 6 50.00 A
-- 6 200.00 B (currently wrong)
-- 7 50.00 A
-- 7 200.00 B (currently wrong)
-- 8 50.00 C (right now this is showing as C, but I would be okay if it was A)
基本上,如果一个帐户的所有记录都是正数,那么我应该显示所有记录,如果所有记录都是负数,则相同,但如果有混合,我想将这些值净值并将它们放在第一个代码桶中。
这是我迄今为止尝试过的(尽管我一直在旋转我的轮子,所以一个全新的方法可能是最好的)。
;with cte as (
select *
,sum(amount) over (PARTITION by account) as accounttotal
,sum(amount) over (PARTITION by account order by case when amount < 0 then 0 else 1 end, code) as runningtotal
,case when (select count(1) from test b where b.account = test.account and b.amount < 0) > 0 then 1 else 0 end as hasnegative
,ROW_NUMBER() over(partition by account order by case when amount < 0 then 0 else 1 end, code) as rownum
from test
)
--select * from cte
,cte2 as (
select
*
,max(rownum) over(partition by account ) as maxrownum
,FIRST_VALUE(code) over(partition by account order by case when runningtotal > 0 then 0 else 1 end, code) as firstcode
,sum(case when amount < 0 then 1 else 0 end) over(partition by account) as negativecount
from cte
)
--select * from cte2
select
--*,
account as accountnumber
,case when hasnegative = 0 or negativecount = maxrownum then amount else runningtotal end as val
,case when hasnegative = 0 or negativecount = maxrownum then code else firstcode end as code
from cte2
where
runningtotal > 0
or rownum = maxrownum
or negativecount = maxrownum
order by account, code
解决方案
不确定,但这可能是您正在寻找的答案:
SELECT account , SUM(amount) amount , min(code) code FROM (
SELECT *, dense_RANK() OVER (PARTITION BY account , CASE WHEN amount> 0 THEN 1 ELSE -1 END ORDER BY code) rn2 FROM (
SELECT account , SUM(amount) amount , min(code) code FROM (
SELECT *, dense_RANK() OVER (PARTITION BY account , CASE WHEN amount> 0 THEN 1 ELSE -1 END ORDER BY id) rn FROM test) tt
GROUP BY account , rn
HAVING SUM(amount) <> 0
) tt2
) tt3
GROUP BY account , rn2
ORDER BY account
推荐阅读
- javascript - html表单在mysql中插入数据的数据提交中断
- javascript - 循环遍历数组并填充矩阵 - JS
- python - 我正在尝试打开扩展名为 .ml 的文件
- jsonpath - jsonpath查找所有键离开包含一个字符串
- swift - 在 swiftui 中 2 秒后推送视图
- c++ - 是否有任何 C++ 函数可以对地图进行排序?
- python - 在字符串之后对齐文本以更加对称
- php - Slim 4:使用容器与模型和控制器共享数据库连接,无需 Eloquent
- javascript - Javascript 检查数据可用性和 http 状态
- react-native - 使用 Context API 时 React Native 渲染白屏