34,576
社区成员
发帖
与我相关
我的任务
分享
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
-->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 個資料列受到影響)
*/
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 行受影响)
DECLARE @TB1 TABLE(feename VARCHAR(10),money money)
INSERT @TB1
SELECT '管理 费' ,20.00 UNION ALL
SELECT '速递 运费', 600.00 UNION ALL
SELECT '通信费' , 450.00
DECLARE @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 行)
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
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.00
SELECT
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 行受影响)
*/