MySQL: Performing an operation on different rows based on condition?

Say I have a query which returns the following result:

| val   |  type  | i |
----------------------
| 59    |   1    | 1 |
| 40    |   2    | 2 |
| 12    |   1    | 3 |
| 3     |   2    | 4 |
| 24    |   1    | 5 |
| 30    |   1    | 6 |
| 98    |   2    | 7 |
| 45    |   2    | 8 |
| 46    |   1    | 9 |

val = an arbitrary number
type = 1 or 2
i = auto increment column, just for reference

I want to perform the following subtraction:

A - B

A:  Value of row of type '2'
B:  Value of row above A of type '1' if it exists, but without crossing a row of type '2'

I really hope that made sense..

Some examples:

Row i=2 is of type 2.  So the first row above it of type 1 is i=1.  
So the subtraction will be:  40 - 59 = -19 

Row i=7 is of type 2.  So the first row above it of type 1 is i=6
So the subtraction will be: 98 - 30 = 68

Row i=8 is of type 2.  B开发者_Python百科ut if we go above we cross a value of type 2.
So the subtraction should return 0, or be ignored, whichever is simplest.

In the end the result should return a column of values where type=1 and the subtracted value .

Eg:

| val | diff |
--------------
| 59  | -19  |
| 12  | -9   |
| 24  | 0    |  ***
| 30  | 68   |
| 46  | 0    |  ***

*** Return diff=0 if the subtraction was never made for this row.

I have the query for getting my initial table. I know how to do the subtraction (in the real case val is a date, but I'm keeping it simple for this example).

The only thing I have no idea how to do is perform logic based on different rows in MySQL. For example how can I find the nearest row based on some condition and then reference that row to perform some operation on it? Is there perhaps a way to reference the current 'i' and do an subrtaction on (row at i) minus (row at i-1) or something similar.

I've spent many hours on this already and I'm just about to give up and do it in php instead and take the performance hit. As a last resort I'm asking here if there's a way to do all this in mysql directly?

I don't need a full solution as this is pretty complex requirement but I will take any sort of advice or pointers you can give me. I'm not the best at MySQL so maybe I'm missing something simple.

Thanks.

 SELECT t1.val AS val, IFNULL( t2.val, t1.val ) - t1.val AS diff FROM my_table AS t1
 LEFT JOIN my_table AS t2 ON( t2.type = 2 AND t2.i = t1.i + 1 )
 WHERE t1.type = 1 ORDER BY t1.i;

Tested on your data, result:

| val | diff |
--------------
| 59  | -19  |
| 12  | -9   |
| 24  | 0    |
| 30  | 68   |
| 46  | 0    |

What you need is a stored procedure with a cursor on your table. This should be a good starting pont.

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE val, type, prev_val, prev_type INT;
  DECLARE cur1 CURSOR FOR SELECT val, type, i from your_table;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;

  read_loop: LOOP
    FETCH cur1 INTO val, tpye, i;
    IF done THEN
      LEAVE read_loop;
    END IF;

    -- your logic goes here, sorry, not sure I understood correctly, but roughly here it goes
    IF type = 2 THEN
      IF prev_type is not null and prev_type = 1 THEN
        INSERT INTO result_table VALUES (prev_val, val - prev_val);
      END IF;
    END IF;
    SET prev_val = val;
    SET prev_type = type;
  END LOOP;

  CLOSE cur1;
END;

Maybe you can do something like this:

select t1.val, (t1.val - t2.val) as diff
     from table as t1, table as t2
     where t1.i = t2.i + 1 
       and t1.type = 1
       and t2.type = 2

to select with zeros you can do a hack like this:

select t1.val, (t1.val - t2.val)*(t2.type - t1.type) as diff
     from table as t1, table as t2
     where t1.i = t2.i + 1 
       and t1.type = 1

to get the last zero when there is one, join with:

select val, 0 as diff from
   select val
      from table
      order by i desc
      limit 1