Select rows which depend on conditions on n other rows - mysql

I'm not sure how to correctly articulate the title, so please correct it if giving the wrong idea.

Here's an example table named table1:

id    name1    number1    name2    number2
...   ...      ...        ...      ...
341   A        12         T        10
342   C        17         A        21
343   H        15         G        3
344   C        10         A        11
345   T        15         G        16
...   ...      ...        ...      ...

Here is what I'd like to select, for n=3:

 id    name1    number1    name2    number2    number3
 ...   ...      ...        ...      ...        ...
 341   A        12         T        10         ...
 342   C        17         A        21         ...
 344   C        10         A        11         11-10+21-17+12-10
 351   D        9          A        5          5-9+11-10+21-17
 360   A        18         C        10         18-1开发者_C百科0+5-9+11-10
 503   A        21         K        16         9
 ...   ...      ...        ...      ....       based on last 3 where name1 or name2=A

As you can see number_i belongs to name_i, i=1,2

Conditions:

- name1 or name2 must be A

- number3 depends on last 3 number_i entries (current included) where name_i=A, i=1,2

Following should get you started.

Note that there is much room for improvement but because of my lacking knowledge regarding MySQL, I have taken the safe route that should work on every DBMS.

SQL Statement

SELECT  id
        , name1
        , number1
        , name2
        , number2
        , r1number1 - r1number2 + r2number1 - r2number2 + r3number1 - r3number2
FROM    ( 
          SELECT  r1.id
                  , r1.name1
                  , r1.number1
                  , r1.name2
                  , r1.number2
                  , r1number1 = CASE WHEN r1.name1 = 'A' THEN r1.number1 ELSE r1.number2 END
                  , r1number2 = CASE WHEN r1.name1 = 'A' THEN r1.number2 ELSE r1.number1 END
                  , r2number1 = CASE WHEN r2.name1 = 'A' THEN r2.number1 ELSE r2.number2 END
                  , r2number2 = CASE WHEN r2.name1 = 'A' THEN r2.number2 ELSE r2.number1 END
                  , r3number1 = CASE WHEN r3.name1 = 'A' THEN r3.number1 ELSE r3.number2 END
                  , r3number2 = CASE WHEN r3.name1 = 'A' THEN r3.number2 ELSE r3.number1 END
          FROM    (
                    SELECT  r1id = r1.id, r2id = MAX(r2.id), r3id = MAX(r3.id)
                    FROM    (SELECT * FROM q WHERE name1 = 'A' OR name2 = 'A') r1
                            LEFT OUTER JOIN (SELECT * FROM q WHERE name1 = 'A' OR name2 = 'A') r2 ON r2.id < r1.id
                            LEFT OUTER JOIN (SELECT * FROM q WHERE name1 = 'A' OR name2 = 'A') r3 ON r3.id < r2.id
                    GROUP BY
                            r1.id
                  ) rid
                  INNER JOIN (SELECT * FROM q WHERE name1 = 'A' OR name2 = 'A') r1 ON r1.id = rid.r1id
                  LEFT OUTER JOIN (SELECT * FROM q WHERE name1 = 'A' OR name2 = 'A') r2 ON r2.id = rid.r2id
                  LEFT OUTER JOIN (SELECT * FROM q WHERE name1 = 'A' OR name2 = 'A') r3 ON r3.id = rid.r3id
        ) r