代码之家 › 专栏 › 技术社区 › Community wiki

使用SUM和LIMIT更新,滚动SUM

sql-update mysql

Community wiki · 技术社区 · 2 年前

我有两张桌子, SVISE 和 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

总和=21.85 .

期望的最终结果 在…上 斯维斯 ,基于上述值:

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

我可以得到特定人的总金额,但我不能使用限制来获得最后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)

这行不通。

我试过了 multi-table updates 和 nested queries 以实现这一点。每个场景都有已知的局限性,这些局限性阻碍了我实现期望的结果。

嵌套查询铁路超高看见父表。 Unknown column 'SV.hid'in 'where clause'
多表更新不能与限制一起使用。 Incorrect usage of UPDATE and LIMIT

任何解决方案都可以。不需要在单个查询中完成。如果有人想尝试使用中间表。

一 SQL fiddle 也可用。

提前感谢您的帮助。

--更新--

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

4 回复 | 直到 11 年前

Akash 11 年前

这应该起作用,

已更新 加入 svice

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

注: 请将此与您更新的数据进行核对,由于日期条件的原因,所提供的测试数据无法产生您预期的结果

Mihai 11 年前

尝试

UPDATE svice SV
JOIN (SELECT SUM(degrees)sumdeg,p_id FROM(SELECT DISTINCT degrees,p_id FROM OVERW,svice WHERE OVERW.p_id IN (SELECT svice.hid FROM svice) 
AND date(mo_date)<date(svice.start) 
AND year(mo_date)=year(svice.start)ORDER BY mo_date DESC )deg  group by p_id)bbc 
      ON bbc.p_id=SV.hid 
      SET 
SV.ofh=bbc.sumdeg where p_id =SV.hid

http://sqlfiddle.com/#!2/95b42/42

越来越近,现在它“只”需要GROUP BY中的一个限制。

Strawberry 11 年前

两个假设:

你可以弄清楚如何将其转化为更新,并且
PK存在于(id,mo_date)

那你就可以这么做了-

SELECT p_id
     , SUM(degrees) ttl
  FROM 
     ( SELECT x.*
         FROM overw x 
         JOIN overw y 
           ON y.p_id = x.p_id 
          AND y.mo_date >= x.mo_date 
        GROUP 
           BY p_id
            , mo_date HAVING COUNT(*) <= 3
     ) a 
 GROUP 
    BY p_id;

Strawberry 11 年前

也许我行动迟缓,但让我们暂时忽略svice。你能展示正确的结果和下面每一行的工作吗。。。

+------+---------+------------+--------+
| p_id | degrees | mo_date    | result |
+------+---------+------------+--------+
|    5 |    6.20 | 2013-06-10 |      ? | 
|    5 |    5.45 | 2013-08-11 |      ? |
|    5 |   10.20 | 2013-10-09 |  21.85 | <- = 10.2+5.45+6.2 = 21.85 
|    8 |   14.75 | 2013-04-25 |      ? |
|    5 |    9.85 | 2013-03-10 |      ? |
|    8 |   11.00 | 2013-02-22 |      ? |  
+------+---------+------------+--------+