晴风

需求:取表中三个时间字段中最大的一个,赋值到新字段

方案一:case when

SELECT  
CASE
    WHEN t1>t2 THEN CASE WHEN t1>t3 THEN t1 ELSE t3 END
    WHEN t2>t3 THEN t2
    ELSE t3
END maxt
FROM(
SELECT '2022-03-01' t1,'2022-02-01' t2,'2022-04-01' t3
);

方案二:行转列(SPARKSQL)

SELECT  
max(explode(a) b) maxt
from
( 
  select array(t1,t2,t3) a
  FROM
  (SELECT '2022-03-01' t1,'2022-02-01' t2,'2022-04-01' t3) 
);

测试表

CREATE TABLE test_0620(
id int,
t1 varchar(20),
t2 varchar(20),
t3 varchar(20),
maxt varchar(20)
);
INSERT INTO test_0620(id,t1,t2,t3) VALUES(1,'2022-03-01','2022-02-01','2022-04-01');
SELECT * FROM test_0620;

使用case when实现

UPDATE test_0620 SET
maxt=(
CASE
    WHEN t1>t2 THEN CASE WHEN t1>t3 THEN t1 ELSE t3 END
    WHEN t2>t3 THEN t2
    ELSE t3
END
)

后续:存在null的情况

-- 给null一个默认最小值处理
SELECT  
CASE
    WHEN IFNULL(t1,TO_DATE('1970-01-01'))>IFNULL(t2,TO_DATE('1970-01-01')) THEN CASE WHEN IFNULL(t1,TO_DATE('1970-01-01'))>IFNULL(t3,TO_DATE('1970-01-01')) THEN t1 ELSE t3 END
    WHEN IFNULL(t2,TO_DATE('1970-01-01'))>IFNULL(t3,TO_DATE('1970-01-01')) THEN t2
    ELSE t3
END maxt
FROM(
SELECT '2022-01-01' t1,NULL t2,NULL t3
);