PHP代码列出所有具有总值的用户从其他表中收集
问题描述：
table A
-----------------
a_id | user _a |
-----------------
1 |adam |
2 |jose |
3 |adam |
4 |adam |
5 |anne |
6 |jose |
table 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
table C
--------------------------------------
c_id | user_c | value1 | value2
--------------------------------------
1 |adam | 33 | 9
2 |jose | 46 |88
3 |adam | 77 |21
4 |adam | 81 |15
table D
--------------------------------------
d_id | user_d | value1 | value2
--------------------------------------
1 |adam | 33 | 9
2 |jose | 46 |88
how can I view the total value of value1 and value2. example: I want my list view page look like this.
--------------------------------------------------------
user | total value of value1 | total value of value2
---------------------------------------------------------
adam |415 | 99
jose |183 | 270
anne |11 | 67
pls. help.
表格A p>
-------- ---------
a_id | 用户_a |
-----------------
1 | adam |
2 | jose |
3 | adam |
4 | adam |
5 | anne |
6 | jose | \ n code> pre>
表B p>
------------------ --------------------
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
code> pre>
表格C p>
-------------- ------------------------
c_id | user_c | value1 | value2
--------------------------------------
1 | adam | 33 | 9
2 | jose | 46 | 88
3 | adam | 77 | 21
4 | adam | 81 | 15
code> pre>
表D p>
-------------- ------------------------
d_id | user_d | value1 | value2
--------------------------------------
1 | adam | 33 | 9
2 | jose | 46 | 88
code> pre>
如何查看value1和value2的总值。
example：我希望我的列表视图页面如下所示。 p> \ ñ
----------------------------------------- ---------------
user | value1的总值 value2的总价值
-------------------------------------------- -------------
adam | 415 | 99
jose | 183 | 270
anne | 11 | 67
代码> PRE>
请。 帮助。 p>
div>
答
Here's one approach, to obtain the specified result using a SQL query (independent of any processing in 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