求入库总量不等于出库总量的单号
入库表A：
id 合同号 产品ID 入库数量 入库日期
1 cno-1 121 100 2013-9-14
2 cno-1 122 200 2013-9-14
3 cno-2 121 50 2013-9-17
出库表B：
id 合同号 产品ID 出库数量 出库日期
1 cno-1 121 10 2013-9-20
2 cno-1 122 100 2013-9-22
3 cno-1 121 30 2013-9-23
求入库总量不等于出库总量的单号
[解决办法]
;with ta(id,合同号,产品ID,入库数量,入库日期) as
(
select 1,'cno-1',121,100,'2013-9-14'
union all select 2,'cno-1',122,200,'2013-9-14'
union all select 3,'cno-2',121,50,'2013-9-17'
),
tb(id,合同号,产品ID,出库数量,出库日期) as
(
select 1,'cno-1',121,10,'2013-9-20'
union all select 2,'cno-1',122,100,'2013-9-22'
union all select 3,'cno-1',121,30,'2013-9-23'
)
select a.合同号
from (select 合同号,SUM(入库数量) as 入库数量 from ta group by 合同号)a
left join (select 合同号,SUM(出库数量) as 出库数量 from tb group by 合同号)b
on a.合同号=b.合同号
where a.入库数量<>isnull(b.出库数量,0)
/*
合同号
cno-1
cno-2
*/
----------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2013-09-27 09:36:34
-- Verstion:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
--Feb 10 2012 19:39:15
--Copyright (c) Microsoft Corporation
--Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[合同号] varchar(5),[产品ID] int,[入库数量] int,[入库日期] datetime)
insert [A]
select 1,'cno-1',121,100,'2013-9-14' union all
select 2,'cno-1',122,200,'2013-9-14' union all
select 3,'cno-2',121,50,'2013-9-17'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([id] int,[合同号] varchar(5),[产品ID] int,[出库数量] int,[出库日期] datetime)
insert [B]
select 1,'cno-1',121,10,'2013-9-20' union all
select 2,'cno-1',122,100,'2013-9-22' union all
select 3,'cno-1',121,30,'2013-9-23'
--------------开始查询--------------------------
SELECT
a.产品ID, ISNULL(a.入库数量,0)-ISNULL(b.出库数量,0) AS 库存
FROM
(select 产品ID,SUM(入库数量) AS 入库数量 from [A] GROUP BY 产品ID)a
JOIN
(select 产品ID,SUM(出库数量) AS 出库数量 from [B] GROUP BY 产品ID)b
ON
a.产品ID=b.产品ID
WHERE
ISNULL(a.入库数量,0)-ISNULL(b.出库数量,0)>0
----------------结果----------------------------
/* 产品ID 库存
----------- -----------
121 110
122 100
(2 行受影响)
*/
if OBJECT_ID('tba') is not null drop table tba
create table tba(id int,[合同号] varchar(10),[产品ID] int,[入库数量]int,[入库日期] varchar(10))
insert into tba(id,[合同号],[产品ID],[入库数量],[入库日期])
select 1,'cno-1',121,100,'2013-9-14' union all
select 2,'cno-1',122,200,'2013-9-14' union all
select 3,'cno-2',121,50,'2013-9-17'
if OBJECT_ID('tbb') is not null drop table tbb
create table tbb(id int,[合同号] varchar(10),[产品ID] int,[出库数量] int,[出库日期] varchar(10))
insert into tbb(id,[合同号],[产品ID],[出库数量],[出库日期])
select 1,'cno-1',121,10,'2013-9-20' union all
select 2,'cno-1',122,100,'2013-9-22' union all
select 3,'cno-1',121,30,'2013-9-23'
select a.合同号,a.产品ID,[剩余库存]=ISNULL(a.[该合同该产品入库数量],0)-ISNULL(b.[该合同该产品出库数量],0)
from(
select a.合同号,a.产品ID,SUM([入库数量]) as [该合同该产品入库数量]
from tba a with(nolock) group by a.[合同号],a.[产品ID]
)a left join(
select b.合同号,b.产品ID,SUM([出库数量]) as [该合同该产品出库数量]
from tbb b with(nolock) group by b.[合同号],b.[产品ID]
)b on a.合同号=b.合同号 and a.产品ID=b.产品ID
where ABS(ISNULL(a.[该合同该产品入库数量],0)-ISNULL(b.[该合同该产品出库数量],0))>0
/**
合同号 产品ID 剩余库存
---------- ----------- -----------
cno-1 121 60
cno-1 122 100
cno-2 121 50
*/
SELECT a.[产品ID], ISNULL(a.[入库数量],0)-ISNULL(b.[出库数量],0) AS [剩余库存]
FROM(select [产品ID],SUM([入库数量]) AS [入库数量] from tba GROUP BY [产品ID])a
left join
(select [产品ID],SUM([出库数量]) AS [出库数量] from tbb GROUP BY [产品ID])b
ON a.[产品ID]=b.[产品ID]
WHERE ISNULL(a.[入库数量],0)-ISNULL(b.[出库数量],0)>0
/**
产品ID 剩余库存
----------- -----------
121 110
122 100
*/
--根据你自己的需求选择一种吧
----------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2013-09-27 09:36:34
-- Verstion:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
--Feb 10 2012 19:39:15
--Copyright (c) Microsoft Corporation
--Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[合同号] varchar(5),[产品ID] int,[入库数量] int,[入库日期] datetime)
insert [A]
select 1,'cno-1',121,100,'2013-9-14' union all
select 2,'cno-1',122,200,'2013-9-14' union all
select 3,'cno-2',121,50,'2013-9-17'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([id] int,[合同号] varchar(5),[产品ID] int,[出库数量] int,[出库日期] datetime)
insert [B]
select 1,'cno-1',121,10,'2013-9-20' union all
select 2,'cno-1',122,100,'2013-9-22' union all
select 3,'cno-1',121,30,'2013-9-23'
--------------开始查询--------------------------
SELECT
a.合同号--, ISNULL(a.入库数量,0)-ISNULL(b.出库数量,0) AS 库存
FROM
(select 合同号,SUM(入库数量) AS 入库数量 from [A] GROUP BY 合同号)a
LEFT JOIN
(select 合同号,SUM(出库数量) AS 出库数量 from [B] GROUP BY 合同号)b
ON
a.合同号=b.合同号
WHERE
ISNULL(a.入库数量,0)-ISNULL(b.出库数量,0)>0
----------------结果----------------------------
/*合同号
-----
cno-1
cno-2
(2 行受影响)
*/
