我有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
但这不起作用。如何在查询中添加这些列?
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 :)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句