A 테이블
(코드하고 이름이 존재)
select 'A' code, 'bolt' name from dual union all
select 'B' , 'knife' name from dual union all
select 'C' , 'toy' from dual
B 테이블 (이름하고 부서진 날짜만 있음 데이터가 무수히 많음)
name broken_day
bolt 20170612
bolt 20160712
bolt 20120612
knife 20140101
knife 20140303
간략화했는데요
결과는
code name 총고장횟수 지난1년동안 고장횟수
A bolt 3 2
B knife 2 0
C toy 0 0 (없어도 나와야합니다)
간단할것 같은데 머에 꼽혔는지 잘 안나오네요.
고맙습니다.
WITH a AS ( SELECT 'A' code, 'bolt' name FROM dual UNION ALL SELECT 'B', 'knife' FROM dual UNION ALL SELECT 'C', 'toy' FROM dual ) , b AS ( SELECT 'bolt' name, '20170612' broken_day FROM dual UNION ALL SELECT 'bolt' , '20160712' FROM dual UNION ALL SELECT 'bolt' , '20120612' FROM dual UNION ALL SELECT 'knife', '20140101' FROM dual UNION ALL SELECT 'knife', '20140303' FROM dual ) SELECT a.code , a.name , COUNT(b.name) cnt , COUNT(CASE WHEN broken_day >= TO_CHAR(ADD_MONTHS(sysdate, -12), 'yyyymmdd') THEN 1 END) cnt_1 FROM a , b WHERE a.name = b.name(+) GROUP BY a.code, a.name ORDER BY a.code ;
with a as
(
select 'A' code, 'bolt' name from dual
union all select 'B', 'knife' from dual
union all select 'C', 'toy' from dual
)
, b as
(
select 'bolt' name, '20170612' broken_day from dual
union all select 'bolt' , '20160712' from dual
union all select 'bolt' , '20120612' from dual
union all select 'knife', '20140101' from dual
union all select 'knife', '20140303' from dual
)
select a.code
, a.name
, COUNT(b.name) cnt
, COUNT(case when broken_day >= TO_CHAR(ADD_MONTHS(sysdate, -12), 'yyyymmdd') then 1 end) cnt_1
from a
, b
where a.name = b.name(+)
and b.name = 'knife'
group by a.code, a.name
order by a.code
;
이렇게 하면 나이프만 나오는데 애초에 마농님이 올렸던 결과처럼 나오게 하고 싶습니다.
사실 이렇게 하면 아래 and b.name = 'knife' 가 의미가 없는데
그래도 처음 마농님이 보여주신 결과처럼 나오게 하려면 어떻게 해야할까요?