mysql - 使用 SUM 和 LIMIT 更新，滚动 SUM

Question

我有 2 个表，SVISE 和 OVERW

在 OVERW 里面，我有一些乐谱，其中包含人物 ID 和乐谱的日期。例如

p_id    degrees    mo_date
5        10.2      2013-10-09
5        9.85      2013-03-10
8        14.75     2013-04-25
8        11.00     2013-02-22
5        5.45      2013-08-11
5        6.2       2013-06-10

SVISE.ofh 字段必须更新为最后三个记录的总和(对于特定的人，按日期降序排列)，因此对于 id 为 5 的人，总和将从行中得出

5        10.2      2013-10-09
5        5.45      2013-08-11
5        6.2       2013-06-10

sum=21.85。

在 SVISE 上的

期望的最终结果，基于上述值:

HID OFH     START
5   21.85   October, 16 2013   ##(10.2 + 5.45 + 6.2)
5   21.5    September, 07 2013 ##(5.45 + 6.2 + 9.85)
5   0       March, 05 2013     ##(no rows)
8   25.75   October, 14 2013   ##(14.75 + 11)
3   0       October, 14 2013   ##(no rows)
5   0       March, 05 2012     ##(no rows)

OFH最初为 0

我可以获得特定人员的总和，但我无法使用 limit 来获取最后 3 行。它被忽略了。

这是我用来检索给定日期的每个人所有学位的总和的查询:

UPDATE SVISE SV
SET 
SV.ofh=(SELECT sum(degrees) FROM OVERW WHERE p_id =SV.hid 
AND date(mo_date)<date(SV.start) 
AND year(mo_date)=year(SV.start))

我不能只对总和使用限制:

UPDATE SVISE SV
SET 
SV.ofh=(SELECT sum(degrees) FROM OVERW WHERE p_id =SV.hid 
AND date(mo_date)<date(SV.start) 
AND year(mo_date)=year(SV.start)
ORDER BY mo_date DESC 
LIMIT 3)

这行不通。

我已经尝试使用多表更新 和嵌套查询 来实现这一点。每个场景都有已知的局限性，这些局限性阻碍了我实现预期的结果。

• 嵌套查询无法看到父表。 “where 子句”中的未知列“SV.hid”
• 多表更新不能使用限制。 UPDATE 和 LIMIT 的错误使用

任何解决方案都可以。无需在单个查询中执行此操作。如果有人想尝试甚至使用中间表。

安SQL fiddle也可用。

预先感谢您的帮助。

--更新--

这是 Akash 的解决方案:http://sqlfiddle.com/#!2/4cf1a/1

Answer 1

这应该可以，

更新加入服务

UPDATE
svice SV
JOIN (
  SELECT 
    hid,
    start,
    sum(degrees) as degrees
  FROM 
    (
      SELECT 
        *,
        IF(@prev_row <> unix_timestamp(start)+P_ID, @row_number:=0,NULL),
        @prev_row:=unix_timestamp(start)+P_ID,
        @row_number:=@row_number+1 as row_number
      FROM 
        (
        SELECT
          mo_date,
          p_id,
          hid,
          start,
          degrees
        FROM 
          OVERW 
          JOIN svice sv ON ( p_id = hid 
                            AND date(mo_date)<date(SV.start) 
                            AND year(mo_date)=year(SV.start) )
         ORDER BY
          hid,
          start,
          mo_date desc
        ) sub_query1
        JOIN ( select @row_number:=0, @prev_row:=0 ) sub_query2
      ) sub_query
    where
      row_number <= 3
    GROUP BY 
      hid,
      start
  ) sub_query ON ( sub_query.hid = sv.hid AND sub_query.start = sv.start )
SET 
  SV.ofh = sub_query.degrees

注意:用您更新的数据检查一下，由于日期条件，提供的测试数据无法产生您预期的结果