在sql中做a/(a+b)

62lalag4 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(257)

我正在执行以下代码，但无法获得正确的百分比作为输出

Select
name,
fans,
useful,
funny,
(useful/(Select (useful+funny) from user)) As useful_percent
From user
Order by fans desc
limit 10

输出是这样的，

+-----------+------+--------+--------+----------------+
| name      | fans | useful |  funny | useful_percent |
+-----------+------+--------+--------+----------------+
| Amy       |  503 |   3226 |   2554 |             36 |
| Mimi      |  497 |    257 |    138 |              2 |
| Harald    |  311 | 122921 | 122419 |           1381 |
| Gerald    |  253 |  17524 |   2324 |            196 |
| Christine |  173 |   4834 |   6646 |             54 |
| Lisa      |  159 |     48 |     13 |              0 |
| Cat       |  133 |   1062 |    672 |             11 |
| William   |  126 |   9363 |   9361 |            105 |
| Fran      |  124 |   9851 |   7606 |            110 |
| Lissa     |  120 |    455 |    150 |              5 |
+-----------+------+--------+--------+----------------+

有人能解释一下代码出了什么问题吗？

sql mysql

来源：https://stackoverflow.com/questions/50903146/doing-a-ab-in-sql

3条答案

按热度按时间

yh2wf1be1#

试试这个简单的查询。

Select
  name,
  fans,
  useful,
  funny,
  (useful/(useful+funny)) As useful_percent
From user
Order by fans desc
limit 10

或有用的百分比（%）值，然后使用

Select
  name,
  fans,
  useful,
  funny,
 ((useful/(useful+funny)) * 100.0)  As useful_percent
From user
Order by fans desc
limit 10

赞(0）回复(0）举报 2021-06-20

oxalkeyp2#

我建议你使用 nullif() 要避免被零除的问题：

Select name, fans, useful, funny,
       useful / nullif(useful + funny, 0) As useful_percent
From user
Order by fans desc
limit 10;

如其他答案所述，子查询不合适。

赞(0）回复(0）举报 2021-06-20

gkn4icbw3#

您的选择将导致错误，因为 (Select (useful+funny) from user) 返回多行。
你可能想要

Select
  name,
  fans,
  useful,
  funny,
  100.0 * useful/(useful+funny) As useful_percent
From user
Order by fans desc
limit 10

赞(0）回复(0）举报 2021-06-20

我来回答

在sql中做a/(a+b)

3条答案

相关问题

热门标签

最新问答