mysql - 选择依赖于其他 n 行的条件的行 - mysql

Question

我不确定如何正确表达标题，所以如果给出错误的想法，请更正它。
下面是一个名为 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
...   ...      ...        ...      ...

对于 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-10+5-9+11-10
 503   A        21         K        16         9
 ...   ...      ...        ...      ....       based on last 3 where name1 or name2=A

如您所见，编号_i 属于名称_i，i=1,2
条件:
- name1 或 name2 必须是 A
- number3 取决于最后 3 个 number_i 条目(包括当前)，其中 name_i=A，i=1,2

Answer 1

以下应该可以帮助您入门。

请注意，很多还有改进的余地，但由于我对 MySQL 缺乏了解，所以我选择了适用于每个 DBMS 的安全路线。

SQL语句

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