gpt4 book ai didi

mysql - 检测负面变化并采取行动

转载 作者:行者123 更新时间:2023-11-29 14:04:39 25 4
gpt4 key购买 nike

我有这张表,我只想查看 AB

ID     CODE       COUNT   
102 AB 9
101 AB 8
100 AC 23 //not important!!!!
99 AB 7
98 AB 6
97 AB 5
96 AB 0

然后我想计算具有 CODE 'AB' 的特定 ID 之间的差异

所以

Step 1: 9 - 8 = 1   // (corrected sample data. this line was wrong)
Step 2: 8 - 7 = 1
Step 3: 7 - 6 = 1
Step 4: 6 - 5 = 1
Step 5: 5 - 0 = 5

这是我通过@bonCodigo 提出的查询来完成的

select ID, DIFFERENCE, 
COUNT from (
SELECT
t.ID, t.CODE, t.COUNT,
@PREVCOUNT,
@PREVCOUNT - t.COUNT DIFFERENCE,
@PREVCOUNT := t.COUNT -- Updates for the next iteration, so it
-- must come last!
FROM
(SELECT ID, CODE, COUNT FROM some_table WHERE CODE = 'AB' ORDER BY ID DESC) t,
(SELECT @PREVCOUNT := NULL) _uv
group by t.id, t.code
)x
where x.difference >= 0
order by ID DESC;

由于我的新传入数据有时会重置计数,因此它会从 0 开始计数,直到为止。

所以有时我会按以下顺序获取数据:

ID        COUNT
1. 0
2. 1
3. 2
4. 7
5. 4 // which means the counter has reset to 0 and counted up to 4 again.
6. 5

现在我的查询正在做什么,它只计算积极的变化,并将其视为差异。

它的作用是:

Step 1: 1 - 0 = 1
Step 2: 2 - 1 = 1
Step 3: 7 - 2 = 5
Step 4: 4 - 7 = -3 //discarded as this difference is smaller than 0
Step 5: 5 - 4 = 1

所以如果我 SUM() 这个我得到 8 http://sqlfiddle.com/#!2/6924a/2

虽然我希望这段代码一旦出现负差就从 0 开始计数

所以我想要的是:

Step 1: 1 - 0 = 1
Step 2: 2 - 1 = 1
Step 3: 7 - 2 = 5
Step 4: 4 - 7 = -3 BUT MAKE IT 4 because the counter started from 0 again.
Step 5: 5 - 4 = 1

所以如果我 SUM() 这个我得到12

最佳答案

IF 语句即可解决问题。

IF((@PREVCOUNT - t.COUNT) < 0, @PREVCOUNT, (@PREVCOUNT - t.COUNT)) DIFFERENCE

这是工作 fiddle :http://sqlfiddle.com/#!2/6924a/7/0

修改后的查询是:

select ID, DIFFERENCE, 
COUNT from (
SELECT
t.ID, t.CODE, t.COUNT,
@PREVCOUNT,
IF((@PREVCOUNT - t.COUNT) < 0, @PREVCOUNT, (@PREVCOUNT - t.COUNT)) DIFFERENCE,
@PREVCOUNT := t.COUNT -- Updates for the next iteration, so it
-- must come last!
FROM
(SELECT ID, CODE, COUNT FROM some_table WHERE CODE = 'AB' ORDER BY ID DESC) t,
(SELECT @PREVCOUNT := NULL) _uv
group by t.id, t.code
)x
where x.difference >= 0
order by ID DESC;

关于mysql - 检测负面变化并采取行动,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14563083/

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