向大家请教一个SQL

Question

我有张统计表他有如下字段
eventlevel(事件级别)       fk_assetid(资产对象)    countnum(发生次数)
1                          1                        15
2                          1                        20
3                          2                        35
4                          2                        40
5                          3                        50
4                          3                        60
5                          4                        80
资产对象表
pk_assetid    name
1             asset1
2             asset2
3             asset3
4             asset4做下说明 当eventlevel等与5是为高风险   为4,3时中风险    为1,2 时低风险现在我要得出如下的结果
资产对象             被攻击总次数          高风险攻击次数   中风险攻击次数     低风险攻击次数
asset1             35                  0               0                  35
asset2             75                  0               75                 0
asset3             110                 50               60                0
asset4             60                  80                0               0
谢谢

Answer 1

select a.name,sum(b.countnum),case b.eventlevel when 5 then b.countnum else 0 end,case b.eventlevel when 4 then b.countnum when 3 then b.countnum else 0 end,b.eventlevel when 2 then b.countnum when 1 then b.countnum else 0 end
from 资产对象表 a,统计表 b where a.pk_assetid=b.fk_assetid group by a.pk_assetid;

Answer 2

select name,
sum(countnum)被攻击总次数
sum(decode(fk_assetid,5,countnum,0)) as 高风险攻击次数,
sum(decode(fk_assetid,4,countnum,3,countnum,0)) as 中风险攻击次数,
sum(decode(fk_assetid,2,countnum,1,countnum,0)) as 低风险攻击次数
from taba,tabb
where taba.eventlevel=tabb.pk_assetid
group by taba.name

Answer 3

select t2.name,
       sum(t1.countnum)被攻击总次数,
       sum(case when t1.eventlevel=5 then 1 else 0 end)高风险攻击次数,
       sum(case when t1.eventlevel in(4,3) then 1 else 0 end)中风险攻击次数,
       sum(case when t1.eventlevel in(1，2) then 1 else 0 end)低风险攻击次数
  from 统计表 t1,
       资产对象表 t2
 where t1.fk_assetid=t2.fk_assetid
group by t2.name 

Answer 4

select a.name,sum(b.countnum),case b.eventlevel when 5 then b.countnum else 0 end,case b.eventlevel when 4 then b.countnum when 3 then b.countnum else 0 end,b.eventlevel when 2 then b.countnum when 1 then b.countnum else 0 end
from 资产对象表 a,统计表 b where a.pk_assetid=b.fk_assetid group by a.name;

Answer 5

select name, 
sum(countnum)被攻击总次数 
sum(decode(eventlevel,5,countnum,0)) as 高风险攻击次数, 
sum(decode(eventlevel,4,countnum,3,countnum,0)) as 中风险攻击次数, 
sum(decode(eventlevel,2,countnum,1,countnum,0)) as 低风险攻击次数 
from taba,tabb 
where taba.fk_assetid=tabb.pk_assetid 
group by taba.name

Answer 6

select  name ,sum(countnum)被攻击总次数 ,
sum(case eventlevel when 4 then  countnum else 0 end )高风险攻击次数,
sum(case eventlevel when 3 then  countnum else 0 end )中风险攻击次数,
sum(case when eventlevel=1 or eventlevel=2 then countnum else 0 end )低风险攻击次数
from 统计表,资产对象表 where  统计表. fk_assetid=资产对象表.pk_assetid  
group by name 

Answer 7

[code=SQL]
上面的表为event 下面的表为assetselect b.name as 资产对象,
       sum(countnum) as 被攻击总次数 ,
       ab.a as 高风险攻击次数,
       ab.b+ab.c as 中风险攻击次数,
       ab.d+ab.e as 低风险攻击次数,
from (select fk_assetid,
  case eventlevel
             when 5 then sum(countnum)  
             else  0
        end as a,
     case eventlevel
             when 4 then sum(countnum)  
             else  0
        end as b,
       case eventlevel
             when 3 then sum(countnum)
             else  0 
       end as c,
      case eventlevel
             when 2 then sum(countnum)  
             else  0
        end as d,
        case eventlevel
             when 1 then sum(countnum)
             else  0 
       end as e,
      from event
      group by fk_assetid;
) as ab,
asset 
where ab.fk_assetid=asset.fk_assetid;
             
            

Answer 8

[code=SQL]
上面的表为event 下面的表为assetselect b.name as 资产对象,
       sum(countnum) as 被攻击总次数 ,
       ab.a as 高风险攻击次数,
       ab.b+ab.c as 中风险攻击次数,
       ab.d+ab.e as 低风险攻击次数,
from (select fk_assetid,
  case eventlevel
             when 5 then sum(countnum)  
             else  0
        end as a,
     case eventlevel
             when 4 then sum(countnum)  
             else  0
        end as b,
       case eventlevel
             when 3 then sum(countnum)
             else  0 
       end as c,
      case eventlevel
             when 2 then sum(countnum)  
             else  0
        end as d,
        case eventlevel
             when 1 then sum(countnum)
             else  0 
       end as e,
      from event
      group by fk_assetid;
) as ab,
asset 
where ab.fk_assetid=asset.fk_assetid;