麻烦大神帮我写几个统计的sql语句,本人sql统计方面还是比较次的,百度了好多,实在是写不出来了,求大神帮我写一下,谢谢
解决方案 »
- 救命呀!谁知道如何在Mac下安装Oracle?
- ERwin工具的relationship种类的问题
- myeclipse配置oracle链接问题
- 关于oracle版本
- 如何将一个方案下的表转移到另一方案下?
- 互联网连接数据库问题
- 我用utl_smtp发送邮件时,中文的就变成乱码了?急需高手指点!(1000分)
- 高高手请进:如何在触发器中断开当前用户连接
- 高手请进,怎么样建立oracle并行服务器?
- oracle explain 分析 SQL 时, buffer sort 耗时将近 16K 大概是怎么回事呢?
- oracle 多表关联查询时 添加查询条件,查询速度变得极慢
- 怎么都没有 软件销售 职位?
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'; 没有环境不能验证,也不知道是否符合你的需求,参考一下吧 最后一个写的有点麻烦,看其他大神有没有更好的
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 八折)这两个查询出来的总量,谢谢