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

有一个表的单列很少有空值和空值。

colName
null
null
A
null
-
B
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

最佳答案

/*由于表不包含任何主列或标识列，因此无法确定哪个值是前一个值。这将是随机的。
上面设置的值很小，这就是为什么below查询可以正常工作而没有任何问题的原因。
如果它更大，那么继续放一个标识栏
在查询中将行号列替换为标识列*/

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*/