--用户表
create table user_info
(
u_id int primary key,--用户id
u_name varchar2(20),--姓名
Department_id int ,--部门id
Position_id int ,--职位id
)
--到账记录表
create table user_yes_info
(
u_id int ,
fee float,--金额
theMonth varchar(10)--月份
)
--待发记录表
create table user_waiting_info
(
u_id int ,
fee float,--金额
theMonth varchar(10)--月份)
--部门表
create table Department_info
(
Department_id int primary key ,
Department_name varchar2(50)
)
--职位表
create table Position_info
(
Position_id int primary key ,
Position_name varchar2(50)
)最后要查出来的字段是:
(查出所有用户的,没有的为0
姓名,部门 ,职位 ,总金额累计,到账金额累计,待发金额累计,本月总金额,本月到账金额,本月待发金额
解决方案 »
- 请问一个sql语句
- 64位win7,安装64位oracle11gr2,想装个plsqldev结果装不上
- 本机如何连接别的机器上的oracle数据库
- WIN2000下的oracle 9.2 server 安装后不能进行EXP,IMP,提示“协议适配器错误”!!!
- 事务处理,如何得知语句执行情况!
- PLSQL DEVOPER 调试 问题 在线等
- oracel 高级复制部件的安装
- 呵呵 请教大家啊
- 创建物化视图使用以下查询语句报ORA-22818:不支持下面方式子查询!!!高手帮忙改造下。
- oracle知识框架
- 重做日志文件和归档日志文件的关系
- 寻求帮助:oracle 10g用imp导入数据的时候报错:ORA-01658: 无法为表空间 MAXDATA 中的段创建 INITIAL 区
select ui.u_name,
(select Department_name from Department_info where Department_id=ui.Department_id) as Department_name,
(select Position_name from Position_info where Position_id=ui.Position_id) as Position_name,
(select sum(fee) from user_yes_info where u_id=ui.u_id)+(select sum(fee) from user_waiting_info where u_id=ui.u_id) as sum_fee,
(select sum(fee) from user_yes_info where u_id=ui.u_id) as yes_fee,
(select sum(fee) from user_waiting_info where u_id=ui.u_id) as waiting_fee,
(select sum(fee) from user_yes_info where u_id=ui.u_id and theMonth='11')+(select sum(fee) from user_waiting_info where u_id=ui.u_id and theMonth='11') as thismonth_sum_fee,
(select sum(fee) from user_yes_info where u_id=ui.u_id and theMonth='11') as thismonth_yes_fee,
(select sum(fee) from user_waiting_info where u_id=ui.u_id and theMonth='11') as thismonth_waiting_fee,
from user_info ui
select u.u_name,
(select d.department_name from Department_info d where d.department_id=u.department_id) department_name,
(select p.position_name from Position_info p where p.position_id=u.position_id) position_id,
(select nvl(sum(nvl(uy.fee,0)),0)+nvl(sum(nvl(uw.fee,0)),0) from user_yes_info uy,user_waiting_info uw where uy.u_id=uw.u_id and uy.u_id=u.u_id) zje,
(select nvl(sum(nvl(uy.fee,0)),0) from user_yes_info uy,user_waiting_info uw where uy.u_id=uw.u_id and uy.u_id=u.u_id) dzje,
(select nvl(sum(nvl(uw.fee,0)),0) from user_yes_info uy,user_waiting_info uw where uy.u_id=uw.u_id and uy.u_id=u.u_id) dfje,
(select nvl(sum(nvl(uy.fee,0)),0)+nvl(sum(nvl(uw.fee,0)),0) from user_yes_info uy,user_waiting_info uw where uy.u_id=uw.u_id and uy.u_id=u.u_id and uy.themonth=uw.themonth and to_char(uy.themonth,'yyyy-mm') like '11') byzje,
(select nvl(sum(nvl(uy.fee,0)),0) from user_yes_info uy,user_waiting_info uw where uy.u_id=uw.u_id and uy.u_id=u.u_id and to_char(uy.themonth,'yyyy-mm') like '11') bydzje,
(select nvl(sum(nvl(uw.fee,0)),0) from user_yes_info uy,user_waiting_info uw where uy.u_id=uw.u_id and uy.u_id=u.u_id and to_char(uw.themonth,'yyyy-mm') like '11') bydfje
from user_info u
已经过测试,无误。
注:nvl(col,0)函数,当col字段值为空时,则取0。oracle里sum函数里,如果有null值,则全为计算结果为空。
select sum(fee) from user_yes_info where u_id=ui.u_id and theMonth='11')+(select sum(fee) from user_waiting_info where u_id=ui.u_id and theMonth='11'这种写法,如果user_yes_info 有而 user_waiting_info 没有的用户,会得到为0吗
select ui.u_name,
(select Department_name from Department_info where Department_id=ui.Department_id) as Department_name,
(select Position_name from Position_info where Position_id=ui.Position_id) as Position_name,
(select sum(nvl(fee,0)) from user_yes_info where u_id=ui.u_id)+(select sum(nvl(fee,0)) from user_waiting_info where u_id=ui.u_id) as sum_fee,
(select sum(nvl(fee,0)) from user_yes_info where u_id=ui.u_id) as yes_fee,
(select sum(nvl(fee,0)) from user_waiting_info where u_id=ui.u_id) as waiting_fee,
(select sum(nvl(fee,0)) from user_yes_info where u_id=ui.u_id and theMonth='11')+(select sum(nvl(fee,0)) from user_waiting_info where u_id=ui.u_id and theMonth='11') as thismonth_sum_fee,
(select sum(nvl(fee,0)) from user_yes_info where u_id=ui.u_id and theMonth='11') as thismonth_yes_fee,
(select sum(nvl(fee,0)) from user_waiting_info where u_id=ui.u_id and theMonth='11') as thismonth_waiting_fee,
from user_info ui
我想问下 ,这样写执行速度怎么样,会不会比较慢呢
而且在一条语句里面,多出出现 (select sum(nvl(fee,0)) from user_yes_info where u_id=ui.u_id)
是不是这句话在出现的每一处,都要重新计算一次呢
Department_info d,Position_info p where t2.uid=d.uid and t2.uid=p.uid
左连接 + nvl做的
t.u_name,--姓名
t1.department_name,--部门
t2.position_name,--职位
t3.fee_yes, --到账金额累计
t4.fee_waiting,--待发金额累计
t5.fee_yes_month,--本月到账金额
t4.fee_waiting,--本月待发金额
t4.fee_waiting+t5.fee_yes_month as fee_month --本月总金额
from user_info t
left join Department_info t1 on t.department_id=t1.department_id
left join Position_info t2 on t.position_id=t2.position_id
left join (
select t.u_id,sum(nvl(t1.fee,0)) fee_yes from user_info t
left join user_yes_info t1 on t.u_id=t1.u_id
group by t.u_id
) t3 on t.u_id=t3.u_id
left join (
select t.u_id,sum(nvl(t1.fee,0)) fee_waiting from user_info t
left join user_waiting_info t1 on t.u_id=t1.u_id
group by t.u_id
) t4 on t.u_id=t4.u_id
left join (
select t.u_id,sum(nvl(t1.fee,0)) fee_yes_month from user_info t
left join user_yes_info t1 on t.u_id=t1.u_id
where t1.themonth='11' --'11'为本月
group by t.u_id
) t5 on t.u_id=t5.u_id
left join (
select t.u_id,sum(nvl(t1.fee,0)) fee_waiting_month from user_info t
left join user_waiting_info t1 on t.u_id=t1.u_id
where t1.themonth='11' --'11'为本月
group by t.u_id
) t6 on t.u_id=t6.u_id
where 1=1
order by t.u_name,t1.department_name,t2.position_name
select ui.u_name,
(select Department_name from Department_info where Department_id=ui.Department_id) as Department_name,
(select Position_name from Position_info where Position_id=ui.Position_id) as Position_name,
sum(nvl(uyi.fee,0))+sum(nvl(uw.fee,0)) as sum_fee,
sum(nvl(uyi.fee,0) as yes_fee,
sum(nvl(uw.fee,0)) as waiting_fee,
sum(nvl(muyi.fee,0))+sum(nvl(muw.fee,0)) as thismonth_sum_fee,
sum(nvl(muyi.fee,0)) as thismonth_yes_fee,
sum(nvl(muw.fee,0)) as thismonth_waiting_fee,
from user_info ui left join user_yes_info uyi on uyi.u_id=ui.u_id
left join user_waiting_info uw on uw.u_id=ui.u_id
left join (select fee,u_id from user_yes_info where theMonth='11') muyi on muyi.u_id=ui.u_id
left join (select fee,u_id from user_waiting_info where theMonth='11') muw on muw.u_id=ui.u_id
'请指明计算公式' as 总金额累计,incomeTotal as 到账金额累计,payTotal as 待发金额累计,
'请指明计算公式1' as 本月总金额,incomeTotalThis as 本月到账金额,payTotalThis as 本月待发金额
from
(
select u.u_id,u.u_name, d.department_name, p.position_name
from user_info u, Department_info d, Position_info p
where u.Department_id = d.Department_id
and u.Position_id = p.Position_id
) employee left join
(
select u_id,
sum(fee) as incomeTotal,
sum(decode(theMonth,to_char(sysdate, 'mm'), fee, 0)) as incomeTotalThis,
sum(decode(theMonth,to_char(add_months(sysdate,-1), 'mm'), fee, 0)) as incomeTotalLast
from user_yes_info
group by u_id
) income on employee.u_id = income.u_id left join
(
select u_id,
sum(fee) as payTotal,
sum(decode(theMonth,to_char(sysdate, 'mm'), fee, 0)) as payTotalThis,
sum(decode(theMonth,to_char(add_months(sysdate,-1), 'mm'), fee, 0)) as payTotalLast
from user_waiting_info
group by u_id
) pay on employee.u_id = pay.u_id
group by u_name, department_name, position_name