php - 从 GROUP BY 获取最后一个值

Question

我有这样的 table

id  Name       Taste      price        Qty
1   Hot-Dog    Spicy      500           3
2   Pop-Corn   Caramel    400           2
3   Hot-Dog    Non-Spicy  600           1      
4   Hot-Dog    Spicy      520           4     
5   Pop-Corn   Salty      350           5

如何制作返回行

Name       Taste      price    Qty
Hot-Dog    Spicy      520       7  <--- Group by name and taste, sum(qty), with lastest price 
Pop-Corn   Caramel    400       2
Hot-Dog    Non-Spicy  600       1
Pop-Corn   Salty      350       5

$sql = mysqli_query($con,"SELECT *,sum(qty) as qtys FROM sometable GROUP BY name,taste");

简单地说..按名称返回组，并使用“来自同一项目的最新价格(最大 ID)”和总和(数量)

如何获得最新价格。我尝试 min(),max() 它返回最小值或最大值。

Answer 1

使用 order by desc limit 1 将第一个元素。

示例:

select*,sum(qty) as qtys from sometable GROUP BY name ORDER BY id desc LIMIT 1

由于价格是由最新的条目决定的，所以您只需要选择 by name = 'hot dog' 例如，sum qty，order by id desc 和 limit one result。

 select*,sum(qty)as qtys, price 
 fromsometable 
 where name ='Hot-Dog'  orderB by  id desc LIMIT 1

希望这对您有所帮助。