mysql - 根据mysql中的条件添加两行的列

Question

我有以下表结构:

+------+-------+-------+--------+| mid  | a     | b     | points |+------+-------+-------+--------+|   69 |  3137 | 13316 |    210 ||   70 | 13316 |  3137 |    350 ||   71 |  3497 | 13316 |    200 ||   72 | 13316 |  3497 |     25 ||   73 |  3605 | 13316 |    205 ||   74 | 13316 |  3605 |    290 |+------+-------+-------+--------+

当第 1 行的“a” = 第 2 行的“b”且第 2 行的“a” = 第 1 行的“b”时，我想添加两行的“点”值。

输出需要是这样的:

+------+-------+-------+--------+| mid  | a     | b     | points |+------+-------+-------+--------+|   69 |  3137 | 13316 |    560 ||   71 |  3497 | 13316 |    225 ||   73 |  3605 | 13316 |    495 |+------+-------+-------+--------+

Answer 1

你可以试试这个方法

SELECT t1.mid, t1.a, t1.b, t1.points + t2.points points
  FROM table1 t1 JOIN table1 t2
    ON t1.a = t2.b
   AND t1.b = t2.a
   AND t1.mid < t2.mid

或利用 MySQL 非标准 GROUP BY 扩展

SELECT mid, a, b, SUM(points) points
  FROM
(
  SELECT mid, a, b, points
    FROM table1
   ORDER BY mid
) q
 GROUP BY LEAST(a, b), GREATEST(a, b)

输出:

| MID |    A |     B | POINTS ||-----|------|-------|--------||  69 | 3137 | 13316 |    560 ||  71 | 3497 | 13316 |    225 ||  73 | 3605 | 13316 |    495 |

这里是SQLFiddle 演示