while (@level1>=1)
begin
update #result1
set prime_cost = mid.prime_cost,apport_direct_cost = mid.apport_direct_cost,public_cost = mid.public_cost,
manage_cost = mid.manage_cost,apport_cost_s = mid.apport_cost_s,apport_cost_t = mid.apport_cost_t,
apport_cost_f = mid.apport_cost_f,fixed_cost = mid.fixed_cost,dy_cost = mid.dy_cost,control_cost = mid.control_cost,
uncontrol_cost = mid.uncontrol_cost,manpower_cost = mid.manpower_cost ,retire_cost = mid.retire_cost ,
material_cost = mid.material_cost,pure_M_cost = mid.pure_M_cost ,depreciation_cost = mid.depreciation_cost,
other_cost = mid.other_cost,
O_T_income = mid.O_T_income,I_T_income = mid.I_T_income,
I_M_income = mid.I_M_income,O_M_income = mid.O_M_income,
O_T_cost = mid.O_T_cost,I_T_cost = mid.I_T_cost,
O_M_cost = mid.O_M_cost,I_M_cost = mid.I_M_cost,
fund_subsidy_income = mid.fund_subsidy_income,self_exec_income = mid.self_exec_income,
total_bed_used_days=mid.total_bed_used_days,emp_num=mid.emp_num,outp_num=mid.outp_num,imma_cost=mid.imma_cost,risk_cost=mid.risk_cost
from (
select supper_dept, sum(prime_cost) prime_cost,sum(apport_direct_cost) apport_direct_cost,sum(public_cost) public_cost,
sum(manage_cost) manage_cost,sum(apport_cost_s) apport_cost_s,sum(apport_cost_t) apport_cost_t,
sum(apport_cost_f) apport_cost_f,sum(fixed_cost) fixed_cost,sum(dy_cost) dy_cost,sum(control_cost) control_cost,
sum(uncontrol_cost) uncontrol_cost,sum(manpower_cost) manpower_cost ,sum(retire_cost) retire_cost ,
sum(material_cost) material_cost,sum(pure_M_cost) pure_M_cost ,sum(depreciation_cost) depreciation_cost,
sum(other_cost) other_cost,
sum(O_T_income) O_T_income,sum(I_T_income) I_T_income,
sum(I_M_income) I_M_income,sum(O_M_income) O_M_income,
sum(O_T_cost) O_T_cost,sum(I_T_cost) I_T_cost,
sum(O_M_cost) O_M_cost,sum(I_M_cost) I_M_cost,
sum(fund_subsidy_income) fund_subsidy_income,sum(self_exec_income) self_exec_income,
sum(total_bed_used_days) total_bed_used_days,sum(emp_num) emp_num,sum(outp_num) outp_num
,sum(imma_cost) imma_cost,sum(risk_cost) risk_cost
from #result1
where level=@level1
group by supper_dept
)mid --按照级别进行汇总
where #result1.dept_code=mid.supper_dept
set @level1=@level1-1
end
begin
update #result1
set prime_cost = mid.prime_cost,apport_direct_cost = mid.apport_direct_cost,public_cost = mid.public_cost,
manage_cost = mid.manage_cost,apport_cost_s = mid.apport_cost_s,apport_cost_t = mid.apport_cost_t,
apport_cost_f = mid.apport_cost_f,fixed_cost = mid.fixed_cost,dy_cost = mid.dy_cost,control_cost = mid.control_cost,
uncontrol_cost = mid.uncontrol_cost,manpower_cost = mid.manpower_cost ,retire_cost = mid.retire_cost ,
material_cost = mid.material_cost,pure_M_cost = mid.pure_M_cost ,depreciation_cost = mid.depreciation_cost,
other_cost = mid.other_cost,
O_T_income = mid.O_T_income,I_T_income = mid.I_T_income,
I_M_income = mid.I_M_income,O_M_income = mid.O_M_income,
O_T_cost = mid.O_T_cost,I_T_cost = mid.I_T_cost,
O_M_cost = mid.O_M_cost,I_M_cost = mid.I_M_cost,
fund_subsidy_income = mid.fund_subsidy_income,self_exec_income = mid.self_exec_income,
total_bed_used_days=mid.total_bed_used_days,emp_num=mid.emp_num,outp_num=mid.outp_num,imma_cost=mid.imma_cost,risk_cost=mid.risk_cost
from (
select supper_dept, sum(prime_cost) prime_cost,sum(apport_direct_cost) apport_direct_cost,sum(public_cost) public_cost,
sum(manage_cost) manage_cost,sum(apport_cost_s) apport_cost_s,sum(apport_cost_t) apport_cost_t,
sum(apport_cost_f) apport_cost_f,sum(fixed_cost) fixed_cost,sum(dy_cost) dy_cost,sum(control_cost) control_cost,
sum(uncontrol_cost) uncontrol_cost,sum(manpower_cost) manpower_cost ,sum(retire_cost) retire_cost ,
sum(material_cost) material_cost,sum(pure_M_cost) pure_M_cost ,sum(depreciation_cost) depreciation_cost,
sum(other_cost) other_cost,
sum(O_T_income) O_T_income,sum(I_T_income) I_T_income,
sum(I_M_income) I_M_income,sum(O_M_income) O_M_income,
sum(O_T_cost) O_T_cost,sum(I_T_cost) I_T_cost,
sum(O_M_cost) O_M_cost,sum(I_M_cost) I_M_cost,
sum(fund_subsidy_income) fund_subsidy_income,sum(self_exec_income) self_exec_income,
sum(total_bed_used_days) total_bed_used_days,sum(emp_num) emp_num,sum(outp_num) outp_num
,sum(imma_cost) imma_cost,sum(risk_cost) risk_cost
from #result1
where level=@level1
group by supper_dept
)mid --按照级别进行汇总
where #result1.dept_code=mid.supper_dept
set @level1=@level1-1
end
select
(
select a.id,a.name,b.id id_b,b.name name_b
from a.a1=b.b1
)
set name=name_b
这样写的条件是b.b1有唯一索引。也就是说a的每一个在这个查询结果里不能扩充为多行。delete 也可以用这种写法
select
(
select a.id,a.name,b.id id_b,b.name name_b
from a,b
where a.id=b.id
)
set name=name_b