表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中没有包含金额数据。不知道是谁这么设计的数据库,真晕,改结构已经没有办法了,可能是利用现有的结构来做了。
谢谢各位的指点!
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中没有包含金额数据。不知道是谁这么设计的数据库,真晕,改结构已经没有办法了,可能是利用现有的结构来做了。
谢谢各位的指点!
表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 2
结果:
2012-12-1 60 50 10
2012-12-2 30 20 10
2012-12-3 10 10 10
-- 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 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
numberB取值的时候我觉得应该还是
CASE WHEN b.number IS NULL THEN 0
ELSE b.number
楼主给的那个12月3日的是不是有误?
ELSE b.number 也可以
GO
-->生成表Aif 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-->生成表Bif 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',2SELECT
Date
,SUM([A+B]) AS [A+B]
,ISNULL(SUM(B),SUM(A)) AS [B]
, SUM(A) AS [A]
FROM (
SELECT
A.Date
,A.Number AS [A+B]
,A.Number AS [B]
,NULL AS [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