----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-11-12 15:00:16
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64)
-- Jul 22 2014 15:26:36
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([ID] int,[应收明细] varchar(6),[应收金额] numeric(7,2))
insert [A]
select 1,'税款',10000.00 union all
select 1,'服务费',500.00 union all
select 1,'交通费',200.00 union all
select 1,'材料费',1000.00 union all
select 2,'服务费',400.00 union all
select 3,'服务费',500.00 union all
select 3,'交通费',300.00
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([ID] int,[已收金额] numeric(6,2))
insert [B]
select 1,1000.00 union all
select 2,400.00 union all
select 3,500.00
--------------开始查询--------------------------select a.id,sum(应收金额)应收金额,已收金额
from [A] left join [B] on a.id=b.id
where a.应收明细<>'税款'
group by a.id,已收金额
order by a.id
----------------结果----------------------------
/*
id 应收金额 已收金额
----------- --------------------------------------- ---------------------------------------
1 1700.00 1000.00
2 400.00 400.00
3 800.00 500.00
*/
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-11-12 15:00:16
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64)
-- Jul 22 2014 15:26:36
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([ID] int,[应收明细] varchar(6),[应收金额] numeric(7,2))
insert [A]
select 1,'税款',10000.00 union all
select 1,'服务费',500.00 union all
select 1,'交通费',200.00 union all
select 1,'材料费',1000.00 union all
select 2,'服务费',400.00 union all
select 3,'服务费',500.00 union all
select 3,'交通费',300.00
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([ID] int,[已收金额] numeric(6,2))
insert [B]
select 1,1000.00 union all
select 2,400.00 union all
select 3,500.00
--------------开始查询--------------------------select a.id,sum(应收金额)应收金额,已收金额
from [A] left join [B] on a.id=b.id
where a.应收明细<>'税款'
group by a.id,已收金额
order by a.id
----------------结果----------------------------
/*
id 应收金额 已收金额
----------- --------------------------------------- ---------------------------------------
1 1700.00 1000.00
2 400.00 400.00
3 800.00 500.00
*/
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-11-12 15:00:16
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64)
-- Jul 22 2014 15:26:36
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([ID] int,[应收明细] varchar(6),[应收金额] numeric(7,2))
insert [A]
select 1,'税款',10000.00 union all
select 1,'服务费',500.00 union all
select 1,'交通费',200.00 union all
select 1,'材料费',1000.00 union all
select 2,'服务费',400.00 union all
select 3,'服务费',500.00 union all
select 3,'交通费',300.00
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([ID] int,[已收金额] numeric(6,2))
insert [B]
select 1,1000.00 union all
select 2,400.00 union all
select 3,500.00
--------------开始查询--------------------------select a.id,sum(应收金额)应收金额,已收金额,sum(应收金额)-已收金额 as 未收金额
from [A] left join [B] on a.id=b.id
where a.应收明细<>'税款'
group by a.id,已收金额
having sum(应收金额)-已收金额<>0
order by a.id
----------------结果----------------------------
/*
id 应收金额 已收金额 未收金额
----------- --------------------------------------- --------------------------------------- ---------------------------------------
1 1700.00 1000.00 700.00
3 800.00 500.00 300.00
*/
FROM(SELECT ID,SUM(应收金额)[应收总额] FROM A GROUP BY ID)T1
LEFT JOIN B T2 ON T1.ID=T2.ID
WHERE T1.[应收总额]<>ISNULL(T2.[已收金额],0)
ID NAME
1 张三
2 李四
3 王五查询结果名字 应收总额 已收金额 未收金额
张三 1700 1000 700
王五 800 500 300
ID NAME
1 张三
2 李四
3 王五 查询结果 名字 应收总额 已收金额 未收金额
张三 1700 1000 700
王五 800 500 300
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-11-12 15:00:16
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64)
-- Jul 22 2014 15:26:36
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([ID] int,[应收明细] varchar(6),[应收金额] numeric(7,2))
insert [A]
select 1,'税款',10000.00 union all
select 1,'服务费',500.00 union all
select 1,'交通费',200.00 union all
select 1,'材料费',1000.00 union all
select 2,'服务费',400.00 union all
select 3,'服务费',500.00 union all
select 3,'交通费',300.00
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([ID] int,[已收金额] numeric(6,2))
insert [B]
select 1,1000.00 union all
select 2,400.00 union all
select 3,500.00
--> 测试数据:[C]
if object_id('[C]') is not null drop table [C]
go
create table [C]([ID] int,[NAME] varchar(4))
insert [C]
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五'
--------------开始查询--------------------------select c.NAME,sum(应收金额)应收金额,已收金额,sum(应收金额)-已收金额 as 未收金额
from [A] left join [B] on a.id=b.id
INNER JOIN C ON A.ID=C.ID
where a.应收明细<>'税款'
group by C.NAME,已收金额
having sum(应收金额)-已收金额<>0----------------结果----------------------------
/*
NAME 应收金额 已收金额 未收金额
---- --------------------------------------- --------------------------------------- ---------------------------------------
王五 800.00 500.00 300.00
张三 1700.00 1000.00 700.00
*/
FROM(SELECT ID,SUM(应收金额)[应收总额] FROM A GROUP BY ID)T1
LEFT JOIN B T2 ON T1.ID=T2.ID
LEFT JOIN C T3 ON T1.ID=T3.ID
WHERE T1.[应收总额]<>ISNULL(T2.[已收金额],0)这样应该可以吧
非常感谢!
重新开贴了,麻烦再指教!http://bbs.csdn.net/topics/390931187
SELECT T1.ID,T3.Name,T1.[应收总额],ISNULL(T2.[已收金额],0)[已收金额],T1.[应收总额]-ISNULL(T2.[已收金额],0) [未收金额]
FROM(SELECT ID,SUM(应收金额)[应收总额] FROM A WHERE 应收明细<>'税款' GROUP BY ID)T1
LEFT JOIN B T2 ON T1.ID=T2.ID
LEFT JOIN C T3 ON T1.ID=T3.ID
WHERE T1.[应收总额]<>ISNULL(T2.[已收金额],0)