blocks|key|1233548|text|您可以将total另存为variable，然后在除法计算中使用它。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1233549|SELECT+
++`id`,+`count`,+
+++@total:=(SELECT+sum(`count`)+FROM+`table`+GROUP+BY+`group`)+AS+`total`,+
++`count`/@total+AS+`percent`+
FROM+`table`|code-block|syntax|javascript|1233550|注意：GROUP是MySQL中的reserved+word。您应该用反引号(`)将它(以及所有其他字段/表名)括起来。|1233551|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/user-variables.html|1|http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html^0|4|5|C|8|0|0|0|3|5|G|D|1|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@$9|Z|A|10|1|11]]|E|$]]|$1|F|3|G|5|H|7|12|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|13|8|@$9|14|A|15|B|C]]|D|@$9|16|A|17|1|18]]|E|$]]|$1|M|3|-4|5|6|7|19|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]|U|$5|P|Q|R|E|$S|V]]]]

You can save <code>total</code> as a <a href="http://dev.mysql.com/doc/refman/5.0/en/user-variables.html" rel="noreferrer">variable</a>, then use that in the division calculation.

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

NOTE: <code>GROUP</code> is a <a href="http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html" rel="noreferrer">reserved word</a> in MySQL. You should enclose it (and all other field/table names) in backticks (`).

blocks|key|4008333|text|与table一样，group是mysql中的保留字，您应该像这样使用它：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4008334|select+id,+count,+(select+sum(count)+from+`table`+group+by+`group`)+as+total,+count/total+as+percent+from+`table`|code-block|syntax|javascript|4008335|欲了解更多信息，请访问：MySQL+Reserved+Words|4008336|您将看到实际上可以使用count，但我还是会将所有的表名和列名都放在引号中。|4008337|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html^0|1|5|9|5|0|0|C|K|0|0|B|5|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]|$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|11|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|12|8|@]|D|@$9|13|A|14|1|15]]|E|$]]|$1|M|3|N|5|6|7|16|8|@$9|17|A|18|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|19|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]]]

<code>group</code> is a reserved word in mysql, as is <code>table</code>, you should use it like:

<pre><code>select id, count, (select sum(count) from `table` group by `group`) as total, count/total as percent from `table`
</code></pre>

For more information: <a href="http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html" rel="nofollow">MySQL Reserved Words</a>

You'll see there that you can actually use <code>count</code> but I would put all table and column names in quotes anyway.

blocks|key|5622980|text|您的问题是，内部查询需要每行生成1个结果，而不是为每个组生成1个结果。您希望在内部查询中添加一个where子句，如下所示|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5622981|where+inner_table.group+=+outer_table.group|offset|length|style|CODE|5622982|因此只返回一个结果。|5622983|entityMap^0|0|0|17|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

Your problem is that the inner query needs to generate 1 result per row, not 1 for every group. You want to add a where clause in the inner query saying something like

<code>where inner_table.group = outer_table.group</code> 

so that only one result is returned.

blocks|key|1235949|text|您也可以在不引入变量的情况下执行此操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1235950|select+id,+
+++count,+
+++(select+sum(count)+from+`table`+group+by+`group`)+as+total,+
+++(select+count/total)+as+percent+
from+`table`;|code-block|syntax|javascript|1235951|产生：|1235952|%2B------%2B-------%2B-------%2B---------%2B
%7C+id+++%7C+count+%7C+total+%7C+percent+%7C
%2B------%2B-------%2B-------%2B---------%2B
%7C++++1+%7C+++++3+%7C+++++5+%7C++0.6000+%7C
%7C++++2+%7C+++++2+%7C+++++5+%7C++0.4000+%7C
%2B------%2B-------%2B-------%2B---------%2B
2+rows+in+set+(0.05+sec)|1235953|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You can also do this without introducing a variable:

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

Produces:

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

blocks|key|4008417|text|这个问题已经有了MySql的答案，但如果有人像我一样在这里登陆MSSql，那么这个问题就像下面的MSSql一样简单|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4008418|select+id,+count,
++total+=+(select+sum(count)+from+table+group+by+group),
++count/total+as+percent+
from+table|code-block|syntax|javascript|4008419|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This question already has an answer for MySql but by mistake if anyone lands here for MSSql as i did, it is as simple as below for MSSql

<pre><code>select id, count,
 total = (select sum(count) from table group by group),
 count/total as percent 
from table
</code></pre>

I have a MySQL query that runs a brief operation (totalling the counts in a select statement) and I want to use the result to do a math operation, but I'm getting an error. 

Table:

<pre><code>id | group | count |
-----------------------------
1 1 3
2 1 2
</code></pre>

Query:

<pre><code>select id, count,
 (select sum(count) from table group by group) as total,
 count/total as percent 
from table
</code></pre>

The error is because there is no real "total" column in the table. How can I make the query work?

MySQL use generated column in select query

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个MySQL查询，它运行一个简短的操作(在select语句中对计数求和)，并且我想使用结果进行数学操作，但是我得到了一个错误。表：id  |   group   |   count   |-----------------------------1       1           32       1    ...

问MySQL在select查询中使用生成的列
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL在select查询中使用生成的列EN