クロス集計のサブクエリ、内部結合

create table a(a1 date,a2 number,a3 number);

insert into a values('2006/1/1',1,100);

insert into a values('2006/1/1',2,1000);

insert into a values('2006/1/4',1,100);

insert into a values('2006/1/5',3,3000);

create table b(b1 date);

insert into b values('2006/1/1');

insert into b values('2006/1/2');

insert into b values('2006/1/3');

insert into b values('2006/1/4');

insert into b values('2006/1/5');



select

	b1

,	sum(decode(a2,1,a3,0)) x1

,	sum(decode(a2,2,a3,0)) x2

,	sum(decode(a2,3,a3,0)) x3

from

	(select b1,a2,a3 from a,b where b.b1 = a.a1(+))

group by

	b1

;



B1               X1         X2         X3

-------- ---------- ---------- ----------

06-01-01        100       1000          0

06-01-02          0          0          0

06-01-03          0          0          0

06-01-04        100          0          0

06-01-05          0          0       3000

WITH DateTbl AS (
  SELECT distinct saletime FROM tbl_books 
),
PriceTbl AS (
	SELECT 
		SaleTime,
		SUM(DECODE(Genre,'Science',Price,'0')) col1,
		SUM(DECODE(Genre,'Language',Price,'0')) col2,
		SUM(DECODE(Genre,'History',Price,'0')) col3 
	FROM TBL_BOOKS
	WHERE
		OptionType = 2
		AND IssueDate >= TO_DATE('20060101','yyyymmdd') AND IssueDate < TO_DATE('20060201','yyyymmdd')
	GROUP BY SaleTime
)
SELECT 
	TO_CHAR(DateTbl.SaleTime),
	NVL(PriceTbl.col1,0) "Science",
	NVL(PriceTbl.col2,0) "Language",
	NVL(PriceTbl.col3,0) "History" 
FROM 
	DateTbl LEFT OUTER JOIN PriceTbl ON DateTbl.SaleTime = PriceTbl.SaleTime ;