SELECT ISNULL(A.feename,B.feename)feename,ISNULL(A.money,0)-ISNULL(B.money,0) AS money FROM @TB1 A FULL JOIN @TB2 B ON A.feename=B.feename
select [feename],sum([money])[money] from( select [feename],[money] from tb union all select [feename],-[money] from tb)T group by [feename]
if object_id('[应收费用]') is not null drop table [应收费用] go create table [应收费用]([feename] varchar(8),[money] numeric(5,2)) insert [应收费用] select '管理费',20.00 union all select '速递运费',600.00 union all select '通信费',450.00 if object_id('[应付费用]') is not null drop table [应付费用] go create table [应付费用]([feename] varchar(8),[money] numeric(5,2)) insert [应付费用] select '办 证费',638.79 union all select '附加费用',107.25 union all select '速递运费',555.00SELECT ISNULL(A.feename,B.feename) AS feename, ISNULL(A.[money],0)-ISNULL(B.[money],0) AS [money] FROM [应收费用] A FULL JOIN [应付费用] B ON A.feename=B.feename --测试结果: /* feename money -------- --------------------------------------- 管理费 20.00 速递运费 45.00 通信费 450.00 办 证费 -638.79 附加费用 -107.25(5 行受影响)*/
SELECT ISNULL(A.feename,B.feename)feename,ISNULL(A.money,0)-ISNULL(B.money,0) AS money FROM @TB1 A FULL JOIN @TB2 B ON A.feename=B.feename 楼主自己弄一下吧,FULL JOIN UNION ALL都可以实现的
if object_id('tb')is not null drop table tb go create table tb(feename nvarchar(10),[money] decimal(10,2),[TYPE] varchar(10)) insert tb select N'管理费', 20.00, 0 union all select N'速递运费', 600.00, 0 union all select N'通信费' , 450.00, 0 union all select N'办 证费', 638.79, 1 union all select N'附加费用', 107.25, 1 union all select N'速递运费', 555.00, 1 select a.feename,[money]=a.[money]-b.[money] from( select feename,[money]=sum(case when [TYPE]=0 then [money] else 0 end) from tb group by feename )a, (select feename,[money]=sum(case when [TYPE]=0 then 0 else [money] end) from tb group by feename )b where a.feename=b.feename feename money ---------- --------------------------------------- 办 证费 -638.79 附加费用 -107.25 管理费 20.00 速递运费 45.00 通信费 450.00(5 行受影响)
-->Title:Generating test data -->Author:wufeng4552 -->Date :2009-10-09 16:11:18
declare @A table([feename] nvarchar(4),[money] decimal(18,2)) Insert @A select N'管理费',20.00 union all select N'速递运费',600.00 union all select N'通信费',450.00 declare @B table([feename] nvarchar(4),[money] decimal(18,2)) Insert @B select N'办 证费',638.79 union all select N'附加费用',107.25 union all select N'速递运费',555.00 select [feename],sum([money])[money] from( select [feename],[money] from @a union all select [feename],-[money] from @b)T group by [feename] /* feename money ------- --------------------------------------- 附加费用 -107.25 通信费 450.00 速递运费 45.00 管理费 20.00 办 证费 -638.79(5 個資料列受到影響) */
SELECT ISNULL(A.feename,B.feename) AS feename, ISNULL(A.[money],0)-ISNULL(B.[money],0) AS [money] FROM (SELECT * FROM TB WHERE TYPE=0) A FULL JOIN (SELECT * FROM TB WHERE TYPE=1) B ON A.feename=B.feename
SELECT ISNULL(A.feename,B.feename)feename,ISNULL(A.money,0)-ISNULL(B.money,0) AS money FROM
@TB1 A FULL JOIN @TB2 B ON A.feename=B.feename
select [feename],[money] from tb
union all
select [feename],-[money] from tb)T
group by [feename]
go
create table [应收费用]([feename] varchar(8),[money] numeric(5,2))
insert [应收费用]
select '管理费',20.00 union all
select '速递运费',600.00 union all
select '通信费',450.00
if object_id('[应付费用]') is not null drop table [应付费用]
go
create table [应付费用]([feename] varchar(8),[money] numeric(5,2))
insert [应付费用]
select '办 证费',638.79 union all
select '附加费用',107.25 union all
select '速递运费',555.00SELECT
ISNULL(A.feename,B.feename) AS feename,
ISNULL(A.[money],0)-ISNULL(B.[money],0) AS [money]
FROM
[应收费用] A
FULL JOIN
[应付费用] B
ON A.feename=B.feename
--测试结果:
/*
feename money
-------- ---------------------------------------
管理费 20.00
速递运费 45.00
通信费 450.00
办 证费 -638.79
附加费用 -107.25(5 行受影响)*/
@TB1 A FULL JOIN @TB2 B ON A.feename=B.feename
楼主自己弄一下吧,FULL JOIN
UNION ALL都可以实现的
数据是从一个表里得到的每条数据有一列是表示数据类型的type如果是0表示应收费用,如果是1表示应付费用
费用类型不是固定的
INSERT @TB1
SELECT '管理 费' ,20.00 UNION ALL
SELECT '速递 运费', 600.00 UNION ALL
SELECT '通信费' , 450.00DECLARE @TB2 TABLE(feename VARCHAR(10),money money)
INSERT @TB2
SELECT '办 证费' ,638.79 UNION ALL
SELECT '附加费用', 107.25 UNION ALL
SELECT '速递运费' , 555.00
SELECT ISNULL(A.feename,B.feename)feename,ISNULL(A.money,0)-ISNULL(B.money,0) AS money FROM
@TB1 A FULL JOIN @TB2 B ON A.feename=B.feename(所影响的行数为 3 行)
(所影响的行数为 3 行)feename money
---------- ---------------------
办 证费 -638.7900
附加费用 -107.2500
速递运费 45.0000
管理费 20.0000
通信费 450.0000(所影响的行数为 5 行)
把完整的表结构和测试数据发出来BTW:可以用视图查询的方法把type=0的作为a表,type=1的作为2表
if object_id('tb')is not null drop table tb
go
create table tb(feename nvarchar(10),[money] decimal(10,2),[TYPE] varchar(10))
insert tb select
N'管理费', 20.00, 0 union all select
N'速递运费', 600.00, 0 union all select
N'通信费' , 450.00, 0 union all select
N'办 证费', 638.79, 1 union all select
N'附加费用', 107.25, 1 union all select
N'速递运费', 555.00, 1
select a.feename,[money]=a.[money]-b.[money]
from(
select feename,[money]=sum(case when [TYPE]=0 then [money] else 0 end)
from tb group by feename
)a,
(select feename,[money]=sum(case when [TYPE]=0 then 0 else [money] end)
from tb group by feename
)b
where a.feename=b.feename
feename money
---------- ---------------------------------------
办 证费 -638.79
附加费用 -107.25
管理费 20.00
速递运费 45.00
通信费 450.00(5 行受影响)
-->Author:wufeng4552
-->Date :2009-10-09 16:11:18
declare @A table([feename] nvarchar(4),[money] decimal(18,2))
Insert @A
select N'管理费',20.00 union all
select N'速递运费',600.00 union all
select N'通信费',450.00
declare @B table([feename] nvarchar(4),[money] decimal(18,2))
Insert @B
select N'办 证费',638.79 union all
select N'附加费用',107.25 union all
select N'速递运费',555.00
select [feename],sum([money])[money] from(
select [feename],[money] from @a
union all
select [feename],-[money] from @b)T
group by [feename]
/*
feename money
------- ---------------------------------------
附加费用 -107.25
通信费 450.00
速递运费 45.00
管理费 20.00
办 证费 -638.79(5 個資料列受到影響)
*/
ISNULL(A.feename,B.feename) AS feename,
ISNULL(A.[money],0)-ISNULL(B.[money],0) AS [money]
FROM
(SELECT * FROM TB WHERE TYPE=0) A
FULL JOIN
(SELECT * FROM TB WHERE TYPE=1) B
ON
A.feename=B.feename