表A
-----------------
a_id | user _a |
-----------------
1 |adam |
2 |jose |
3 |adam |
4 |adam |
5 |anne |
6 |jose |
表B
--------------------------------------
b_id | user_b | value1 | value2
--------------------------------------
1 |adam | 33 | 9
2 |jose | 46 |88
3 |adam | 77 |21
4 |adam | 81 |15
5 |anne | 11 |67
6 |jose | 45 |6
表C
--------------------------------------
c_id | user_c | value1 | value2
--------------------------------------
1 |adam | 33 | 9
2 |jose | 46 |88
3 |adam | 77 |21
4 |adam | 81 |15
表D
--------------------------------------
d_id | user_d | value1 | value2
--------------------------------------
1 |adam | 33 | 9
2 |jose | 46 |88
我如何查看value1和value2的总值。
示例：我希望列表视图页面如下所示。
--------------------------------------------------------
user | total value of value1 | total value of value2
---------------------------------------------------------
adam |415 | 99
jose |183 | 270
anne |11 | 67
请救命。
最佳答案
这是一种使用SQL查询（与PHP中的任何处理无关）来获取指定结果的方法：
SELECT t.user_t AS `user`
, SUM(t.value1) AS `total value of value1`
, SUM(t.value2) AS `total value of value2`
FROM ( SELECT b.user_b AS user_t
, SUM(b.value1) AS value1
, SUM(b.value2) AS value2
FROM table_b b
GROUP BY b.user_b
UNION ALL
SELECT c.user_c AS user_t
, SUM(c.value1) AS value1
, SUM(c.value2) AS value2
FROM table_c c
GROUP BY c.user_c
UNION ALL
SELECT d.user_d AS user_t
, SUM(d.value1) AS value1
, SUM(d.value2) AS value2
FROM table_d d
GROUP BY d.user_d
) t
GROUP BY t.user_t
ORDER BY 2 DESC