在MySQL中选择每组具有特定值的行组 [英] Select groups of rows each with a certain value in MySQL

问题描述

说我在表中有以下几行:

Say I have the following rows in a table:

+----+--------------+-------+------+
| ID | fruit        | amount| price|
+----+--------------+-------+------+
|  1 | oranges      |     7 |  20  |
+----+--------------+-------+------+
|  2 | plums        |     21|  32  |
+----+--------------+-------+------+
|  3 | oranges      |     2 |  32  |
+----+--------------+-------+------+
|  4 | oranges      |     7 |  12  |
+----+--------------+-------+------+
|  5 | plums        |     4 |  89  |
+----+--------------+-------+------+
|  6 | grapes       |     5 |  34  |
+----+--------------+-------+------+ 
|  7 | grapes       |     6 |  132 |
+----+--------------+-------+------+
|  8 | plums        |     2 |  19  |
+----+--------------+-------+------+

我的最终目标是获取表中每个独特水果的总金额和总价格.

My end goal is to get total amounts and total prices for each unique fruit in a table.

现在，我正在考虑要获得预期效果的功能的顺序是:

Right now, the sequence of functions I was thinking of doing to get the desired effect is:

using PHP: SELECT * from table where fruit = "oranges". 

我将使用ajax来运行它，所以我将使用jquery来添加金额和价格.然后重复李子"和葡萄".

I will be using ajax to run this so I was going to use jquery to add the amount and price. Then repeat for "plums" and "grapes".

我要解决的问题之一是，我不知道水果表中会出现什么水果.

One of the issues i'll have is I won't know what fruits will be in the fruit table.

那么，有没有一种更简单的方法来查找列中的所有唯一水果，然后在数据库中搜索包含每个水果的行，然后为每个水果添加总计?

So is there a simpler way to find all unique fruits in the column, then search the database for rows with each of those fruits and then add totals for each?

推荐答案

您还可以获取分组的结果，然后对其进行迭代以显示

You can also get the grouped result and then iterate over it for display

select fruit, sum(amount), sum(amount*price)
from table 
group by fruit

这篇关于在MySQL中选择每组具有特定值的行组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！