表a： 应收款，表b：已收款，要求查询未收款（即同一ID，应收款、已收款差值不是0的），其中税款不包括在应收款内
表a：
ID 应收明细 应收金额
1 税款 10000.00
1 服务费 500.00
1 交通费 200.00
1 材料费 1000.00
2 服务费 400.00
3 服务费 500.00
3 交通费 300.00
表b：
ID 已收金额
1 1000.00
2 400.00
3 500.00
要求得到：
ID 应收总额 已收金额 未收金额
1 1700 1000 700
3 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
--------------开始查询--------------------------
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
*/
------解决思路----------------------
SELECT T1.ID,T1.[应收总额],ISNULL(T2.[已收金额],0)[已收金额],T1.[应收总额]-ISNULL(T2.[已收金额],0) [未收金额]
FROM(SELECT ID,SUM(应收金额)[应收总额] FROM A GROUP BY ID)T1
LEFT JOIN B T2 ON T1.ID=T2.ID
WHERE T1.[应收总额]<>ISNULL(T2.[已收金额],0)
------解决思路----------------------
----------------------------------------------------------------
-- 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
*/
------解决思路----------------------
SELECT T1.ID,T3.Name,T1.[应收总额],ISNULL(T2.[已收金额],0)[已收金额],T1.[应收总额]-ISNULL(T2.[已收金额],0) [未收金额]这样应该可以吧
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)