오라클 dual + 테이블 질문이요!!

Question

SELECT TO_CHAR(o.created_at, 'MM-DD') AS 날짜, SUM(o.quantity) AS 수량, SUM(o.payment_price) AS 금액 FROM orders o, products p WHERE o.product_id &#61; p.id(&#43;) AND o.created_at BETWEEN to_date(to_char(sysdate -7, 'YYYYMMDD') || '000000', 'YYYYMMDDHH24MISS') AND TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD') || '235959', 'YYYYMMDDHH24MISS') GROUP BY TO_CHAR(o.created_at, 'MM-DD') ORDER BY TO_CHAR(o.created_at, 'MM-DD') ASC해당쿼리를 실행시날짜 수량 금액10-21 9 0 이나옴니다 select to_date(sysdate &#43; 1 - level,'yyyy-mm-dd') as 날짜 from dual connect by level <&#61;7 union해당쿼리를 실행하면 날짜20/10/2720/10/2620/10/2520/10/2420/10/2320/10/2220/10/21이런식으로 나옴니다 이걸 두개를 태이블을 합처서 날짜 수량 금액20/10/27 0 020/10/26 0 020/10/25 0 020/10/24 0 020/10/23 0 020/10/22 0 020/10/21 9 0 이런식으로 하려고합니다. 어떻해야할까요?..

햇개발자 · Answer

위 쿼리의 결과로 나오는 날짜 형태를 밑에 쿼리의 날짜 형식으로 맞추고, 두 쿼리 결과로 나온 결과들을 날짜를 이용하여 조인하면 될 것 같습니다.

니콜키크드만 · Answer

with result1 as ( select '10-21' as date, 9 qty, 0 price), result2 as ( select '2020-10-27' as date union all select '2020-10-26' as date union all select '2020-10-25' as date union all select '2020-10-24' as date union all select '2020-10-23' as date union all select '2020-10-22' as date union all select '2020-10-21' as date) select b.date , a.qty , a.price from result1 a , result2 bwhere '2020-'||a.date(&#43;) &#61; b.date; 햇개발자 님 말씀대로 하시면 대충 이렇습니다.

맨땅의헤딩 · Answer

with orders as ( SELECT TO_CHAR(o.created_at, 'yy/mm/dd') AS created_at, SUM(o.quantity) AS quantity, SUM(o.payment_price) AS payment_price FROM orders o, products p WHERE o.product_id &#61; p.id(&#43;) AND o.created_at BETWEEN to_date(to_char(sysdate -7, 'YYYYMMDD') || '000000', 'YYYYMMDDHH24MISS') AND TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD') || '235959', 'YYYYMMDDHH24MISS') GROUP BY TO_CHAR(o.created_at, 'yy/mm/dd') ) , duals as ( select to_date(sysdate &#43; 1 - level,'yy/mm/dd') as today, 0 as quantity, 0 as payment_price from dual connect by level <&#61;7 ) select d.today , o.quantity , o.payment_price from orders o , duals d where o.created_at(&#43;) &#61; d.today;ORA-32039: 순환 WITH 절에는 열 별칭 목록이 있어야 합니다.32039. 00000 - &#34;recursive WITH clause must have column alias list&#34;*Cause: A WITH clause query referred to itself (recursive) but did not have a column alias list specified for it.*Action: Add a column alias list for the WITH clause query name.53행, 9열에서 오류 발생 라는 오류가나오네요... 53행이 FROM orders o, 이쪽입니다.. ㅠㅠ

성능최적화계획 · Answer

select b.today , nvl(a.quantity, 0) , nvl(a.payment_price, 0) from (select to_char(sysdate- 3, 'yyyymmdd') as created_at , 9 as quantity , 0 as payment_price from dual ) a , (select sysdate - level as today, 0 as quantity, 0 as payment_price from dual connect by level <&#61;7 ) b where a.created_at(&#43;) &#61; to_char(b.today, 'yyyymmdd')

맨땅의헤딩 · Answer

감사합니다!!! select a.created_at as created_at, DECODE(b.quantity, null, 0, b.quantity) as quantity, DECODE(b.payment_price, null, 0, b.payment_price) as payment_price from( select TO_CHAR(sysdate &#43; 1 - level,'yy/mm/dd') as created_at, 0 as quantity, 0 as payment_price from dual connect by level <&#61; 7 )a, ( SELECT TO_CHAR(o.created_at, 'yy/mm/dd') AS created_at, SUM(o.quantity) AS quantity, SUM(o.payment_price) AS payment_price FROM orders o, products p WHERE o.product_id &#61; p.id(&#43;) AND o.created_at BETWEEN to_date(to_char(add_months(sysdate,-1), 'YYYYMMDD') || '000000', 'YYYYMMDDHH24MISS') AND TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD') || '235959', 'YYYYMMDDHH24MISS') GROUP BY TO_CHAR(o.created_at, 'yy/mm/dd') )b where a.created_at &#61; b.created_at(&#43;);성공했습니다

오라클 dual + 테이블 질문이요!!

5개의 답변

Q&A 목록