SELECT
SEQ ,
CODE ,
DATE ,
CASE WHEN TO_CHAR(SYSDATE,'YYMMDD') = TO_CHAR(A.DATE,'YYMMDD') THEN
TO_CHAR(A.DATE,'AM HH24:MI')
ELSE
TO_CHAR(A.DATE,'YYYY-MM-DD')
END AS DATE_AS
FROM TABLE
쿼리의 결과 화면입니다.
SEQ CODE DATE DATE_AS
1 ABC$ 2017-09-14 오전 10:14:37 2017-09-14
2 DS!2 2017-09-14 오전 10:14:37 2017-09-14
3 CDX4 2017-09-15 오전 10:13:24 오전 10:13
4 ABD# 2017-09-15 오전 10:14:35 오전 10:14
5 #21A 2017-09-15 오전 10:14:37 오전 10:14
SEQ CODE DATE DATE_AS
NULL-------------------------------------------- 2017-09-14
1 ABC$ 2017-09-14 오전 10:14:37 오전 10:14
2 DS!2 2017-09-14 오전 10:14:37 오전 10:14
NULL-------------------------------------------- 2017-09-15
3 CDX4 2017-09-15 오전 10:13:24 오전 10:13
4 ABD# 2017-09-15 오전 10:14:35 오전 10:14
5 #21A 2017-09-15 오전 10:14:37 오전 10:14
위와 같이 결과를 조회하고 싶은데 어떻게 쿼리를 작성해야할지 감이 안와서 질문드립니다. 도와주세요~
with t(dt) as ( select sysdate - 1 from dual union all select sysdate - 1.2 from dual union all select sysdate - 1.4 from dual union all select sysdate from dual union all select sysdate + 0.2 from dual union all select sysdate + 0.4 from dual ) select dt, nvl(to_char(dt,'AM HH:MI'),to_char(dt,'YYYY-MM-DD')) tmm from t --group by to_char(dt,'YYYY-MM-DD'), rollup((dt, to_char(dt,'AM HH24:MI'))) --중복 시간이 있을 수도 있군요~ 마농님댓글 보고 수정 group by to_char(dt,'YYYY-MM-DD'), rollup((dt, rownum)) order by to_char(dt,'YYYY-MM-DD'), dt nulls first
WITH T AS ( SELECT TO_DATE('2017-09-14 10:14:37','YYYY-MM-DD HH24:MI:SS') DT FROM DUAL UNION ALL SELECT TO_DATE('2017-09-14 10:14:37','YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL SELECT TO_DATE('2017-09-15 10:13:24','YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL SELECT TO_DATE('2017-09-15 10:14:35','YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL SELECT TO_DATE('2017-09-15 10:14:37','YYYY-MM-DD HH24:MI:SS') FROM DUAL ) SELECT DECODE(GB,1,NULL,DT1) AS "DATE" , DATE_AS FROM ( SELECT 2 GB , TO_CHAR(A.DT,'YYYY-MM-DD AM HH24:MI:SS' ) AS DT1 , TO_CHAR(A.DT,'AM HH24:MI' ) DATE_AS FROM T A UNION ALL SELECT DISTINCT 1 GB , TO_CHAR(B.DT,'YYYY-MM-DD') , TO_CHAR(B.DT,'YYYY-MM-DD') FROM T B ORDER BY DT1 , GB )
WITH t AS ( SELECT 1 seq, 'ABC$' code, TO_DATE('2017-09-14 10:14:37', 'yyyy-mm-dd hh24:mi:ss') dt FROM dual UNION ALL SELECT 2, 'DS!2', TO_DATE('2017-09-14 10:14:37', 'yyyy-mm-dd hh24:mi:ss') FROM dual UNION ALL SELECT 3, 'CDX4', TO_DATE('2017-09-15 10:13:24', 'yyyy-mm-dd hh24:mi:ss') FROM dual UNION ALL SELECT 4, 'ABD#', TO_DATE('2017-09-15 10:14:35', 'yyyy-mm-dd hh24:mi:ss') FROM dual UNION ALL SELECT 5, '#21A', TO_DATE('2017-09-15 10:14:37', 'yyyy-mm-dd hh24:mi:ss') FROM dual ) SELECT seq, code, dt , NVL(TO_CHAR(dt, 'am hh:mi'), TO_CHAR(dt, 'yyyy-mm-dd')) date_as FROM t GROUP BY TO_CHAR(dt, 'yyyy-mm-dd'), ROLLUP((seq, code, dt)) ORDER BY TO_CHAR(dt, 'yyyy-mm-dd'), dt NULLS FIRST ;