mysql - 用上一行mysql的值填充空行

Question

有一个单列的表，几乎没有 null 和空值。

colName
null
null
A
null
-
B
null

我需要用上面的非空值填充 null 或空值。

colName
null
null
A
A
A
B
B

我尝试了以下查询，但失败了。

SELECT  
(CASE WHEN colName IS NULL
THEN @prevValue
ELSE @prevValue := colName END) Value
FROM myValues t1
CROSS JOIN (SELECT @prevValue := NULL) t2

Answer 1

/*由于该表不包含任何主列或标识列，因此无法确定哪个值是前一个值。这将是随机的。

上面设置的值很小，这就是为什么下面的查询可以正常工作的原因。 如果它更大，那么继续并放置一个标识列和
将查询中的行号列替换为标识列*/

SELECT 
CASE 
    WHEN myValues1.colName IS NULL AND myValues2.colName IS NOT NULL THEN   myValues2.colName 
    /**Selecting the previous NOT NULL value for a NULL value*/  
    WHEN myValues1.colName='' AND myValues2.colName IS NOT NULL THEN myValues2.colName  
    /**Selecting the previous NOT NULL value for an empty value*/ 
    WHEN myValues1.colName='' AND myValues2.colName IS NULL THEN (SELECT TOP 1 colName FROM (SELECT colname,
                                row_number() OVER (ORDER BY (SELECT 0)) AS rowNumber 
                                FROM myValues) myValues3 WHERE myValues3.rowNumber < myValues2.rowNumber 
                                AND myValues3.rowNumber IS NOT NULL 
                                AND myValues3.rowNumber<>'' ORDER BY myValues3.rowNumber DESC)
/**Selecting the prior NOT NULL value for an empty value*/ 
    WHEN myValues1.colName IS NULL AND myValues2.colName='' THEN (SELECT TOP 1 colName FROM (SELECT colname,
                                row_number() OVER (ORDER BY (SELECT 0)) AS rowNumber 
                                FROM myValues) myValues3 WHERE myValues3.rowNumber < myValues2.rowNumber 
                                AND myValues3.rowNumber IS NOT NULL 
                                AND myValues3.rowNumber<>'' ORDER BY myValues3.rowNumber DESC)
    /**Selecting the priorNOT NULL value for a NULL value*/  
    ELSE myValues1.colName   
    /**Selecting the same value if is not NULL or an empty value*/  
END AS colName  FROM   

(SELECT colName,  
row_number() OVER (ORDER BY (SELECT 0)) AS rowNumber   
FROM myValues) myValues1 /**Setting the row number as it is displayed*/  

LEFT OUTER JOIN   

(SELECT colName,  
row_number() OVER (ORDER BY (SELECT 0)) AS rowNumber  
FROM myValues) myValues2 /**Setting the row number as it is displayed*/  
ON myValues1.rowNumber=myValues2.rowNumber+1  /**Joining in a way to get the previous   value*/