SQL每日一题(20210708)
SQL每日一题(20210928)
SQL每日一题(20220624)
SQL每日一题(20220726)
SELECT Y.LDATE, ifnull(Y.VALUE1, Y.VV) AS "VALUE1", ifnull(Y.VALUE2, Y.VVV) AS "VALUE2"
FROM (SELECT X.*, ROW_NUMBER() OVER (PARTITION BY X.LDATE ORDER BY X.TT DESC, X.TTT DESC) RD
FROM (SELECT T2.*, T1.VALUE1 VV, T1.LDATE TT, T3.VALUE2 VVV, T3.LDATE TTT
FROM T1231 T1
JOIN T1231 T2 ON T1.LDATE <= T2.LDATE AND T1.VALUE1 IS NOT NULL
JOIN T1231 T3 ON T3.LDATE <= T2.LDATE AND T3.VALUE2 IS NOT NULL) X) Y
WHERE Y.RD = 1
ORDER BY Y.LDATE DESC;
SELECT t.LDate,
(SELECT t1.Value1
FROM t1231 t1
WHERE t1.LDate <= t.LDate
and t1.Value1 IS NOT null
ORDER BY t.LDate asc
LIMIT 1) AS Value1,
(SELECT t1.Value2
FROM t1231 t1
WHERE t1.LDate <= t.LDate and t1.Value2 IS NOT null
ORDER BY t.LDate asc
LIMIT 1) AS Value2 FROM t1231 t