如何使用MySql和PHP存储/处理多个表的数据

I have 5 different MySql tables viz:

我有5个不同的MySql表，即：

u_t (uid, pwd, field1, field2)

u_t（uid，pwd，field1，field2）

t_1 (tid_1, ut_uid, field_1, field_2, points)

t_1（tid_1，ut_uid，field_1，field_2，points）

t_2 (tid_2, ut_uid, field_1, field_2, points)

t_2（tid_2，ut_uid，field_1，field_2，points）

t_3 (tid_3, ut_uid, field_1, field_2, points)

t_3（tid_3，ut_uid，field_1，field_2，points）

t_4 (tid_4, ut_uid, field_1, field_2, points)

t_4（tid_4，ut_uid，field_1，field_2，points）

PS: uid=pk , tid_1,2,3,4=pk, ut_uid=fk

PS：uid = pk，tid_1,2,3,4 = pk，ut_uid = fk

All the above tables are having some information based on some unique features.

以上所有表格都基于一些独特的功能提供了一些信息。

Now i want to get the sum of points from each table for every user for storing and displaying purpose, using PHP.

现在我希望使用PHP从每个表中获取每个用户存储和显示目的的点数总和。

2 个解决方案

#1

try

尝试

select u_t.uid, 
       sum(t_1.points+t_2.points+t_3.points+t_4.points+t_5.points) as total_p
from u_t
left outer join t_1 on t_1.ut_uid = u_t.uid
left outer join t_2 on t_2.ut_uid = u_t.uid
left outer join t_3 on t_3.ut_uid = u_t.uid
left outer join t_4 on t_4.ut_uid = u_t.uid
group by u_t.uid

#2

An uglier version of this:

这个丑陋的版本：

SELECT ut.uid, sum(t1.pts + t2.pts + t3.pts + t4.pts) total
FROM u_t ut
LEFT JOIN
(
  SELECT ut_uid, sum(points) pts
  FROM t_1
  GROUP BY ut_uid
) t1
  on ut.uid = t1.ut_uid
LEFT JOIN
(
  SELECT ut_uid, sum(points) pts
  FROM t_2
  GROUP BY ut_uid
) t2
  on ut.uid = t2.ut_uid
LEFT JOIN
(
  SELECT ut_uid, sum(points) pts
  FROM t_3
  GROUP BY ut_uid
) t3
  on ut.uid = t3.ut_uid
LEFT JOIN
(
  SELECT ut_uid, sum(points) pts
  FROM t_4
  GROUP BY ut_uid
) t4
  on ut.uid = t4.ut_uid
GROUP BY ut.uid