据我了解,逻辑(忽略空值)是
SELECT CASE
WHEN field1 >= field2
AND field1 >= field3
THEN field1
WHEN field2 >= field3
THEN field2
ELSE field3
END AS greatest
FROM my_table;
…但仅当所有值均为null时,才应返回null。
我认为这是我更希望能够做的事情(尽管Sybase ASE不支持公用表表达式):
WITH my_table
AS
(
SELECT *
FROM (
VALUES ('A', 1, 2, 3),
('B', 2, 3, 1),
('C', 3, 1, 2),
('D', NULL, 2, 3),
('E', NULL, NULL, 3),
('F', NULL, 3, NULL),
('G', 1, NULL, 3),
('H', 1, 3, NULL),
('X', NULL, NULL, NULL)
) AS T (ID, field1, field2, field3)
),
T1
AS
(
SELECT ID, field1 AS field_n
FROM my_table
UNION
SELECT ID, field2 AS field_n
FROM my_table
UNION
SELECT ID, field3 AS field_n
FROM my_table
)
SELECT ID, MAX(field_n) AS greatest
FROM T1
GROUP
BY ID;