1

Well, my query is :

SELECT *, 
(aliasA.avalue+aliasB.avalue) as mycustomsum

FROM (SELECT bla,bla,bla ...) as aliasA
INNER JOIN (SELECT bla,bla,bla ...) as aliasB 
ON aliasA.mydate=aliasB.otherdate
order by month desc

Now i would like another column of itself but showing mycustomsum of 12 months before ! Self join of alias doesn't work !

in other words the result of my query is :

2017-12 | 123
2017-11 | 456
.
.
.
2016-12 | 789
2016-11 | 321

and i would like :

2017-12 | 123 | 789
2017-11 | 456 | 321
.
.
.
2016-12 | 789 | null
2016-11 | 321 | null

Just want to compare current year to last year , month by month , Its just "show the result from 12 rows ago" ! Any idea ?

digital.aaron
  • 5,435
  • 2
  • 24
  • 43
Ledahu
  • 111
  • 8
  • Check out this answer for a MySQL hack to implement a LAG function. https://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql – digital.aaron Dec 07 '17 at 16:23
  • Depending on your MySQL version, you may have access to an actual LAG function: https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_lag – digital.aaron Dec 07 '17 at 16:24

1 Answers1

0

As aaron said it in previous comment: LAG function are a(the) way but with mysql 5.7... no LAG function, so simulating !

So the working solution for -12 rows value compare is : 

SET @back0=-1;
SET @back1=-1;
SET @back2=-1;
SET @back3=-1;
SET @back4=-1;
SET @back5=-1;
SET @back6=-1;
SET @back7=-1;
SET @back8=-1;
SET @back9=-1;
SET @back10=-1;
SET @back11=-1;

Select * , 
(MyvalueA-MyvalueB) as Myresult,
@back0 m0,
@back0:=@back1 m1,
@back1:=@back2 m2,
@back2:=@back3 m3,
@back3:=@back4 m4,
@back4:=@back5 m5,
@back5:=@back6 m6,
@back6:=@back7 m7,
@back7:=@back8 m8,
@back8:=@back9 m9,
@back9:=@back10 m10,
@back10:=@back11 m11,
@back11:=(MyvalueA-MyvalueB) m12

etc....

Remember to reverse the original order :)

Why this super-crappy solution ? Because there is no Array type in mysql variable !

If someone has another elegant solution, you are welcome

Ledahu
  • 111
  • 8