MySQL 使用派生表和 ORDER BY 进行更新

Question

此问题是 Link 的后续问题。我有一张表，其中包含不同时间点 t 的人员 (id) 和一个特征 (var0)。在某些时间点，该特征缺失，我想用以前的值来填补空白。下面是一个表格示例:

+---+---+----+            +----+---+------+------+------------------+
|id | t |var0|            | id | t | var0 | var1 | @prev_id   := id |
+---+---+----+            +----+---+------+------+------------------+
| 1 | 1 | a  |            |  1 | 1 | a    | a    |                1 |
| 1 | 3 | \N |            |  1 | 3 | \N   | a    |                1 |
| 1 | 7 | \N |            |  1 | 7 | \N   | a    |                1 |
| 1 | 8 | b  |            |  1 | 8 | b    | b    |                1 |
| 1 | 9 | \N |            |  1 | 9 | \N   | b    |                1 |
| 2 | 2 | \N |            |  2 | 2 | \N   | \N   |                2 |
| 2 | 4 | u  |            |  2 | 4 | u    | u    |                2 |
| 2 | 5 | u  |            |  2 | 5 | u    | u    |                2 |
| 2 | 6 | \N |            |  2 | 6 | \N   | u    |                2 |
| 2 | 7 | \N |            |  2 | 7 | u    | u    |                2 |
| 2 | 8 | v  |            |  2 | 8 | v    | v    |                2 |
| 2 | 9 | \N |            |  2 | 9 | \N   | v    |                2 |
+---+---+----+            +----+---+------+------+------------------+

左表是原始的x1表，右表是请求的表。下面是获取结果的代码:

DROP TABLE IF EXISTS test01.x1;
CREATE TABLE test01.x1 (
  id   INTEGER
, t    INTEGER
, var0 CHAR(1)
) ENGINE = InnoDB 
DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci
;

INSERT INTO test01.x1(id,t,var0) VALUES
( 1,1,'a' )
,(1,3,NULL)
,(1,7,NULL)
,(1,8,'b' )
,(1,9,NULL)
,(2,2,NULL)
,(2,4,'u' )
,(2,5,'u' )
,(2,6,NULL)
,(2,7,'u')
,(2,8,'v' )
,(2,9,NULL)
;

DROP TABLE IF EXISTS test01.x2;
CREATE TABLE test01.x2
SELECT id, t
       , var0
       , @prev_var0 := CAST(IF(id = @prev_id AND var0 IS NULL AND @prev_var0 IS NOT NULL 
                           , @prev_var0
                               , var0 
                               ) AS CHAR
                            )  var1
       , @prev_id   := id
FROM test01.x1, (SELECT @prev_id    := NULL
                    ,@prev_var0 := NULL
        ) init
ORDER BY id, t
;

ALTER TABLE test01.x2 MODIFY var1 CHAR(1) DEFAULT NULL;


DROP TABLE IF EXISTS test01.x2;
CREATE TABLE test01.x2
SELECT * FROM test01.x1;


UPDATE test01.x1, (SELECT   @prev_id    := NULL
                          , @prev_var0  := NULL
          ) init
SET var0 = @prev_vr0 := IF(id = @prev_id AND var0 IS NULL AND @prev_var0 IS NOT NULL 
                           , @prev_var0
                               , var0 
                               )
    , @prev_id   := id

ORDER BY id, t

我对另一个解决方案感兴趣。为了创建新表 x2，我想更新表 x1 的 var0。我试过这个:

UPDATE test01.x1, (SELECT   @prev_id    := NULL
                          , @prev_var0  := NULL
          ) init
SET var0 = @prev_vr0 := IF(id = @prev_id AND var0 IS NULL AND @prev_var0 IS NOT NULL 
                           , @prev_var0
                               , var0 
                               )
    , @prev_id   := id                 
ORDER BY id, t

但是它不起作用有两个原因(也许还有其他原因):

• 多个表 UPDATE 中不允许使用 ORDER BY(请参阅 Link)
• @prev_id := id 不起作用。显然，在 SET 语句中不可能直接为用户定义的变量赋值。

有人知道如何获得没有间隙的左表吗？

感谢您的帮助。

Answer 1

您始终可以使用存储过程或函数:

声明一个存储函数:

DELIMITER //
 CREATE FUNCTION fillGap(
   gapID INT, verID INT
 ) RETURNS VARCHAR(255)
 BEGIN
   DECLARE gapValue VARCHAR(255);

 -- gets the value
 SELECT var0
 FROM x1
  WHERE id = gapID AND t <= verID AND var0 IS NOT NULL 
  ORDER BY t DESC
  LIMIT 1
 INTO
  gapValue;

  RETURN gapValue;
END //
DELIMITER ;

然后您可以在 UPDATE 语句中调用它:

UPDATE x1 SET var0 = fillGap(id, t) WHERE var0 IS NULL

此函数从数据库获取一个前一个值，假设 t 是版本号，id 是 object_id。

在 (id=2, t=2) 的情况下会出现此问题，因为该对象 id 没有先前值。在任何情况下 - 编辑提供的函数并添加所需的逻辑。