17,377
社区成员
发帖
与我相关
我的任务
分享
select least(nvl(a,b),nvl(b,a)) from dual
select greatest(nvl(a,b),nvl(b,a)) from dual
这个吧,还算简单。
WITH test AS(
SELECT to_date('2014-03-21','yyyy-mm-dd') a,to_date('2014-03-21','yyyy-mm-dd') b FROM dual UNION ALL
SELECT to_date('2014-03-19','yyyy-mm-dd') a,to_date('2014-03-11','yyyy-mm-dd') b FROM dual UNION ALL
SELECT NULL a,to_date('2014-03-21','yyyy-mm-dd') b FROM dual UNION ALL
SELECT to_date('2014-03-21','yyyy-mm-dd') a,NULL b FROM dual )
SELECT decode(greatest(a,b),a,b,b,a) FROM test
[/quote]
佩服,佩服,思路很好WITH ta AS
(
SELECT 1 a,2 b FROM dual UNION ALL
SELECT 2 a,1 b FROM dual UNION ALL
SELECT 1 a,NULL b FROM dual UNION ALL
SELECT NULL a,NULL b FROM dual UNION ALL
SELECT NULL a,1 b FROM dual
)
SELECT coalesce (LEAST(a,b),a,b) FROM ta
WITH ta AS
(
SELECT 1 a,2 b FROM dual UNION ALL
SELECT 2 a,1 b FROM dual UNION ALL
SELECT 1 a,NULL b FROM dual UNION ALL
SELECT NULL a,NULL b FROM dual UNION ALL
SELECT NULL a,1 b FROM dual
)
SELECT LEAST(NVL(b,a),NVL(a,b)) FROM ta
WITH test AS(
SELECT to_date('2014-03-21','yyyy-mm-dd') a,to_date('2014-03-21','yyyy-mm-dd') b FROM dual UNION ALL
SELECT to_date('2014-03-19','yyyy-mm-dd') a,to_date('2014-03-11','yyyy-mm-dd') b FROM dual UNION ALL
SELECT NULL a,to_date('2014-03-21','yyyy-mm-dd') b FROM dual UNION ALL
SELECT to_date('2014-03-21','yyyy-mm-dd') a,NULL b FROM dual )
SELECT decode(greatest(a,b),a,b,b,a) FROM test