/*
涉及2个表
表1
create table t_department--部门表
(
dept_id varchar(50),--部门编号
dept_name varchar(50)--部门名称
)
insert into t_department select'011','部门1'insert into t_department select'012','部门2'insert into t_department select'01','分部门'--部门1和部门2是“分部门”的下属部门,用011,012,013....代替
-------------------------------------------------------------------
表2是由以下SQL产生的表select * into #mytable
from
(
select
t1.dr as '(已上)合作厅及代理商应收',
t2.dr as '增加信誉值金额合计',
t1.rq as date_,
t1.ssbm as department
from t_rb_total t1
join t_rb_total t2 on t1.rq=t2.rq and t1.ssbm=t2.ssbm and t1.xm='(已上)合作厅及代理商应收'and t2.xm='增加信誉值金额合计'
) aselect
isnull((select sum(t2.[增加信誉值金额合计]-t2.[(已上)合作厅及代理商应收] )
from #mytable t2
where t1.department=t2.department
and t1.date_>t2.date_),0) 往日营业厅余额合计 ,
isnull((select sum(t2.[增加信誉值金额合计]-t2.[(已上)合作厅及代理商应收] )
from #mytable t2
where t1.department=t2.department
and t1.date_>=t2.date_),0) 当日营业厅余额合计 ,
department,date_
into #mytable2
from #mytable t1 select
往日营业厅余额合计,
isnull((select sum(t2.往日营业厅余额合计 )
from #mytable2 t2
where t1.department=t2.department
and t1.date_>=t2.date_),0) 累计1 ,
当日营业厅余额合计,
isnull((select sum(t2.当日营业厅余额合计 )
from #mytable2 t2
where t1.department=t2.department
and t1.date_>=t2.date_),0) 累计2 ,
department,date_
into #aaa
from #mytable2 t1 select * from #aaa --产生的结果为:往日营业厅余额合计 累计1 当日营业厅余额合计 累计2 department date_
0 0 -5 -5 部门1 2008-4-1drop table #mytable2
drop table #mytable
drop table #aaa-------------------------------------------------------------------------------------------------
我要得到的结果是这样:营业厅名称 往日营业厅余额合计 累计1 当日营业厅余额合计 累计2 date_
部门1 0 0 -5 -5 2008-4-1
部门2 0 0 0 0 2008-4-1*/
--下面是我的SQL,--提示出错“服务器: 消息 2770,级别 16,状态 1,行 133
--SELECT INTO 语句的源表和目的表不能相同。”
--怎样改动一下才能达到上面的结果
select
营业厅名称=d.dept_name, 当日营业厅余额合计=isnull(dr_wr.往日营业厅余额合计,0.00),
累计1=isnull(dr_wr.累计1,0.00), 往日营业厅余额合计=isnull(dr_wr.累计2,0.00), 累计2=isnull(dr_wr.累计2,0.00)
from t_department dselect * into #mytable
from
(
select
t1.dr as '(已上)合作厅及代理商应收',
t2.dr as '增加信誉值金额合计',
t1.rq as date_,
t1.ssbm as department
from t_rb_total t1
join t_rb_total t2 on t1.rq=t2.rq and t1.ssbm=t2.ssbm and t1.xm='(已上)合作厅及代理商应收'and t2.xm='增加信誉值金额合计'
) aselect
isnull((select sum(t2.[增加信誉值金额合计]-t2.[(已上)合作厅及代理商应收] )
from #mytable t2
where t1.department=t2.department
and t1.date_>t2.date_),0) 往日营业厅余额合计 ,
isnull((select sum(t2.[增加信誉值金额合计]-t2.[(已上)合作厅及代理商应收] )
from #mytable t2
where t1.department=t2.department
and t1.date_>=t2.date_),0) 当日营业厅余额合计 ,
department,date_
into #mytable2
from #mytable t1 select
往日营业厅余额合计,
isnull((select sum(t2.往日营业厅余额合计 )
from #mytable2 t2
where t1.department=t2.department
and t1.date_>=t2.date_),0) 累计1 ,
当日营业厅余额合计,
isnull((select sum(t2.当日营业厅余额合计 )
from #mytable2 t2
where t1.department=t2.department
and t1.date_>=t2.date_),0) 累计2 ,
department,date_
into #aaa
from #mytable2 t1 left join(select sum(#aaa.往日营业厅余额合计)as '往日营业厅余额合计',
sum(#aaa.累计1)as '累计1',
sum(#aaa.当日营业厅余额合计)as '当日营业厅余额合计',
sum(#aaa.累计2)as '累计2'
from #aaa where #aaa.date_='2008-4-1'group by #aaa.department)as dr_wr
on d.dept_name=dr_wr.departmentwhere d.dept_id like'01%'drop table #mytable2
drop table #mytable
drop table #aaa
往日营业厅余额合计,
isnull((select sum(t2.往日营业厅余额合计 )
from #mytable2 t2
where t1.department=t2.department
and t1.date_>=t2.date_),0) 累计1 ,
当日营业厅余额合计,
isnull((select sum(t2.当日营业厅余额合计 )
from #mytable2 t2
where t1.department=t2.department
and t1.date_>=t2.date_),0) 累计2 ,
department,date_
into #aaa
from #mytable2 t1 left join(select sum(#aaa.往日营业厅余额合计)as '往日营业厅余额合计',
sum(#aaa.累计1)as '累计1',
sum(#aaa.当日营业厅余额合计)as '当日营业厅余额合计',
sum(#aaa.累计2)as '累计2'
from #aaa
where #aaa.date_='2008-4-1'
group by #aaa.department)as dr_wr
on d.dept_name=dr_wr.department where d.dept_id like'01%'
----
这是在做什么呀?#aaa 没有生成你就引用 了呀,当然会出问题呀
sum(#aaa.累计1)as '累计1',
sum(#aaa.当日营业厅余额合计)as '当日营业厅余额合计',
sum(#aaa.累计2)as '累计2'
from #aaa
where #aaa.date_='2008-4-1'
group by #aaa.department
#aaa的查询结果是往日营业厅余额合计 累计1 当日营业厅余额合计 累计2 department date_
0 0 -5 -5 部门1 2008-4-1
t_department的查询结果是dept_id dept_name 011 部门1
012 部门2
01 总部门------------------------------------------------------------------------
需要的报表是这个:
营业厅名称 往日营业厅余额合计 累计1 当日营业厅余额合计 累计2 date_
部门1 0 0 -5 -5 2008-4-1
部门2 0 0 0 0 2008-4-1
---------------------------------------------------------------------------
我的SQL是这样:select
营业厅名称=d.dept_name, 当日营业厅余额合计=isnull(dr_wr.往日营业厅余额合计,0.00),
累计1=isnull(dr_wr.累计1,0.00), 往日营业厅余额合计=isnull(dr_wr.累计2,0.00), 累计2=isnull(dr_wr.累计2,0.00)
from t_department dselect * into #mytable
from
(
select
t1.dr as '(已上)合作厅及代理商应收',
t2.dr as '增加信誉值金额合计',
t1.rq as date_,
t1.ssbm as department
from t_rb_total t1
join t_rb_total t2 on t1.rq=t2.rq and t1.ssbm=t2.ssbm and t1.xm='(已上)合作厅及代理商应收'and t2.xm='增加信誉值金额合计'
) aselect
isnull((select sum(t2.[增加信誉值金额合计]-t2.[(已上)合作厅及代理商应收] )
from #mytable t2
where t1.department=t2.department
and t1.date_>t2.date_),0) 往日营业厅余额合计 ,
isnull((select sum(t2.[增加信誉值金额合计]-t2.[(已上)合作厅及代理商应收] )
from #mytable t2
where t1.department=t2.department
and t1.date_>=t2.date_),0) 当日营业厅余额合计 ,
department,date_
into #mytable2
from #mytable t1 select
往日营业厅余额合计,
isnull((select sum(t2.往日营业厅余额合计 )
from #mytable2 t2
where t1.department=t2.department
and t1.date_>=t2.date_),0) 累计1 ,
当日营业厅余额合计,
isnull((select sum(t2.当日营业厅余额合计 )
from #mytable2 t2
where t1.department=t2.department
and t1.date_>=t2.date_),0) 累计2 ,
department,date_
into #aaa
from #mytable2 t1 left join(select sum(#aaa.往日营业厅余额合计)as '往日营业厅余额合计',
sum(#aaa.累计1)as '累计1',
sum(#aaa.当日营业厅余额合计)as '当日营业厅余额合计',
sum(#aaa.累计2)as '累计2'
from #aaa where #aaa.date_='2008-4-1'group by #aaa.department)as dr_wr
on d.dept_name=dr_wr.departmentwhere d.dept_id like'01%'drop table #mytable2
drop table #mytable
drop table #aaa
--------------------------------------------------------------------------
提示错误是:
服务器: 消息 2770,级别 16,状态 1,行 145
SELECT INTO 语句的源表和目的表不能相同。
sum(#aaa.累计1)as '累计1',
sum(#aaa.当日营业厅余额合计)as '当日营业厅余额合计',
sum(#aaa.累计2)as '累计2'
from #aaa where #aaa.date_='2008-4-1'group by #aaa.department)as dr_wr
on d.dept_name=dr_wr.department where d.dept_id like'01%'
-------------------------------------select sum(#aaa.往日营业厅余额合计)as '往日营业厅余额合计',
sum(#aaa.累计1)as '累计1',
sum(#aaa.当日营业厅余额合计)as '当日营业厅余额合计',
sum(#aaa.累计2)as '累计2'
from #aaa
where #aaa.date_='2008-4-1'
group by #aaa.department