table1的结构:
year type count
-----------------------
2005 type1 10
2005 type3 20
2006 type3 15
2006 type2 30
2007 type2 20table2的结构
typeseq typename
---------------------
1 type1
2 type2
3 type3如何连接查询两表,得到结果如下:
year type count
----------------------------
2005 type1 10
2005 type2
2005 type3 20
2006 type1
2006 type2 30
2006 type3 15
2007 type1
2007 type2 20
2007 type3
year type count
-----------------------
2005 type1 10
2005 type3 20
2006 type3 15
2006 type2 30
2007 type2 20table2的结构
typeseq typename
---------------------
1 type1
2 type2
3 type3如何连接查询两表,得到结果如下:
year type count
----------------------------
2005 type1 10
2005 type2
2005 type3 20
2006 type1
2006 type2 30
2006 type3 15
2007 type1
2007 type2 20
2007 type3
SQL> select * from table1; YEAR TYPE CNT
--------------------------------------- -------------------- ---------------------------------------
2005 type1 10
2005 type3 20
2006 type3 15
2006 type2 30
2007 type2 20SQL> select * from table2; TYPESEQ TYPENAME
--------------------------------------- --------------------
1 type1
2 type2
3 type3SQL>
SQL> select ta.year,ta.typename,tb.cnt from (
2 select distinct a.year,b.typename from table1 a
3 cross join (select typename from table2) b
4 ) ta left join table1 tb on ta.year=tb.year and ta.typename=tb.type
5 order by year; YEAR TYPENAME CNT
--------------------------------------- -------------------- ---------------------------------------
2005 type1 10
2005 type2
2005 type3 20
2006 type1
2006 type2 30
2006 type3 15
2007 type1
2007 type2 20
2007 type3 9 rows selected
select aa.year,typename,count from
(select DISTINCT year,typename from a,b order by year) AA
left outer join a on typename=a.type and a.year=aa.year
order by aa.year
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
(Select Count
From Table1
Where Yaer = a.Yaer
And Type = a.Typename)
From (Select Distinct t.Yaer, T1.Typename From Table1 t, Table2 T1) a
SQL> SELECT YEAR,
2 TYPENAME,
3 MAX(DECODE(TYPE,TYPENAME,COUNT,NULL)) "COUNTS"
4 FROM TABLE1 T1,
5 TABLE2 T2
6 GROUP BY YEAR,TYPENAME;YEAR TYPENAME COUNTS
---- -------- ----------
2005 TYPE1 10
2005 TYPE2
2005 TYPE3 20
2006 TYPE1
2006 TYPE2 30
2006 TYPE3 15
2007 TYPE1
2007 TYPE2 20
2007 TYPE3 9 rows selectedSQL>
这方法看上去确实简便,楼上能否讲解一下 MAX(DECODE(TYPE,TYPENAME,COUNT,NULL)) 的意思,小人才疏学浅没有用过
year type count
----------------------------
2005 type1 10
2005 type2
2005 type3 20
sum 30
2006 type1
2006 type2 30
2006 type3 15
sum 45
2007 type1
2007 type2 20
2007 type3
sum 20
SQL> SELECT YEAR,
2 TYPENAME,
3 MAX(DECODE(TYPE,TYPENAME,COUNT,NULL)) "COUNTS"
4 FROM TABLE1 T1,
14 TABLE2 T2
20 GROUP BY YEAR,TYPENAME
21 UNION ALL
22 SELECT YEAR,
23 NULL,
24 SUM(COUNT)
25 FROM TABLE1 T1
35 GROUP BY YEAR
36 ORDER BY 1,2;YEAR TYPENAME COUNTS
---- -------- ----------
2005 TYPE1 10
2005 TYPE2
2005 TYPE3 20
2005 30
2006 TYPE1
2006 TYPE2 30
2006 TYPE3 15
2006 45
2007 TYPE1
2007 TYPE2 20
2007 TYPE3
2007 2012 rows selectedSQL>