mydb=# select * from sales;
ymd | amt
------------+-----
2004-02-02 | 200
2004-02-02 | 500
2004-02-05 | 100
2004-02-05 | 100
2004-02-08 | 600
2004-02-08 | 100
(6건 있음)
작업시간: 1.954 ms
mydb=# select a.no,coalesce(b.amt,0) as amt
from
(select to_date('200402' || to_char(no, 'FM00'), 'YYYYMMDD') as no
from generate_series(1,10) as a (no)) a
left outer join
(select ymd,sum(amt) as amt
from sales group by ymd) b
on (a.no = b.ymd);
no | amt
------------+-----
2004-02-01 | 0
2004-02-02 | 700
2004-02-03 | 0
2004-02-04 | 0
2004-02-05 | 200
2004-02-06 | 0
2004-02-07 | 0
2004-02-08 | 700
2004-02-09 | 0
2004-02-10 | 0
(10건 있음)
참하지요? 필요했던 기능이었는데, 이제 추가되었군요. 활용 범위가 무궁무진한데, 당장에 생각나는 것이 이것 뿐이군요.
|