php - 两个不同表的分组行的总和

Question

我不知何故陷入了一个非常简单的任务..

我有两个表:

TABLE A:
name  time
name1 100
name2  50
name1 200
name3 100
name2  50

TABLE B:
name time
name3 100
name1  50
name3 200
name2 100
name1  50

我想要的只是总时间最长的前 2 个名字的列表!

类似这样的东西(但这不起作用)

SELECT  a.name 
        , SUM(a.time) as time1
        , (SELECT SUM(time) FROM table2 b WHERE b.name = a.name GROUP BY a.name) as time2
        , time1 + time2 as total
FROM table1 a
GROUP BY a.name
ORDER BY total DESC
LIMIT 2

提前致谢!

Answer 1

我会选择这样的东西:

SELECT
  name,
  SUM(time) AS total_time
  FROM (
    SELECT name, time FROM table_a
    UNION ALL
    SELECT name, time FROM table_b
  ) AS u
  GROUP BY name
  ORDER BY total_time DESC
  LIMIT 2
;

这是一个working sqlfiddle与您的数据。