有下面的表,把相同CARDCODE的INQTY相加起来,然后减去,相同CARDCODE的OUTQTY相加。得到的结果是前面相加的大于0的结果。也就是:
15730005 L300276 Z05 2 0
15740041 L300276 Z05 1 0表A
CARDCODE ITEMCODE WHSCODE INQTY OUTQTY
15720005 L300276 Z05 1 0
15720005 L300276 Z05 0 1
15720019 L300276 Z05 2 0
15720019 L300276 Z05 0 2
15720031 L300276 Z05 1 0
15720031 L300276 Z05 0 1
15720031 L300276 Z05 1 0
15720031 L300276 Z05 0 1
15720044 L300276 Z05 1 0
15720044 L300276 Z05 0 1
15730004 L300276 Z05 2 0
15730004 L300276 Z05 0 2
15730005 L300276 Z05 2 0
15730016 L300276 Z05 2 0
15730016 L300276 Z05 0 2
15730016 L300276 Z05 1 0
15730016 L300276 Z05 0 1
15730016 L300276 Z05 2 0
15730016 L300276 Z05 0 2
15740041 L300276 Z05 1 0
15730005 L300276 Z05 2 0
15740041 L300276 Z05 1 0表A
CARDCODE ITEMCODE WHSCODE INQTY OUTQTY
15720005 L300276 Z05 1 0
15720005 L300276 Z05 0 1
15720019 L300276 Z05 2 0
15720019 L300276 Z05 0 2
15720031 L300276 Z05 1 0
15720031 L300276 Z05 0 1
15720031 L300276 Z05 1 0
15720031 L300276 Z05 0 1
15720044 L300276 Z05 1 0
15720044 L300276 Z05 0 1
15730004 L300276 Z05 2 0
15730004 L300276 Z05 0 2
15730005 L300276 Z05 2 0
15730016 L300276 Z05 2 0
15730016 L300276 Z05 0 2
15730016 L300276 Z05 1 0
15730016 L300276 Z05 0 1
15730016 L300276 Z05 2 0
15730016 L300276 Z05 0 2
15740041 L300276 Z05 1 0
sum(INQTY)-sum(OUTQTY)
from ta
group by CARDCODE having sum(INQTY)-sum(OUTQTY)>0
drop table ta
Go
Create table ta([CARDCODE] int,[ITEMCODE] nvarchar(7),[WHSCODE] nvarchar(3),[INQTY] int,[OUTQTY] int)
Insert ta
select 15720005,N'L300276',N'Z05',1,0 union all
select 15720005,N'L300276',N'Z05',0,1 union all
select 15720019,N'L300276',N'Z05',2,0 union all
select 15720019,N'L300276',N'Z05',0,2 union all
select 15720031,N'L300276',N'Z05',1,0 union all
select 15720031,N'L300276',N'Z05',0,1 union all
select 15720031,N'L300276',N'Z05',1,0 union all
select 15720031,N'L300276',N'Z05',0,1 union all
select 15720044,N'L300276',N'Z05',1,0 union all
select 15720044,N'L300276',N'Z05',0,1 union all
select 15730004,N'L300276',N'Z05',2,0 union all
select 15730004,N'L300276',N'Z05',0,2 union all
select 15730005,N'L300276',N'Z05',2,0 union all
select 15730016,N'L300276',N'Z05',2,0 union all
select 15730016,N'L300276',N'Z05',0,2 union all
select 15730016,N'L300276',N'Z05',1,0 union all
select 15730016,N'L300276',N'Z05',0,1 union all
select 15730016,N'L300276',N'Z05',2,0 union all
select 15730016,N'L300276',N'Z05',0,2 union all
select 15740041,N'L300276',N'Z05',1,0
Go
select CARDCODE,
sum(INQTY)-sum(OUTQTY)
from ta
group by CARDCODE having sum(INQTY)-sum(OUTQTY)>0
/*
CARDCODE
----------- -----------
15730005 2
15740041 1(2 個資料列受到影響)
*/
if object_id('tempdb.dbo.#ta') is not null drop table #ta
go
create table #ta([CARDCODE] int,[ITEMCODE] varchar(7),[WHSCODE] varchar(3),[INQTY] int,[OUTQTY] int)
insert #ta
select 15720005,'L300276','Z05',1,0 union all
select 15720005,'L300276','Z05',0,1 union all
select 15720019,'L300276','Z05',2,0 union all
select 15720019,'L300276','Z05',0,2 union all
select 15720031,'L300276','Z05',1,0 union all
select 15720031,'L300276','Z05',0,1 union all
select 15720031,'L300276','Z05',1,0 union all
select 15720031,'L300276','Z05',0,1 union all
select 15720044,'L300276','Z05',1,0 union all
select 15720044,'L300276','Z05',0,1 union all
select 15730004,'L300276','Z05',2,0 union all
select 15730004,'L300276','Z05',0,2 union all
select 15730005,'L300276','Z05',2,0 union all
select 15730016,'L300276','Z05',2,0 union all
select 15730016,'L300276','Z05',0,2 union all
select 15730016,'L300276','Z05',1,0 union all
select 15730016,'L300276','Z05',0,1 union all
select 15730016,'L300276','Z05',2,0 union all
select 15730016,'L300276','Z05',0,2 union all
select 15740041,'L300276','Z05',1,0--------------------------------查询开始------------------------------select CARDCODE,ITEMCODE,[WHSCODE],sum(INQTY-OUTQTY) from #ta
where ITEMCODE='L300276' and WHSCODE='Z05'
group by CARDCODE,ITEMCODE,[WHSCODE]
having sum(INQTY-OUTQTY)>0
/*
CARDCODE ITEMCODE WHSCODE
----------- -------- ------- -----------
15730005 L300276 Z05 2
15740041 L300276 Z05 1(2 行受影响)*/