最新的速率和数量总和从mysql表

I have a table look like below:

我有一张表如下所示:

uid    added_on                rm_id     qnty        rate

1      2017-10-23 10:48:50     5         2           30
2      2017-10-23 10:48:50     6         4           70
3      2017-10-23 10:48:50     7         5           10
4      2017-10-24 11:02:10     5         10          28
5      2017-10-24 11:02:10     6         2           75
6      2017-10-24 11:03:37     7         1           15
7      2017-10-25 11:02:10     6         5           65
8      2017-10-25 11:03:37     7         8           12

I need the rm_id , its quantity (that is rm_id 5 is 12(2+10) ), and its last added rate(latest rate can find from the latest added_on rate or from last uid for each rm_id). Any way the result should look like below:

我需要rm_id，它的数量(rm_id 5是12(2+10))，以及它最后的添加速率(最新的速率可以从每个rm_id的最新added_on速率或最后的uid中找到)。无论如何，结果应该如下所示:

Result

结果

rm_id  total_qnty   rate

5      12           28
6      11           65
7      14           12

I tried to achieve this by using

我试图通过使用来实现这一点

SELECT `rm_id`, sum(`qnty`),`rate` FROM `stock` group by `rm_id` having max(`uid`)

and

和

  SELECT `rm_id`, sum(`qnty`),`rate` FROM `stock` group by `rm_id` having max(date(`added_on`))

But not getting the result as desired.. please help me..

但是没有得到想要的结果。请帮我. .

3 个解决方案

#1

You need to locate the max date, and from that determine the rate, and apply that rate to the summed quantity.

你需要找到最大日期，然后确定速率，然后把速率应用到求和的量上。

select
    t.rm_id, t.rate, gd.sum_qty, gd.sum_qty * t.rate
from table1 t
inner join (
    select rm_id, max(added_on) max_date, sum(qnty) sum_qty
    from table1
    group by rm_id
    ) gd on t.rm_id = gd.rm_id and t.added_on = gd.max_date

The data model is strange, why aren't rates separated?

数据模型很奇怪，为什么利率不分开?

#2

having max(uid) translates to having 8 for rm_id = 7. And MySQL treats numbers > 0 as true, so this becomes having true, i.e. don't limit my results in any way. The aggregated result doesn't contain the single rates anyway, so it's too late to try to get it via HAVING. You'd need an aggregation function for this, such as Oracle's KEEP LAST, but MySQL doesn't feature this.

拥有max(uid)意味着rm_id = 7拥有8。MySQL将> 0的数字视为真，因此这就变成了真，也就是说，不要以任何方式限制我的结果。聚合结果不包含单一利率，因此尝试通过拥有来获取它已经太晚了。您需要一个聚合函数，比如Oracle的KEEP LAST，但是MySQL没有这个功能。

What you want instead is to get the maximum uid and with its help select the related record:

相反，您想要的是获得最大uid，并通过它的帮助选择相关记录:

select
  stock.rm_id,
  stockagg.sum_qnty,
  stock.rate as last_rate
from 
(
  select 
    rm_id, 
    sum(qnty) as sum_qnty, 
    max(uid) as max_uid
  from stock 
  group by rm_id
) stockagg
join stock on stock.uid = stockagg.max_uid;

#3

You can use subqueries:

您可以使用子查询:

SELECT `rm_id`, sum(`qnty`),
  (SELECT `rate`
   FROM `stock` s1
   WHERE `uid` = (SELECT `uid`
                  FROM `stock` s2
                  WHERE s2.`rm_id` = s1.`rm_id`
                  ORDER BY `added_on` DESC
                  LIMIT 1)
  ) AS `rate`
FROM `stock`
GROUP BY `rm_id`

#1