我有张表 有两个列属性 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请问怎么实现 可以跨数据库的 谢谢
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请问怎么实现 可以跨数据库的 谢谢
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
怎么叫可以跨数据库的
sum(decode(forecastType,'蠕虫预警数',1,0))蠕虫预警数,
sum(decode(forecastType,'事件预警数',1,0))事件预警数,
count(forecast) 漏洞预警数 from tablename group by time
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
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
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>
sum(decode(forecastType,2,1,0)) "蠕虫预警数",
sum(decode(forecastType,3,1,0)) "事件预警数",
count(forecastType) "预警总次数"
from t group by time order by time;
很简单。。
oracle中用decode效率更高。。