MySQL MySQL从多列中选择和计算值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12324280/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL select and calculate value from multiple columns
提问by verheesj
I am trying to create a mysql select query of which calculates a value based on two other fields.
我正在尝试创建一个 mysql 选择查询，它根据其他两个字段计算一个值。
This is my query,
这是我的查询，
SELECT request_id, (
unit_cost * quantity
) AS claim_value
FROM `xx_non_part_usage`
WHERE request_id = request_id
GROUP BY request_id
The query above only brings back the total_value for one of the rows.
上面的查询仅带回其中一行的 total_value。
For example - here is some sample data,
例如 - 这是一些示例数据，
ID REQUEST_ID QUANTITY UNIT_VALUE
1 10001 2.0 3.00
2 10001 1.0 19.00
3 10003 0.5 18.00
4 10001 10.0 12.00
5 10003 0.75 6.76
6 10002 9.0 3.20
7 10001 0.10 13.80
8 10001 1.0 90.99
9 10004 6.75 3.00
10 10009 3.23 87.00
As you can see there are several rows of REQUEST_ID '10001'. What the query needs to do is REQUEST_ID * QUANTITY then group them so it only returns the final value price (adding all of the results from the multiply sum (REQUEST_ID * QUANTITY).
如您所见，有几行 REQUEST_ID '10001'。查询需要做的是 REQUEST_ID * QUANTITY 然后将它们分组，以便它只返回最终价值价格（添加乘和 (REQUEST_ID * QUANTITY) 的所有结果）。
Here is an expected result of what I am hoping to get (different example on REQUEST_ID 10003,
这是我希望得到的预期结果（REQUEST_ID 10003 上的不同示例，
REQUEST_ID TOTAL_VALUE
10003 14.07
10004 20.75
...
...
Thank you in advance.
先感谢您。
回答by Guy Fawkes
Why do you use "where" clause?
为什么要使用“where”子句？
mysql> select * from test;
+------------+------------+----------+
| request_id | unit_value | quantity |
+------------+------------+----------+
| 1 | 3 | 2 |
| 1 | 19 | 1 |
| 2 | 6.76 | 0.75 |
| 2 | 18 | 0.5 |
+------------+------------+----------+
4 rows in set (0.00 sec)
mysql> SELECT request_id, sum(unit_value * quantity) as x from test group by request_id;
+------------+--------------------+
| request_id | x |
+------------+--------------------+
| 1 | 25 |
| 2 | 14.070000171661377 |
+------------+--------------------+
2 rows in set (0.00 sec)
回答by Kermit
ID REQUEST_ID CLAIM_VALUE 3 10003 9.00 5 10003 5.07
You want to use SUM
你想用 SUM
SELECT
request_id,
SUM(unit_cost * quantity) AS claim_value
FROM `xx_non_part_usage`
WHERE request_id = request_id
GROUP BY request_id
You shouldn't be using GROUP BYunless you are using an aggregate function.
GROUP BY除非使用聚合函数，否则不应使用。
