t1的结构:
year tname cnt
-----------------------
2005 type1 10
2005 type3 20
2006 type3 15
2006 type2 30
2007 type2 20
t2的结构
seq tname
---------------------
1 type1
2 type2
3 type3
如何连接查询两表,得到结果如下:
year tname cnt
----------------------------
2005 type1 10
2005 type2
2005 type3 20
2006 type1
2006 type2 30
2006 type3 15
2007 type1
2007 type2 20
2007 type3
解决方法
WITH a AS (
SELECT '2005' YEAR ,'type1' TYPE ,10 cnt FROM dual
UNION
SELECT '2006' YEAR ,'type1' TYPE ,20 FROM dual
UNION
SELECT '2006' YEAR ,'type2' TYPE ,100 FROM dual
UNION
SELECT '2007' YEAR ,'type1' TYPE ,30 FROM dual
)
SELECT a.year,b.typename,CASE WHEN sum(decode(a.TYPE,b.typename,a.cnt,'0')) = 0 THEN '' WHEN sum(decode(a.TYPE,b.typename,a.cnt,'0')) >0 THEN ''||sum(decode(a.TYPE,b.typename,a.cnt,'0')) END FROM a a ,(SELECT 'type1' typename FROM dual UNION SELECT 'type2' FROM dual UNION SELECT 'type3' FROM dual) b
GROUP BY a.year,b.typename ORDER BY a.year,b.typename