MySQL 在选择查询中使用生成的列

php mysql

2022-08-30 20:42:57

我有一个MySQL查询，它运行一个简短的操作（在select语句中汇总计数），我想使用结果来执行数学运算，但我得到了一个错误。

桌子：

id  |   group   |   count   |
-----------------------------
1       1           3
2       1           2

查询：

select id, count,
  (select sum(count) from table group by group) as total,
  count/total as percent 
from table

错误是因为表中没有真正的“总计”列。如何使查询正常工作？

答案 1

您可以另存为变量，然后在除法计算中使用它。total

SELECT 
  `id`, `count`, 
   @total:=(SELECT sum(`count`) FROM `table` GROUP BY `group`) AS `total`, 
  `count`/@total AS `percent` 
FROM `table`

注意：是MySQL中的保留字。应将其（以及所有其他字段/表名称）括在反引号（'）中。GROUP

答案 2

您也可以在不引入变量的情况下执行此操作：

select id, 
   count, 
   (select sum(count) from `table` group by `group`) as total, 
   (select count/total) as percent 
from `table`;

生产：

+------+-------+-------+---------+
| id   | count | total | percent |
+------+-------+-------+---------+
|    1 |     3 |     5 |  0.6000 |
|    2 |     2 |     5 |  0.4000 |
+------+-------+-------+---------+
2 rows in set (0.05 sec)