时间戳

首页 > 解决方案 > 从单独的表中添加计数总计

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

问题描述

我有 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

但这不起作用。如何在查询中添加这些列？

标签： mysqlmathcount

解决方案

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 :)

推荐阅读