select bc.outid,bc.name,sum(t.opfare) from rec_cust_acc t,base_customers bc where
t.acccode = 101 and t.customerid = bc.customerid and t.opdt between
to_date(concat('2010-08-01',' 00:00:00'),'yyyy-mm-dd hh24:mi:ss') and
to_date(concat('2010-12-06',' 23:59:59'),'yyyy-mm-dd hh24:mi:ss')
group by bc.outid,bc.name order by bc.outid
我的需求是这样的:
这个sql查出来的是员工充值金额列表,我想在此基础上新添一项,用来显示补助金额
补助金额标准是这样的:
对于sql查出来的sum(t.opfare)值,如果>=50,补助金额为100;如果<50,补助金额为sum(t.opfare)*2
等待高手指点啊!
t.acccode = 101 and t.customerid = bc.customerid and t.opdt between
to_date(concat('2010-08-01',' 00:00:00'),'yyyy-mm-dd hh24:mi:ss') and
to_date(concat('2010-12-06',' 23:59:59'),'yyyy-mm-dd hh24:mi:ss')
group by bc.outid,bc.name order by bc.outid
我的需求是这样的:
这个sql查出来的是员工充值金额列表,我想在此基础上新添一项,用来显示补助金额
补助金额标准是这样的:
对于sql查出来的sum(t.opfare)值,如果>=50,补助金额为100;如果<50,补助金额为sum(t.opfare)*2
等待高手指点啊!
解决方案 »
- 几张有重复记录的表关联查询取最新的记录,如何优化?
- 关于oracle备份与恢复问题
- 【求助】请教下,什么是RAC节点?
- 在oracle里存大量文本信息,应该用什么类型??
- 在线等待
- 使用三层嵌套语句对数据进行分页,在9和8下是支持排序的,到了oracle7下就不支持了?
- to:: luckysxn(风花雪) and zhangshunshi(宇轩)
- 我的才安装的oracle出的问题,请帮我指点一下??
- 这个SQL语句查询起来为什么这么慢?
- 在配置oracle客户端是,有几个什么名的到底指什么东西(像主机名,数据库名)~
- 360°冰天雪地裸体跪求,数据转储最佳可行性解决方案
- sqlldr怎么导入数据到表的个别列?疑惑一天了。
select bc.outid,bc.name,sum(t.opfare) , case when sum(t.opfare) >= 50 then 100 else sum(t.opfare) * 2 end "补助金额"
from rec_cust_acc t,base_customers bc where
t.acccode = 101 and t.customerid = bc.customerid and t.opdt between
to_date(concat('2010-08-01',' 00:00:00'),'yyyy-mm-dd hh24:mi:ss') and
to_date(concat('2010-12-06',' 23:59:59'),'yyyy-mm-dd hh24:mi:ss')
group by bc.outid,bc.name order by bc.outid
select bc.outid,
bc.name,
sum(t.opfare) as col,
case when sum(t.opfare)>=50 then 100 else sum(t.opfare)*2 end as col1
from rec_cust_acc t,base_customers bc where
t.acccode = 101 and t.customerid = bc.customerid and t.opdt between
to_date(concat('2010-08-01',' 00:00:00'),'yyyy-mm-dd hh24:mi:ss') and
to_date(concat('2010-12-06',' 23:59:59'),'yyyy-mm-dd hh24:mi:ss')
group by bc.outid,bc.name order by bc.outid
select bc.outid,bc.name,sum(t.opfare) , (case when sum(t.opfare) >= 50 then 100 else sum(t.opfare) * 2 end) buzhu
from rec_cust_acc t,base_customers bc where
t.acccode = 101 and t.customerid = bc.customerid and t.opdt between
to_date(concat('2010-08-01',' 00:00:00'),'yyyy-mm-dd hh24:mi:ss') and
to_date(concat('2010-12-06',' 23:59:59'),'yyyy-mm-dd hh24:mi:ss')
group by bc.outid,bc.name order by bc.outid
select bc.outid,bc.name,sum(t.opfare),
decode(sign(sum(t.opfare)-50),1,100,-1,sum(t.opfare)*2)
from rec_cust_acc t,base_customers bc where
t.acccode = 101 and t.customerid = bc.customerid and t.opdt between
to_date(concat('2010-08-01',' 00:00:00'),'yyyy-mm-dd hh24:mi:ss') and
to_date(concat('2010-12-06',' 23:59:59'),'yyyy-mm-dd hh24:mi:ss')
group by bc.outid,bc.name order by bc.outid
bc.name,
sum(t.opfare),
case
when sum(t.opfare) >= 50 then
100
else
sum(t.opfare) * 2
end "补助金额"
from rec_cust_acc t, base_customers bc
where t.acccode = 101
and t.customerid = bc.customerid
and t.opdt between
to_date(concat('2010-08-01', ' 00:00:00'), 'yyyy-mm-dd hh24:mi:ss') and
to_date(concat('2010-12-06', ' 23:59:59'), 'yyyy-mm-dd hh24:mi:ss')
group by bc.outid, bc.name
order by bc.outid;
sum(t.opfare)的合计是sum(sum(t.opfare))
补助金额的合计怎么写啊?
帮帮忙吧,大侠!
--嵌套一层,用下分析函数
select cnt.outid,
cnt.name,
sum(cnt.sumopfare) over(order by 1) totalopfare,
sum(cnt.otherfee) over(order by 1) totalotherfee
from (select bc.outid,
bc.name,
sum(t.opfare) sumopfare,
case
when sum(t.opfare) >= 50 then
100
else
sum(t.opfare) * 2
end otherfee
from rec_cust_acc t, base_customers bc
where t.acccode = 101
and t.customerid = bc.customerid
and t.opdt between
to_date(concat('2010-08-01', ' 00:00:00'),
'yyyy-mm-dd hh24:mi:ss') and
to_date(concat('2010-12-06', ' 23:59:59'),
'yyyy-mm-dd hh24:mi:ss')
group by bc.outid, bc.name
order by bc.outid) cnt
select count(distinct(bc.outid)),sum(sum(t.opfare)) from rec_cust_acc t,base_customers bc where
t.acccode = 101 and t.customerid = bc.customerid and t.opdt between
to_date(concat('2010-08-01',' 00:00:00'),'yyyy-mm-dd hh24:mi:ss') and
to_date(concat('2010-12-06',' 23:59:59'),'yyyy-mm-dd hh24:mi:ss')
group by bc.outid,bc.name,t.opfare order by bc.outid;
这个sql能实现人数、充值金额总数的合计,补助金额的合计在此基础上怎么获得啊?
帮忙啊!