我现在有个棘手的量表合并的问题想请大家帮我解决一下,因为数据库很大,我这里举个小小的例子:
表A:
Account usage
1 10
2 6
3 6
4 9
表B:
Account usage
3 8
4 7
5 4
8 10
我要把两个表合并成一个表,条件是:Account不同的将usage直接/2作为新表的usage, Account相同的将usage相加再/2作为新表的usage请高手指点!!!
表A:
Account usage
1 10
2 6
3 6
4 9
表B:
Account usage
3 8
4 7
5 4
8 10
我要把两个表合并成一个表,条件是:Account不同的将usage直接/2作为新表的usage, Account相同的将usage相加再/2作为新表的usage请高手指点!!!
表C:
Account usage
1 5
2 3
3 7
4 8
5 2
8 5
declare @a table (Account int,usage int)
insert into @a
select 1,10 union all
select 2,6 union all
select 3,6 union all
select 4,9
--> 测试数据: @b
declare @b table (Account int,usage int)
insert into @b
select 3,8 union all
select 4,7 union all
select 5,4 union all
select 8,10select Account=isnull(a.Account,b.Account),usage=(isnull(a.usage,0)+isnull(b.usage,0))/2
from @a a
full join @b b
on a.Account=b.Account
Account usage
----------- -----------
1 5
2 3
3 7
4 8
5 2
8 5(6 行受影响)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-06 11:29:38
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([Account] int,[usage] int)
insert [A]
select 1,10 union all
select 2,6 union all
select 3,6 union all
select 4,9
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([Account] int,[usage] int)
insert [B]
select 3,8 union all
select 4,7 union all
select 5,4 union all
select 8,10
--------------开始查询--------------------------
select
isnull(a.Account,b.Account) as Account,
(isnull(a.usage,0)+isnull(b.usage,0))/2 as usage
from
a full join b
on
a.Account=b.Account----------------结果----------------------------
/* Account usage
----------- -----------
1 5
2 3
3 7
4 8
5 2
8 5(6 行受影响)
*/
from 表名1 a
full join 表名2 b
on a.Account=b.Account
insert into c
select *** --你的查询语句
from ...