mysql - 从 MySQL 中的两个不同表中减去值

Question

我有两张桌子

Meetingyear1
+----+----------+------------+
| id | county   | attendance |
+----+----------+------------+
|  1 | Nyeri    |        100 |
|  2 | Murang'a |         50 |
|  3 | Kiambu   |        200 |
|  4 | Nairobi  |         50 |
|  5 | Kisumu   |        300 |
|  6 | Nyeri    |         50 |
|  7 | Kisumu   |        400 |
|  8 | Nairobi  |        800 |
|  9 | Kiambu   |         80 |
| 10 | Murang'a |        200 |
+----+----------+------------+

Meetingyear2
+----+----------+------------+
| id | county   | attendance |
+----+----------+------------+
|  1 | Nyeri    |         50 |
|  2 | Murang'a |         78 |
|  3 | Kiambu   |        500 |
|  4 | Nairobi  |       1000 |
|  5 | Kisumu   |          0 |
|  6 | Nyeri    |         12 |
|  7 | Kisumu   |        200 |
|  8 | Nairobi  |         60 |
|  9 | Kiambu   |        100 |
| 10 | Murang'a |          0 |
+----+----------+------------+



select meetingyear1.county,sum(meetingyear1.attendance) as attendance1,sum(meetingyear2.attendance) as attendance2,sum(meetingyear1.attendance-meetingyear2.attendance) as difference from meetingyear1 INNER JOIN meetingyear2 ON meetingyear1.county =meetingyear2.county GROUP BY county;

我得到这个

+----------+-------------+-------------+------------+
| county   | attendance1 | attendance2 | difference |
+----------+-------------+-------------+------------+
| Kiambu   |         560 |        1200 |       -640 |
| Kisumu   |        1400 |         400 |       1000 |
| Murang'a |         500 |         156 |        344 |
| Nairobi  |        1700 |        2120 |       -420 |
| Nyeri    |         300 |         124 |        176 |
+----------+-------------+-------------+------------+

预期的结果是

COUNTY         ATTENDANCE
NYERI            88
MURANG'A         172
KIAMBU           320
NAIROBI          210
KISUMU           500

标签： mysql

Answer 1

您可以在下面尝试 - 使用子查询并加入

select A.county, attendance1-attendance2 as difference from
(
select meetingyear1.county, sum(meetingyear1.attendance) as attendance1
from meetingyear1
group by meetingyear1.county
)A inner join
(
select meetingyear2.county, sum(meetingyear2.attendance) as attendance2
from meetingyear2
group by meetingyear2.county
)B on A.county=B.county