复杂查询求救
表A
Date BH Number
2012-12-1 1 10
2012-12-1 2 20
2012-12-1 3 30
2012-12-2 1 10
2012-12-2 2 20
2012-12-3 1 10
表B
Date BH
2012-12-1 2
2012-12-1 3
2012-12-2 1
结果：
2012-12-1 60 50 10
2012-12-1 30 20 10
2012-12-1 10 10 10
即对表A以Date分组统计，后然后以日期为索引联合表B和表A进行查询，表A中相应日期对应的数据再减去联合查询的数据。
表A实际是销售数据，表B是退货数据，表A包含了B的信息，但B中没有包含金额数据。不知道是谁这么设计的数据库，真晕，改结构已经没有办法了，可能是利用现有的结构来做了。
谢谢各位的指点！
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-01-15 23:34:06
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
--Jun 17 2011 00:57:23
--Copyright (c) Microsoft Corporation
--Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[表A]
if object_id('[表A]') is not null drop table [表A]
go
create table [表A]([Date] datetime,[BH] int,[Number] int)
insert [表A]
select '2012-12-1',1,10 union all
select '2012-12-1',2,20 union all
select '2012-12-1',3,30 union all
select '2012-12-2',1,10 union all
select '2012-12-2',2,20 union all
select '2012-12-3',1,10
--> 测试数据：[表B]
if object_id('[表B]') is not null drop table [表B]
go
create table [表B]([Date] datetime,[BH] int)
insert [表B]
select '2012-12-1',2 union all
select '2012-12-1',3 union all
select '2012-12-2',2
--------------开始查询--------------------------
SELECT a.date ,
a.number AS numberA ,
CASE WHEN b.number IS NULL THEN a.number
ELSE b.number
END numberB ,
a.number - CASE WHEN b.number IS NULL THEN 0
ELSE b.number
END numberC
FROM ( SELECT CONVERT(VARCHAR(10), [date], 121) [date] ,
SUM(number) number
FROM [表A]
GROUP BY CONVERT(VARCHAR(10), [date], 121)
) a
LEFT JOIN ( SELECT CONVERT(VARCHAR(10), b.[date], 121) [date] ,
SUM(number) number
FROM [表A] a
INNER JOIN [表B] b ON a.[date] = b.[date]
AND a.bh = b.bh
GROUP BY CONVERT(VARCHAR(10), b.[date], 121)
) b ON a.date = b.date
----------------结果----------------------------
/*
date numberA numberB numberC
---------- ----------- ----------- -----------
2012-12-01 60 50 10
2012-12-02 30 20 10
2012-12-03 10 10 10
(3 行受影响)
*/
select convert(varchar(10),Date,120 ) date ,zj NumberA ,th NumberB,zj-th NumberC from
( select a.Date,sum(a.Number) zj,sum(case when b.BH is null then 0 else Number end ) th
from 表A as a left join 表B as b on a.Date=b.Date and a.BH=b.BH
group by a.Date ) as a
USE test
GO
-->生成表A
if object_id('A') is not null
drop table A
Go
Create table A([Date] datetime,[BH] smallint,[Number] smallint)
Insert into A
Select '2012-12-1',1,10
Union all Select '2012-12-1',2,20
Union all Select '2012-12-1',3,30
Union all Select '2012-12-2',1,10
Union all Select '2012-12-2',2,20
Union all Select '2012-12-3',1,10
-->生成表B
if object_id('B') is not null
drop table B
Go
Create table B([Date] datetime,[BH] smallint)
Insert into B
Select '2012-12-1',2
Union all Select '2012-12-1',3
Union all Select '2012-12-2',2
SELECT
Date
,SUM([A+B])AS [A+B]
,ISNULL(SUM(B),SUM(A))AS [B]
, SUM(A)AS [A]
FROM (
SELECT
A.Date
,A.NumberAS [A+B]
,A.NumberAS [B]
,NULLAS [A]
FROM A
INNER JOIN B ON A.Date=B.Date AND A.BH=B.BH
UNION ALL
SELECT
Date
,Number
,NULL
,Number
FROM A
WHERE NOT EXISTS(SELECT 1 FROM B
WHERE B.Date=A.Date
AND B.BH=A.BH
)
) AS T
GROUP BY T.Date
/*
Date A+B B A
----------------------- ----------- ----------- -----------
2012-12-01 00:00:00.000 60 50 10
2012-12-02 00:00:00.000 30 20 10
2012-12-03 00:00:00.000 10 10 10
*/
Go
