一统计sql语句,该怎么解决

一统计sql语句

SQL code

--用户表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
姓名，部门 ,职位 ,总金额累计，到账金额累计，待发金额累计,本月总金额，本月到账金额，本月待发金额

[解决办法]
nvl(sum(fee),0)就ok了
[解决办法]
试试下面的sql，测试过：

select 
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
[解决办法]

SQL code

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
[解决办法]
貌似我写的简单一点

select u_name as 姓名，department_name as 部门 ,position_name as 职位 ,
      '请指明计算公式' 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 

 

查看更多 下一篇