Mysql查询获取表名存储在另一个表中的每个表的总和

Question

我有一个存储报告名称的 MySQL 表，对于每个条目，我都有一个表，其中每个主题都有标记。

报告

+------------+----------+
|    S.No.   |ReportName|
+------------+----------+
| 1          | Report1  |
| 2          | Report2  |
+------------+----------+

报告 1

+------------+----------+
|    Subject | Marks    |
+------------+----------+
| Maths      | 78       |
| English    | 99       |
+------------+----------+

报告2

+------------+----------+
|    Subject | Marks    |
+------------+----------+
| Maths      | 80       |
| English    | 99       |
+------------+----------+

现在，我想从每个报告中获取分数总和作为表格。我可以为一张 table 做，但不能为所有人做。

对于一个表，它是:

SELECT (SUM(marks)) AS Total FROM `Report1`

预期:

+------------+----------+
| ReportName | Marks    |
+------------+----------+
| Report1    | 177      |
| Report2    | 179      |
+------------+----------+

Answer 1

你可以试试

使用UNION合并Report1和Report2

SELECT 'Report1' AS ReportName, SUM(Marks) AS Marks
FROM Report1
UNION ALL
SELECT 'Report2' AS ReportName, SUM(Marks) AS Marks
FROM Report2

SQLFiddle

编辑

Rport1和Rport2表的数据格式相同。

我建议你可以将表 Report1 和 Report2 合并为 InfoReport

InfoReport 可以通过 ReportID 与 Reports join

+------------+----------+----------+
|    Subject | Marks    | ReportID |
+------------+----------+----------+
| Maths      | 78       |     1    |
| English    | 99       |     1    |
| Maths      | 80       |     2    |
| English    | 99       |     2    |
+------------+----------+----------+

这样的新查询。

SELECT t1.ReportName as 'ReportName',
       SUM(T.Marks) as Marks
FROM InfoReport AS T
INNER JOIN Reports as T1 ON T.ReportID = T1.SNO
GROUP BY t1.ReportName

SQLFiddle