select identity(int,1,1) as id into #t from 表1select
a.code,
a.a1,
case when not exists(select 1 from #t where code=a.code and id<a.id)
then isnull((select sum(a2) from 表2 where code=a.code),0)
+ isnull((select sum(a3) from 表3 where code=a.code),0)
end
from
#t a
order by
a.code,a.id
a.code,
a.a1,
case when not exists(select 1 from #t where code=a.code and id<a.id)
then isnull((select sum(a2) from 表2 where code=a.code),0)
+ isnull((select sum(a3) from 表3 where code=a.code),0)
end
from
#t a
order by
a.code,a.id
解决方案 »
- 关于openrowset的问题
- 用sa登录一个数据库,运行查询分析器"SELECT * From system" 显示错误:"对象system无效",怎么回事???
- 求助
- 神啊,救救我吧!一把年纪了,还没弄懂啊!公网如何连接局域网SQL服务器!
- 相同查询在服务器和PC上为何查询耗时相差这么多?
- SQL server有沒有行號這個功能
- 表字段名的大小写转换
- 我在SQL Server中写了一个返回一个游标的存储过程,请问如何用TStoreProc控件取得返回的游标中的数据集.
- SQLServer 7 中如何查看当前的连接用户,和其他连接信息?(初学者问题3)
- sql Lite 统计查询怎么做 ?
- 数据库的疑难问题!!!各位有兴趣吗!!!
- 请教这个存储过程那里错了
left join (select code,isnull(sum(a2),0) as a2
from 表2
group by code) b
on a.code=b.code
left join (select code,isnull(sum(a3),0) as a3
from 表3
group by code) c
on a.code=b.code
[create] table t1
(code int,a1 int)
insert t1 values (1,100)
insert t1 values (2,150)
insert t1 values (1,200)
insert t1 values (3,50)[create] table t2
(code int,a2 int)
insert t2 values (1,50)
insert t2 values (1,200)
insert t2 values (2,500)[create] table t3
(code int,a3 int)
insert t3 values (1,50)
/*------------------查询语句---------------------*/
select * from
(
select a.code,a.a1,
b=case when a.a2 is null and b.a3 is null then a2 else isnull(a.a2,0)+isnull(b.a3,0) end from
(
select a.code,a.a1,sum(a2) as a2 from
(
select code,min(a1) as a1 from t1 group by code
) a left join t2 b on a.code=b.code group by a.code,a.a1
) a left join t3 b on a.code=b.code
) a
union all
select *,null from t1 a where not exists
(select * from
(select code,min(a1) as a1 from t1 group by code) b
where a.code=b.code and a.a1=b.a1)
order by code
/*----------删除环境-----------------------*/
drop table t1
drop table t2
drop table t3/*-----------查询结果-----------------------*/
code a1 b
----------- ----------- -----------
1 100 300
1 200 NULL
2 150 500
3 50 NULL(所影响的行数为 4 行)
你的执行结果如下
1 100.0000 300.0000
2 150.0000 550.0000
1 200.0000 300.0000
3 50.0000 NULL我要的是(b是相同code的sum(表2.a2)+sum(表3.a3))
code a1 b
1 100 300
1 200 null
2 150 500
3 50 null
drop table t1if exists(select 1 from sysobjects where object_id('t2')=id and type='u')
drop table t2if exists(select 1 from sysobjects where object_id('t3')=id and type='u')
drop table t3
gocreate table t1(code int not null,a1 int not null)
create table t2(code int not null,a2 int not null)
create table t3(code int not null,a3 int not null)
goinsert t1
select 1,100 union all
select 2,150 union all
select 1,200 union all
select 3,50insert t2
select 1,50 union all
select 1,200 union all
select 2,500insert t3
select 1,50
goselect * from t1
/*
code a1
1 100
2 150
1 200
3 50
*/
select * from t2
/*
code a2
1 50
1 200
2 500*/
select * from t3
/*
code a3
1 50*/select a.code,a.a1,b=case when not exists(select 1 from t1 where a.code=code and a.a1>a1) then (select sum(a2) from t2 b where a.code=b.code )+(select isnull(sum(a3),0) from t3 b where a.code=b.code ) end from t1 a
/*
code a1 b
1 100 300
2 150 500
1 200 NULL
3 50 NULL
*/