解决方案 »
- 【求SQL】每个用户发布的信息最多只出现1条
- sql server 2000建立的数据库可以在sql server 2005中使用吗?
- Sql server 2005日记传送问题
- 各位大哥大姐,帮小弟写一个SQL语句啊....
- 查询器中删除100记录,如何恢复?
- 邹老大,我的数据库急剧增加的问题还没有解决呀,再来看看
- 求一条SQL语句,把行1,2,3,4,5,6,7转换成列
- 问一个菜鸟级的问题!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
- 在98下如何安装mysql?高手帮忙 !急!!!!!!!!!
- 想做进销存软件,用mssql数据库,做成网络版,请问用vb好还是delphi好,我知道vb的水晶报表很好,但是做成网络版是不是delphi功能更强一些.
- SQL查询问题
- 查询拆分
http://www.cnblogs.com/CareySon/archive/2012/03/07/2383690.html
(5 行受影响)*/
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2010/04/25/5525814.aspx
再者可以通过存储过程的if 判断来实现
if object_id('a') is not null
drop table a
go
create table a
(
id int,
total float
)
go
if object_id('b') is not null
drop table b
go
create table b
(
id int,
price float
)
go
insert into a
select 1,100 union all
select 2,50 union all
select 3,320
go
insert into b
select 1,10 union all
select 3,18 union all
select 4,15go
select isnull(a.id,b.id)id,isnull(total,0)+isnull(B.price,0)total from a full join b on a.id = b.id
if object_id('a') is not null
drop table a
go
create table a
(
id int,
total float
)
go
if object_id('b') is not null
drop table b
go
create table b
(
id int,
price float
)
go
insert into a
select 1,100 union all
select 2,50 union all
select 3,320
go
insert into b
select 1,10 union all
select 3,18 union all
select 4,15go
--查询
select isnull(a.id,b.id)id,isnull(total,0)+isnull(B.price,0)total from a full join b on a.id = b.id
go
select * from a
go
--修改
with t as
(
select isnull(a.id,b.id)id,isnull(B.price,0)price from a full join b on a.id = b.id
)
,t1 as
(
select t.id,t.price from t left join a on t.id = a.id where a.id is null
)
insert into a select id,price from t1
go
update A set total=total+B.price from B where A.id=B.id
go
select * from a
UPDATE 只更新有的,insert 只插人没有的,你先确认下
UPDATE 只更新有的,insert 只插人没有的,你先确认下
先插入的话,a表就会有了ID=4 total=15的记录了。然后再更新,会在ID=4这条上再加上15.变30了。
update a set total=total+b.price from b where a.id=b.id
go
insert a select id,price from b where id not in(select id from a)
go
select * from a
UPDATE 只更新有的,insert 只插人没有的,你先确认下
先插入的话,a表就会有了ID=4 total=15的记录了。然后再更新,会在ID=4这条上再加上15.变30了。
你说的是对的--查询
select isnull(a.id,b.id)id,isnull(total,0)+isnull(B.price,0)total from a full join b on a.id = b.id
go
select * from a
go
--修改
update A set total=isnull(total,0)+isnull(B.price,0) from B where A.id=B.id
go
with t as
(
select isnull(a.id,b.id)id,isnull(B.price,0)price from a full join b on a.id = b.id
)
,t1 as
(
select t.id,t.price from t left join a on t.id = a.id where a.id is null
)
insert into a select id,price from t1
go
select * from a