Truy vấn MySQL tính tổng từ 5 bảng có cột tên "UP" giống nhau

Sử dụng UNION ALL kết hợp với SUM(). Tạo 5 bảng mẫu:

mysql> create table DemoTable1977 ( UP int ); Query OK, 0 rows affected (0.00 sec)
mysql> insert into DemoTable1977 values(10); Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1977 values(20); Query OK, 1 row affected (0.00 sec)
mysql> select * from DemoTable1977;
+------+
| UP   |
+------+
| 10   |
| 20   |
+------+
2 rows in set (0.00 sec)

mysql> create table DemoTable1978 ( UP int ); Query OK, 0 rows affected (0.00 sec)
mysql> insert into DemoTable1978 values(30); Query OK, 1 row affected (0.00 sec)
mysql> select * from DemoTable1978;
+------+
| UP   |
+------+
| 30   |
+------+
1 row in set (0.00 sec)

mysql> create table DemoTable1979 ( UP int ); Query OK, 0 rows affected (0.00 sec)
mysql> insert into DemoTable1979 values(40); Query OK, 1 row affected (0.00 sec)
mysql> select * from DemoTable1979;
+------+
| UP   |
+------+
| 40   |
+------+
1 row in set (0.00 sec)

mysql> create table DemoTable1980 ( UP int ); Query OK, 0 rows affected (0.00 sec)
mysql> insert into DemoTable1980 values(50); Query OK, 1 row affected (0.00 sec)
mysql> select * from DemoTable1980;
+------+
| UP   |
+------+
| 50   |
+------+
1 row in set (0.00 sec)

mysql> create table DemoTable1981 ( UP int ); Query OK, 0 rows affected (0.00 sec)
mysql> insert into DemoTable1981 values(60); Query OK, 1 row affected (0.00 sec)
mysql> select * from DemoTable1981;
+------+
| UP   |
+------+
| 60   |
+------+
1 row in set (0.00 sec)

Truy vấn tính tổng từ 5 bảng với cột "UP":

mysql> select sum(TotalSum) from ( select sum(UP) as TotalSum from DemoTable1977 union all select sum(UP) from DemoTable1978 union all select sum(UP) from DemoTable1979 union all select sum(UP) from DemoTable1980 union all select sum(UP) from DemoTable1981 ) tbl;

Kết quả:

+---------------+
| sum(TotalSum) |
+---------------+
| 210           |
+---------------+
1 row in set (0.00 sec)