比较SQL中循环中的当前行值和前一行值

Question

我有一个包含字段-a(ID(、B(Flag(的表。我需要在这个表中添加一个新列-C(Result(，它的值将基于B(Flag(字段派生。若flag为false，那个么继续检查前面的行，直到我们得到flag为true，然后取A(ID(字段的值并将其填充到C(Result(列中。所以C将有A的最后一个值，B字段为True。

Required Table

<1><1>

A	B	C
1	T
2	F
3		F	1
		T	4
5	T	5
6	F	5
7	T	7
8	F	7
9	F	7
10		F	7
11		T	11

WITH
cte1 AS (
SELECT A, SUM(B='T') OVER (ORDER BY A) group_no
FROM test
),
cte2 AS (
SELECT A, MIN(A) OVER (PARTITION BY group_no) previous_T
FROM cte1
)
UPDATE test
JOIN cte2 USING (A)
SET test.C = cte2.previous_T;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=46c1edfbc42e331f9bb39a36ea71e905

在MySQL 5.x中使用

UPDATE test
JOIN (
SELECT A, 
@tmp := CASE WHEN B='T' THEN A ELSE @tmp END C
FROM test
JOIN (SELECT @tmp:=0) init
ORDER BY A
) data USING (A)
SET test.C = data.C;

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=3f341b98b768c2a2369b5d814453b234