Oracle数据统计,麻烦大神帮我写几个sql语句

[liyaxiong517]

麻烦大神帮我写几个统计的sql语句,本人sql统计方面还是比较次的,百度了好多,实在是写不出来了,求大神帮我写一下,谢谢

[liyaxiong517]

select 
 liuzhe.liuzhe_cnt as 六折, bazhe.bazhe_cnt as 八折
  from (select count(*) as liuzhe_cnt
          from TF_B_FLOW_RESOURCE_ORDER t
         where t.resource_code = '11120005102410006000'
           and t.start_time >=
               to_date('2015-06-18 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
           and t.order_type = '00') liuzhe,
       (select sum(decode(t.discount, '080', 1, 0)) as bazhe_cnt
          from TF_B_FLOW_RESOURCE_ORDER t
         where t.start_time >=
               to_date('2015-06-18 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
           and t.order_type = '00') bazhe

这个是我写的第一个的sql,现在还需要一个总量,麻烦大神在这个基础上帮我加一下怎么计算(liuzhe.liuzhe_cnt as 六折, bazhe.bazhe_cnt as 八折)这两个查询出来的总量，谢谢

[IceIsabel]

把表结构和样例数据贴一下

[chengccy]

数据类型和表结构也要说明一下吧,难道要自己设计?

[liyaxiong517]

这个是表结构,刚才忘了传了,麻烦大神帮我写一下,谢谢了,流量半年包的区分是resource_code = '11120005102410006000',（6折是discount = '060',8折是discount = '080'）这两个折扣必须要满足order_type = '00'的时候折扣才能查到,谢谢了

[IceIsabel]

select count(*),sum(case when t.discount='060' then 1 else 0 end),sum(case when t.discount='080' then 1 else 0 end)
from t
where t.resource_code='11120005102410006000'
and t.start_time>=to_date('2015-05-18','yyyy-mm-dd')
and t.order_type='00';

select count(*),sum(case when t.discount='060' then 1 else 0 end),sum(case when t.discount='080' then 1 else 0 end)
from t
where t.resource_code='11120005102410006000'
and t.start_time between sysdate-1/24 and sysdate
and t.order_type='00';

select count(*),sum(case when t.discount='060' then 1 else 0 end),sum(case when t.discount='080' then 1 else 0 end)
from t
where t.resource_code='11120005102410006000'
and t.order_type='00';

select sum(case when t.start_time between trunc(sysdate) and sysdate then 1 else 0 end),
sum(case when t.start_time between trunc(sysdate-1) and trunc(sysdate)-1/(24*60*60) then 1 else 0 end),
sum(case when t.discount='060' and t.start_time between trunc(sysdate) and sysdate then 1 else 0 end),
sum(case when t.discount='080' and t.start_time between trunc(sysdate) and sysdate then 1 else 0 end),
sum(case when t.discount='060' and t.start_time between trunc(sysdate-1) and trunc(sysdate)-1/(24*60*60) then 1 else 0 end),
sum(case when t.discount='080' and t.start_time between trunc(sysdate-1) and trunc(sysdate)-1/(24*60*60) then 1 else 0 end),
(sum(case when t.start_time between trunc(sysdate) and sysdate then 1 else 0 end)-
sum(case when t.start_time between trunc(sysdate-1) and trunc(sysdate)-1/(24*60*60) then 1 else 0 end))
/sum(case when t.start_time between trunc(sysdate-1) and trunc(sysdate)-1/(24*60*60) then 1 else 0 end),
(sum(case when t.discount='060' and t.start_time between trunc(sysdate) and sysdate then 1 else 0 end)
-sum(case when t.discount='060' and t.start_time between trunc(sysdate-1) and trunc(sysdate)-1/(24*60*60) then 1 else 0 end))
/sum(case when t.discount='060' and t.start_time between trunc(sysdate-1) and trunc(sysdate)-1/(24*60*60) then 1 else 0 end),
(sum(case when t.discount='080' and t.start_time between trunc(sysdate) and sysdate then 1 else 0 end)
-sum(case when t.discount='080' and t.start_time between trunc(sysdate-1) and trunc(sysdate)-1/(24*60*60) then 1 else 0 end))
/sum(case when t.discount='080' and t.start_time between trunc(sysdate-1) and trunc(sysdate)-1/(24*60*60) then 1 else 0 end)
from t
where t.resource_code='11120005102410006000'
and t.order_type='00';

没有环境不能验证，也不知道是否符合你的需求，参考一下吧 最后一个写的有点麻烦，看其他大神有没有更好的

[liyaxiong517]

方便留个扣扣么,还是有一点疑问,想详细的问一下,谢谢了

[碧水幽幽泉]

最好创建语句和字段信息都贴出来，这样大家好帮你分析问题。

[碧水幽幽泉]

语句呢。