mysql - 从单独的表中添加计数总计

Question

我有 2 个表都引用了国家/地区。这是通过表 1 和/或表 2 中的国家/地区字段实现的。

我可以通过以下语句计算金额

SELECT co.*,  
    (SELECT COUNT(*) FROM table1 m WHERE m.country=co.id) AS mCount, 
    (SELECT COUNT(*) FROM table2 e WHERE e.country=co.id) AS eCount FROM countries co

结果是这样的:

| id | name      | mCount | eCount |

但是我想把mCount中的值加到eCount的值上。我期待一个简单的加号，例如:

SELECT co.*, mCount+eCount AS grandTotal

但这行不通。如何在查询中添加这些列？

Answer 1

1- 您可以将查询用作派生表。

SELECT dt.*, (dt.mCount + dt.eCount) AS grandTotal FROM 
(SELECT co.*,  
    (SELECT COUNT(*) FROM table1 m WHERE m.country=co.id) AS mCount, 
    (SELECT COUNT(*) FROM table2 e WHERE e.country=co.id) AS eCount FROM countries co
) AS dt;

2-您可以进一步选择计算列

SELECT co.*,  
   (SELECT COUNT(*) FROM table1 m WHERE m.country=co.id) AS mCount, 
   (SELECT COUNT(*) FROM table2 e WHERE e.country=co.id) AS eCount,
   (SELECT mCount + eCount) AS grandTotal
 FROM countries co

3- 你也可以定义用户变量(不鼓励)

SELECT co.*,  
    @mCount := (SELECT COUNT(*) FROM table1 m WHERE m.country=co.id) AS mCount, 
    @eCount := (SELECT COUNT(*) FROM table2 e WHERE e.country=co.id) AS eCount,
    (@mCount + @eCount) AS grandTotal
 FROM countries co

我希望你会最喜欢#2 :)