Comparison of using CASE with USER VARIABLES
I have this table where I only want to see 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
Refers to this
ID NEWID CODE COUNT
102 102 AB 9
101 101 AB 8
99 100 AB 7
98 99 AB 6
97 98 AB 5
96 97 AB 0
Using
SELECT
t.ID, t.CODE, t.COUNT,
@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;
http://sqlfiddle.com/#!2/e0b8b/36/0
So,
Step 1: 9 - 1 = 1
Step 2: 8 - 7 = 1
Step 3: 7 - 6 = 1
Step 4: 6 - 5 = 1
Step 5: 5 - 0 = 5
Now there may be a case where I have
ID NEWID CODE COUNT
102 102 AB 4
101 101 AB 2
99 100 AB 1
98 99 AB 0
97 98 AB 7
96 97 AB 0
Then I want to count
Step 1: 4 - 2 = 2
Step 2: 2 - 1 = 1
Step 3: 1 - 0 = 1
Step 4: 0 - 7 = -7 //want to discard this negative value
Step 5: 5 - 0 = 7
Where I want to abandon step 4 because it is negative.
Now I am using this code to reverse the negative value
SELECT
t.ID, t.CODE, t.COUNT,
@PREVCOUNT,
@PREVCOUNT - t.COUNT DIFFERENCE,
CASE @PREVCOUNT - t.COUNT WHEN @PREVCOUNT - t.COUNT >= 0 THEN 'equal or bigger' ELSE 'smaller' END,
@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;
After running this code, I expect to see where the comparison made is greater than or equal to 0, instead I get very different results.
Below are links to see what I mean:
http://sqlfiddle.com/#!2/6be4a/1
I would really like to know what is wrong here and I would like to have a solution.
respectfully
+3
source to share
1 answer
How about this:
http://sqlfiddle.com/#!2/6be4a/7
select * from (
SELECT
t.ID, t.CODE, t.COUNT,
@PREVCOUNT,
@PREVCOUNT - t.COUNT DIFFERENCE,
CASE @PREVCOUNT - t.COUNT WHEN @PREVCOUNT - t.COUNT >= 0 THEN 'equal or bigger' ELSE 'smaller' END,
@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;
+1
source to share