Notice
Recent Posts
Recent Comments
Link
«   2024/04   »
일 월 화 수 목 금 토
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30
Tags more
Archives
Today
Total
관리 메뉴

moozi

년도,상품별 판매갯수합계 판매되지 않은 상품도 출력하기 본문

TIS_2021/인공지능2021_1기

년도,상품별 판매갯수합계 판매되지 않은 상품도 출력하기

moozi 2021. 3. 31. 18:17 
-- 오라클문법
with
table1 as (select distinct t1.year, goods.name
                from
                (
                select a.year, a.name, sum(ea) as count
                from (select to_char(orders.orderdate,'YYYY') year, goods.name, ordersDetail.ea
                        from orders join ordersDetail
                        on orders.ono=ordersDetail.ono
                        join goods
                        on ordersDetail.gno=goods.gno) a
                group by a.year, a.name
                order by a.year, a.name) t1 cross join goods
                order by t1.year, goods.name),
table2 as(
                select a.year, a.name, sum(ea) as count
                from (select to_char(orders.orderdate,'YYYY') year, goods.name, ordersDetail.ea
                        from orders join ordersDetail
                        on orders.ono=ordersDetail.ono
                        join goods
                        on ordersDetail.gno=goods.gno) a
                group by a.year, a.name
                order by a.year, a.name
)

select table1.year,table1.name,nvl(table2.count,0)
from table1 , table2
where table1.year=table2.year(+)
and table1.name=table2.name(+)
order by year,count;
        
-- ansi sql문법
with
table1 as (select distinct t1.year, goods.name
                from
                (
                select a.year, a.name, sum(ea) as count
                from (select to_char(orders.orderdate,'YYYY') year, goods.name, ordersDetail.ea
                        from orders join ordersDetail
                        on orders.ono=ordersDetail.ono
                        join goods
                        on ordersDetail.gno=goods.gno) a
                group by a.year, a.name
                order by a.year, a.name) t1 cross join goods
                order by t1.year, goods.name),
table2 as(
                select a.year, a.name, sum(ea) as count
                from (select to_char(orders.orderdate,'YYYY') year, goods.name, ordersDetail.ea
                        from orders join ordersDetail
                        on orders.ono=ordersDetail.ono
                        join goods
                        on ordersDetail.gno=goods.gno) a
                group by a.year, a.name
                order by a.year, a.name
)

select table1.year,table1.name,nvl(table2.count,0)
from table1 left outer join table2
on table1.year=table2.year
and table1.name=table2.name
order by year,count;

'TIS_2021 > 인공지능2021_1기' 카테고리의 다른 글

04/06 haksa  (0) 2021.04.06
oracle 연결  (0) 2021.04.06
자바연습문제풀이  (0) 2021.03.29
3/23 haksa  (0) 2021.03.23
Haksa  (0) 2021.03.18
'TIS_2021/인공지능2021_1기' Related Articles more
Comments