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    

解决方案 »

  1.   


    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
      

  2.   


    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
      

  3.   

    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
      

  4.   

    Select a.*,
     (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
      

  5.   

    简单一点的方法:
    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> 
      

  6.   


    这方法看上去确实简便,楼上能否讲解一下 MAX(DECODE(TYPE,TYPENAME,COUNT,NULL)) 的意思,小人才疏学浅没有用过
      

  7.   

    DECODE相当于CASE WHEN, 如果TYPE值等于TYPENAME,那么返回出来的值就是所对应的COUNT列的值,否则的话返回NULL值.
      

  8.   

    谢谢各位解答!尤其是mantisXF的方法,真是让我仰望的高度!!
      

  9.   

    结贴结早了!我的结果中如果还要得到每年的count总计值,该怎么写呢?即如下结果:
    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
      

  10.   

    用group by rollup(year) 来分组统计
      

  11.   

    TRY IT ..
    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>