---------------------------------------------------------------- -- 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 WHERE T1.应收明细<>'税款'GROUP BY ID)T1 LEFT JOIN(SELECT ID,SUM(已收金额)[已收金额] FROM B GROUP BY ID)T2 ON T1.ID=T2.ID LEFT JOIN C T3 ON T1.ID=T3.ID WHERE T1.[应收总额]<>ISNULL(T2.[已收金额],0)
select c.NAME,sum(应收金额)应收金额,sum(b.已收金额)已收金额,sum(应收金额)-sum(b.已收金额) 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(应收金额)-sum(b.已收金额)<>0
WITH a(ID,应收明细,应收金额) AS ( 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(ID,已收金额) AS ( SELECT 1,700.00 UNION ALL SELECT 1,300.00 UNION ALL SELECT 2,400.00 UNION ALL SELECT 3,300.00 UNION ALL SELECT 3,200.00 ) ,c(ID,NAME) AS ( SELECT 1,'张三' UNION ALL SELECT 2,'李四' UNION ALL SELECT 3,'王五' ) SELECT c.NAME, ISNULL(ta.应收总额,0) AS 应收总额, ISNULL(tb.已收总额,0) AS 已收总额, ISNULL(ta.应收总额,0) - ISNULL(tb.已收总额,0) AS 未收金额 FROM c LEFT JOIN ( SELECT ID,SUM(应收金额) AS 应收总额 FROM a WHERE 应收明细<>'税款' GROUP BY ID ) ta ON c.ID = ta.ID LEFT JOIN ( SELECT ID,SUM(已收金额) AS 已收总额 FROM b GROUP BY ID ) tb ON c.ID = tb.ID WHERE (ISNULL(ta.应收总额,0) - ISNULL(tb.已收总额,0)) <> 0 NAME 应收总额 已收总额 未收金额 ---- ------------------ ---------------- -------------- 张三 1700.00 1000.00 700.00 王五 800.00 500.00 300.00
必须得先sum聚合 ,嵌套连接
可以了,谢谢!但新问题不断,如果 表a 再增加 2列,账单号、账单日期的话,查询出来的应收总数就不对了,因为我在group by ID 添加LE 帐单号、账单日期语句如下,要怎么改动? SELECT T1.ID,T3.Name,T1.[应收总额],ISNULL(T2.[已收金额],0)[已收金额],T1.[应收总额]-ISNULL(T2.[已收金额],0) [未收金额] FROM(SELECT ID,帐单号,账单日期,SUM(应收金额)[应收总额] FROM A WHERE T1.应收明细<>'税款'GROUP BY ID,帐单号,账单日期)T1 LEFT JOIN(SELECT ID,SUM(已收金额)[已收金额] FROM B GROUP BY ID)T2 ON T1.ID=T2.ID LEFT JOIN C T3 ON T1.ID=T3.ID WHERE T1.[应收总额]<>ISNULL(T2.[已收金额],0) 表a: ID 帐单号 账单日期 应收明细 应收金额 1 A001 2014-10-1 税款 10000.00 1 A002 2014-10-8 服务费 500.00 1 A003 2014-10-10 交通费 200.00 1 A003 2014-10-10 材料费 1000.00 2 A008 2014-10-15 服务费 400.00 3 A010 2014-10-15 服务费 500.00 3 A011 2014-10-16 交通费 300.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
--> 测试数据:[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 WHERE T1.应收明细<>'税款'GROUP BY ID)T1
LEFT JOIN(SELECT ID,SUM(已收金额)[已收金额] FROM B GROUP BY ID)T2 ON T1.ID=T2.ID
LEFT JOIN C T3 ON T1.ID=T3.ID
WHERE T1.[应收总额]<>ISNULL(T2.[已收金额],0)
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(应收金额)-sum(b.已收金额)<>0
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(ID,已收金额) AS (
SELECT 1,700.00 UNION ALL
SELECT 1,300.00 UNION ALL
SELECT 2,400.00 UNION ALL
SELECT 3,300.00 UNION ALL
SELECT 3,200.00
)
,c(ID,NAME) AS (
SELECT 1,'张三' UNION ALL
SELECT 2,'李四' UNION ALL
SELECT 3,'王五'
)
SELECT c.NAME,
ISNULL(ta.应收总额,0) AS 应收总额,
ISNULL(tb.已收总额,0) AS 已收总额,
ISNULL(ta.应收总额,0) - ISNULL(tb.已收总额,0) AS 未收金额
FROM c
LEFT JOIN (
SELECT ID,SUM(应收金额) AS 应收总额
FROM a
WHERE 应收明细<>'税款'
GROUP BY ID
) ta
ON c.ID = ta.ID
LEFT JOIN (
SELECT ID,SUM(已收金额) AS 已收总额
FROM b
GROUP BY ID
) tb
ON c.ID = tb.ID
WHERE (ISNULL(ta.应收总额,0) - ISNULL(tb.已收总额,0)) <> 0
NAME 应收总额 已收总额 未收金额
---- ------------------ ---------------- --------------
张三 1700.00 1000.00 700.00
王五 800.00 500.00 300.00
SELECT T1.ID,T3.Name,T1.[应收总额],ISNULL(T2.[已收金额],0)[已收金额],T1.[应收总额]-ISNULL(T2.[已收金额],0) [未收金额]
FROM(SELECT ID,帐单号,账单日期,SUM(应收金额)[应收总额] FROM A WHERE T1.应收明细<>'税款'GROUP BY ID,帐单号,账单日期)T1
LEFT JOIN(SELECT ID,SUM(已收金额)[已收金额] FROM B GROUP BY ID)T2 ON T1.ID=T2.ID
LEFT JOIN C T3 ON T1.ID=T3.ID
WHERE T1.[应收总额]<>ISNULL(T2.[已收金额],0)
表a:
ID 帐单号 账单日期 应收明细 应收金额
1 A001 2014-10-1 税款 10000.00
1 A002 2014-10-8 服务费 500.00
1 A003 2014-10-10 交通费 200.00
1 A003 2014-10-10 材料费 1000.00
2 A008 2014-10-15 服务费 400.00
3 A010 2014-10-15 服务费 500.00
3 A011 2014-10-16 交通费 300.00
从界面上来讲:
A)报表控件可以自动分组显示明细,并且对分组进行汇总。只需要查询明细就可以了。
B)表格控件通常没有汇总功能,不过有合并单元格的功能。你可以把汇总子查询和明细再JOIN起来,这样每条记录都有汇总数,就能合并单元格了。
我打印是用水晶报表的。你的意思是查询结果显示在datagrid,通过点击数据行来显示具体细节资料,打印的时候用水晶报表 进行分组汇总?
报表本身就有同时显示明细和分组汇总的功能,根本不需要用SQL统计。