/*
朋友負責的專案裡有個Table是這樣的：
在同一筆record中存在三個不同的欄位組，
報表則須要從這三個欄位組找出最接近系統日期的那一組。
她希望是用最精簡的SQL來處理掉這個需求，於是我就借來玩玩了。
1) 不使用 least function
2) 使用 least function
*/
/* ----- 1)不使用 least function ----- */
-- 1) 建立一個Table裡面存在三組不同的欄位組
DROP TABLE TRIPLE_REC
CREATE TABLE TRIPLE_REC
(
KEYNO INTEGER PRIMARY KEY,
TYPE1 INTEGER,
TYPEUSER1 VARCHAR2(5),
TYPEDT1 DATE,
TYPE2 INTEGER,
TYPEUSER2 VARCHAR2(5),
TYPEDT2 DATE,
TYPE3 INTEGER,
TYPEUSER3 VARCHAR2(5),
TYPEDT3 DATE
);
--測試新增兩筆資料
INSERT INTO TRIPLE_REC VALUES (1,1,'ADMIN',TO_DATE('20070831','YYYYMMDD'),
2,'XC001',TO_DATE('20080701','YYYYMMDD'),
3,'XC019',TO_DATE('20050729','YYYYMMDD'));
INSERT INTO TRIPLE_REC VALUES (2,1,'XC005',TO_DATE('20020821','YYYYMMDD'),
2,'XC008',TO_DATE('20080501','YYYYMMDD'),
3,'XC001',TO_DATE('20060729','YYYYMMDD'));
COMMIT;
--看一下資料內容
SELECT * FROM TRIPLE_REC ORDER BY KEYNO;
KEYNO TYPE1 TYPEUSER1 TYPEDT1 TYPE2 TYPEUSER2 TYPEDT2 TYPE3 TYPEUSER3 TYPEDT3
----- ----- --------- ------- ----- --------- ------- ----- --------- --------
1 1 ADMIN 2007/8/31 2 XC001 2008/7/1 3 XC019 2005/7/29
2 1 XC005 2002/8/21 2 XC008 2008/5/1 3 XC001 2006/7/29
--從三組不同的資料欄位中選一組日期最接近系統日的
SELECT *
FROM
(SELECT KEYNO,TYPE,TYPEUSER,TYPEDT,DIFF,
ROW_NUMBER() OVER(PARTITION BY KEYNO ORDER BY DIFF) CLOSEDAYDIFF
FROM (SELECT KEYNO,TYPE1 TYPE,TYPEUSER1 TYPEUSER,TYPEDT1 TYPEDT,
ABS(TRUNC(TYPEDT1) - TRUNC(SYSDATE)) DIFF
FROM TRIPLE_REC
UNION ALL
SELECT KEYNO,TYPE2 TYPE,TYPEUSER2 TYPEUSER,TYPEDT2 TYPEDT,
ABS(TRUNC(TYPEDT2) - TRUNC(SYSDATE)) DIFF
FROM TRIPLE_REC
UNION ALL
SELECT KEYNO,TYPE3 TYPE,TYPEUSER3 TYPEUSER,TYPEDT3 TYPEDT,
ABS(TRUNC(TYPEDT3) - TRUNC(SYSDATE)) DIFF
FROM TRIPLE_REC) TMP
) TMP2
WHERE CLOSEDAYDIFF = 1;
--得到以下結果
KEYNO TYPE TYPEUSER TYPEDT DIFF CLOSEDAYDIFF
------ ------ --------- ------------- --------- ------------
1 2 XC001 2008/7/1 56 1
2 2 XC008 2008/5/1 117 1
/* ----- 2)使用 least function ----- */
/* 建立測試table */
create table least_test
(
seqno integer,
id1 char(5),
col1 date,
id2 char(5),
col2 date,
id3 char(5),
col3 date,
id4 char(5),
col4 date
);
/* 建立測試資料 */
insert into least_test values (1,'00123',to_date('20080115','yyyymmdd'),
'00576',to_date('20080222','yyyymmdd'),
'00123',to_date('20080305','yyyymmdd'),
'00456',to_date('20080510','yyyymmdd'));
insert into least_test values (2,'00123',to_date('20080530','yyyymmdd'),
'00123',to_date('20080202','yyyymmdd'),
'00576',to_date('20071230','yyyymmdd'),
'00576',to_date('20080620','yyyymmdd'));
insert into least_test values (3,'00456',to_date('20081030','yyyymmdd'),
'00456',to_date('20080622','yyyymmdd'),
'00456',to_date('20081105','yyyymmdd'),
'00123',to_date('20080430','yyyymmdd'));
commit;
/* 用 least 找 col1,col2,col3,col4 中最接近系統日期的欄位*/
select seqno,case least(diff1,diff2,diff3,diff4)
when diff1 then id1
when diff2 then id2
when diff3 then id3
when diff4 then id4 end,
case least(diff1,diff2,diff3,diff4)
when diff1 then col1
when diff2 then col2
when diff3 then col3
when diff4 then col4 end
from (select seqno,id1,id2,id3,id4,col1,col2,col3,col4,
abs(trunc(col1) - trunc(sysdate)) diff1,
abs(trunc(col2) - trunc(sysdate)) diff2,
abs(trunc(col3) - trunc(sysdate)) diff3,
abs(trunc(col4) - trunc(sysdate)) diff4
from least_test) t
/* 結果 */
1 00456 2008/5/10
2 00576 2008/6/20
3 00456 2008/11/5
