我有张表 有两个列属性 time(时间),forecastType(预警类型 说名1代表漏洞预警数 2--代表蠕虫预警数 3--事件预警数) 我现在要得出下面的查询结果 请问怎么实现时间 漏洞预警数 蠕虫预警数 事件预警数 预警总次数
2008-5-1 1 1 1 3
2008-5-2 2 2 2 6
2008-5-3 3 3 3 9
2008-5-4 1 1 2 4
2008-5-5 2 2 3 7请问怎么实现  可以跨数据库的 谢谢

解决方案 »

  1.   

    实现不难
    select  time,
          sum(case forecastType when 1 then 1 esle 0 end )漏洞预警数,
    sum(case forecastType when 2 then 1 esle 0 end )蠕虫预警数,
    ....
    from 
    group by time
    怎么叫可以跨数据库的
      

  2.   

    select time,sum(decode(forecastType,'漏洞预警数',1,0))漏洞预警数,
                 sum(decode(forecastType,'蠕虫预警数',1,0))蠕虫预警数,
                 sum(decode(forecastType,'事件预警数',1,0))事件预警数,
                count(forecast) 漏洞预警数 from tablename group by time
      

  3.   


    SQL> select * from t;TIME                                            FORECASTTYPE
    -------------------- ---------------------------------------
    2008-5-1                                                   1
    2008-5-1                                                   2
    2008-5-1                                                   3
    2008-5-1                                                   2
    2008-5-2                                                   1
    2008-5-3                                                   1
    2008-5-4                                                   1
    2008-5-3                                                   3
    2008-5-2                                                   29 rows selectedSQL> 
    SQL> select time,sum(decode(forecastType,1,1,0)) "漏洞预警数",
      2              sum(decode(forecastType,2,1,0)) "蠕虫预警数",
      3              sum(decode(forecastType,3,1,0)) "事件预警数",
      4              count(forecastType) "预警总次数"
      5  from t group by time order by time;TIME                 漏洞预警数 蠕虫预警数 事件预警数 预警总次数
    -------------------- ---------- ---------- ---------- ----------
    2008-5-1                      1          2          1          4
    2008-5-2                      1          1          0          2
    2008-5-3                      1          0          1          2
    2008-5-4                      1          0          0          1
      

  4.   

    select time,
           sum(case forecastType
                 when 1 then
                  1 esle 0
               end) 漏洞预警数,
           sum(case forecastType
                 when 2 then
                  1 esle 0
               end) 蠕虫预警数,
           sum(case forecastType
                 when 3 then
                  1 esle 0
               end) 事件预警数,
           count(forecastType) 预警总次数 
      from tablename
     group by time
      

  5.   


    CASE WHEN 的方式是ORACLE和SQL SERVER都可以用的,DECODE是ORACLE里特有的。SQL> SELECT * FROM TEST_TIME;TIME     FORECASTTYPE
    -------- ------------
    2008-5-1            1
    2008-5-1            2
    2008-5-1            3
    2008-5-1            2
    2008-5-2            1
    2008-5-3            1
    2008-5-4            1
    2008-5-3            3
    2008-5-2            29 rows selectedSQL> SELECT TIME,
      2         SUM(CASE WHEN FORECASTTYPE = 1
      3          THEN 1
      4          ELSE 0
      5           END) "漏洞预警数",
      6         SUM(CASE WHEN FORECASTTYPE = 2
      7          THEN 1
      8          ELSE 0
      9           END) "蠕虫预警数",
     10         SUM(CASE WHEN FORECASTTYPE = 3
     11          THEN 1
     12          ELSE 0
     13           END) "事件预警数",
     14         COUNT(1) "预警总次数"
     15    FROM TEST_TIME
     16   GROUP BY TIME;TIME     漏洞预警数 蠕虫预警数 事件预警数 预警总次数
    -------- ---------- ---------- ---------- ----------
    2008-5-1          1          2          1          4
    2008-5-2          1          1          0          2
    2008-5-3          1          0          1          2
    2008-5-4          1          0          0          1SQL> 
    SQL> SELECT TIME,
      2         SUM(DECODE(FORECASTTYPE,1,1,0)) "漏洞预警数",
      3         SUM(DECODE(FORECASTTYPE,2,1,0)) "蠕虫预警数",
      4         SUM(DECODE(FORECASTTYPE,3,1,0)) "事件预警数",
      5         COUNT(1) "预警总次数"
      6    FROM TEST_TIME
      7   GROUP BY TIME;TIME     漏洞预警数 蠕虫预警数 事件预警数 预警总次数
    -------- ---------- ---------- ---------- ----------
    2008-5-1          1          2          1          4
    2008-5-2          1          1          0          2
    2008-5-3          1          0          1          2
    2008-5-4          1          0          0          1SQL> 
      

  6.   

    select time,sum(decode(forecastType,1,1,0)) "漏洞预警数",
                sum(decode(forecastType,2,1,0)) "蠕虫预警数",
                sum(decode(forecastType,3,1,0)) "事件预警数",
                count(forecastType) "预警总次数"
    from t group by time order by time;
    很简单。。
    oracle中用decode效率更高。。