时间戳

首页 > 解决方案 > 我想从 mysql db 更新我的表中的一个字段 - 但问题陈述在正文中如下所示

mysql - 我想从 mysql db 更新我的表中的一个字段 - 但问题陈述在正文中如下所示

问题描述

下面的数据解释了我的问题和所需的输出。请在下面查看更多详细信息

+------------+-----+--------------+-------------------+
|    Date    | ID  | Payment Done | The Problem Field |
+------------+-----+--------------+-------------------+
| 2020-02-15 | 111 |            1 |                 0 |
| 2020-03-15 | 111 |            0 |                 0 |
| 2020-04-15 | 111 |            0 |                -1 |
| 2020-03-15 | 222 |            0 |                 0 |
| 2020-03-31 | 222 |            0 |                -1 |
| 2020-04-14 | 222 |            1 |                 0 |
| 2020-02-29 | 333 |            0 |                 0 |
| 2020-03-15 | 333 |            0 |                -1 |
| 2020-03-35 | 333 |            1 |                 0 |
| 2020-04-14 | 333 |            0 |                 0 |
+------------+-----+--------------+-------------------+

在此处输入图像描述

图片包含解释我遇到的问题的数据。对于给定的 ID，如果在 D1 日期未完成付款，并且在前一个日期 D2（D2

因此，无论问题字段为 -1，这意味着在该日期和前一个日期（日期之间的差异并不总是相同）期间，付款不是由该特定 ID 完成的。

我尝试使用查询来做到这一点，并花了半天多的时间。然后我用 Python 脚本来做这件事——但我的自我还不满足。我想使用查询来做到这一点 - 如果可能的话。

我的想法：

My approach to solving was to write a query that says the following:
Take a date D1 for an ID. 
Get the Max(date) for the same ID when the date is less than D1 - meaning I'll get the immediate lesser record's date. 
Then check if "payment done" is 0 in both the rows and if yes, then update the D1 row's problem field to -1.

顺便说一句，我对 MySQL 的经验并不多，请原谅我是一个幼稚的学习者。我使用的 MySQL 服务器版本是 5.6.41

我希望您看到我试图写的查询（某处出错 - 最里面的查询无法访问外部表的变量），说我上面提到的：

update my_table a set the_problem_field = -1 where 0 = (select payment_done from (select payment_done from  my_table where id = a.id and date =(select max(date) from  my_table  where id = a.id and date<a.date))T) and a.payment_done = 0;

相同的格式：

UPDATE my_table a 
SET    the_problem_field = -1 
WHERE  0 = (SELECT payment_done 
            FROM   (SELECT payment_done 
                    FROM   my_table 
                    WHERE  id = a.id 
                           AND date = (SELECT Max(date) 
                                       FROM   my_table 
                                       WHERE  id = a.id 
                                              AND date < a.date))T) 
       AND a.payment_done = 0;

我希望有人能帮我解决这个问题....拜托！很感谢任何形式的帮助。谢谢你的时间。

标签： mysqldatabase

解决方案

不需要存储过程。关键部分是产生所需结果的内部连接。

您可以通过“记住”最后一个 ID 和付款完成来意识到这一点

列名中不应包含空格，否则您必须始终记住要始终封装它们

UPDATE Paymnettable pt
        INNER JOIN
    (SELECT 
        `Date`,
            IF(@id = ID, IF(`Payment Done` = 1, 0, IF(@Payment_Done = 0, - 1, 0)), 0) `The Problem Field`,
            @id:=ID ID,
            @Payment_Done:=`Payment Done` 'Payment Done'
    FROM
        (SELECT 
        *
    FROM
        Paymnettable
    ORDER BY `ID` , `Date`) t1
      , (SELECT @Payment_Done:=0) t2
      , (SELECT @ID:=0) t3) pt1 
    ON pt.`Date` = pt1.`Date`
        AND pt.ID = pt1.ID 
SET 
    pt.`The Problem Field` = pt1.`The Problem Field`

架构（MySQL v5.6）

CREATE TABLE Paymnettable (
  `Date` DATE,
  `ID` INTEGER,
  `Payment Done` INTEGER,
  `The Problem Field` INTEGER
);

INSERT INTO Paymnettable
  (`Date`, `ID`, `Payment Done`, `The Problem Field`)
VALUES
  ('2020-02-15', '111', '1', '0'),
  ('2020-03-15', '111', '0', '0'),
  ('2020-04-15', '111', '0', '0'),
  ('2020-03-15', '222', '0', '0'),
  ('2020-03-31', '222', '0', '0'),
  ('2020-04-14', '222', '1', '0'),
  ('2020-02-29', '333', '0', '0'),
  ('2020-03-15', '333', '0', '0'),
  ('2020-03-31', '333', '1', '0'),
  ('2020-04-14', '333', '0', '0');

 UPDATE Paymnettable pt
        INNER JOIN
    (SELECT 
        `Date`,
            IF(@id = ID, IF(`Payment Done` = 1, 0, IF(@Payment_Done = 0, - 1, 0)), 0) `The Problem Field`,
            @id:=ID ID,
            @Payment_Done:=`Payment Done` 'Payment Done'
    FROM
        (SELECT 
        *
    FROM
        Paymnettable
    ORDER BY `ID` , `Date`) t1, (SELECT @Payment_Done:=0) t2, (SELECT @ID:=0) t3) pt1 ON pt.`Date` = pt1.`Date`
        AND pt.ID = pt1.ID 
SET 
    pt.`The Problem Field` = pt1.`The Problem Field`

查询 #1

SELECT 
        *
    FROM
        Paymnettable;

| Date       | ID  | Payment Done | The Problem Field |
| ---------- | --- | ------------ | ----------------- |
| 2020-02-15 | 111 | 1            | 0                 |
| 2020-03-15 | 111 | 0            | 0                 |
| 2020-04-15 | 111 | 0            | -1                |
| 2020-03-15 | 222 | 0            | 0                 |
| 2020-03-31 | 222 | 0            | -1                |
| 2020-04-14 | 222 | 1            | 0                 |
| 2020-02-29 | 333 | 0            | 0                 |
| 2020-03-15 | 333 | 0            | -1                |
| 2020-03-31 | 333 | 1            | 0                 |
| 2020-04-14 | 333 | 0            | 0                 |

在 DB Fiddle 上查看

推荐阅读