子表结构及数据如下(DEMO)单据号 存货编码 数量
001 c001 1
001 c002 2
001 c003 1
002 c001 1
002 c003 3
003 c001 1
003 c002 4
004 c001 1
004 c002 5
005 c001 2
005 c002 3
条件:
如:过滤出同一张单据内 只有两种存货,且存货 为c001和c002,并且C001的数量为>=1 and <=2,C002的数量为>=1 and <=4
以上数量按上述条件,符合的为单据003,005,但SQL如何写?急..谢谢各位.注:条件其实为多样,有可能为找同一张单据为的三种货,每一种对应一定的数量
001 c001 1
001 c002 2
001 c003 1
002 c001 1
002 c003 3
003 c001 1
003 c002 4
004 c001 1
004 c002 5
005 c001 2
005 c002 3
条件:
如:过滤出同一张单据内 只有两种存货,且存货 为c001和c002,并且C001的数量为>=1 and <=2,C002的数量为>=1 and <=4
以上数量按上述条件,符合的为单据003,005,但SQL如何写?急..谢谢各位.注:条件其实为多样,有可能为找同一张单据为的三种货,每一种对应一定的数量
where t1.单据号 = t2.单据号 and t1.存货编码 = 'c001' and t2.存货编码 = 'c002'
and t1.数量 between 1 and 2 and t2.数量 between 1 and 4 and t1.单据号 not in
(select distinct 单据号 from demo where 存货编码 <> 'c001' and 存货编码 <> 'c002' )
insert into demo values('001', 'c001' ,1)
insert into demo values('001', 'c002' ,2)
insert into demo values('001', 'c003' ,1)
insert into demo values('002', 'c001' ,1)
insert into demo values('002', 'c003' ,3)
insert into demo values('003', 'c001' ,1)
insert into demo values('003', 'c002' ,4)
insert into demo values('004', 'c001' ,1)
insert into demo values('004', 'c002' ,5 )
insert into demo values('005', 'c001' ,2)
insert into demo values('005', 'c002' ,3)
go
select t1.单据号 from demo t1 , demo t2
where t1.单据号 = t2.单据号 and t1.存货编码 = 'c001' and t2.存货编码 = 'c002'
and t1.数量 between 1 and 2 and t2.数量 between 1 and 4 and t1.单据号 not in
(select distinct 单据号 from demo where 存货编码 <> 'c001' and 存货编码 <> 'c002' )drop table demo/*
单据号
----------
003
005(所影响的行数为 2 行)
*/
insert into tb4
select '001', 'c001', 1
union
select '001', 'c002', 2
union
select '001','c003', 1
union
select '002','c001', 1
union
select '002','c003', 3
union
select '003','c001',1 union
select '003','c002', 4 union select
'004','c001' ,1 union select
'004' ,'c002' ,5 union select
'005','c001' ,2 union select
'005' ,'c002' ,3 select * from tb4select number,max(case when names='c001' then counnum else null end) 'c001',
max(case when names='c002' then counnum else null end) 'c002',
max(case when names='c003' then counnum else null end) 'c003' from
tb4 group by numberselect * from
(
select number,max(case when names='c001' then counnum else null end) 'c001',
max(case when names='c002' then counnum else null end) 'c002',
max(case when names='c003' then counnum else null end) 'c003' from
tb4 group by number
) a where c001 between 1 and 2 and c002 between 1 and 4 and
c001 is not null and c002 is not null and isnull(c003,0)<=0
go
--> -->
declare @T table([单据号] nvarchar(3),[存货编码] nvarchar(4),[数量] int)
Insert @T
select N'001',N'c001',1 union all
select N'001',N'c002',2 union all
select N'001',N'c003',1 union all
select N'002',N'c001',1 union all
select N'002',N'c003',3 union all
select N'003',N'c001',1 union all
select N'003',N'c002',4 union all
select N'004',N'c001',1 union all
select N'004',N'c002',5 union all
select N'005',N'c001',2 union all
select N'005',N'c002',3
Select *
from
@T AS a
WHERE
NOT (
EXISTS(SELECT 1 FROM @T WHERE [单据号]=a.[单据号] AND[存货编码]='c001' AND [数量]IN (1,2))
AND
EXISTS(SELECT 1 FROM @T WHERE [单据号]=a.[单据号] AND[存货编码]='c002' AND [数量]BETWEEN 1 AND 4)
AND
EXISTS(SELECT 1 FROM @T WHERE [单据号]=a.[单据号] HAVING COUNT(1)=2)
)
/*
单据号 存货编码 数量
001 c001 1
001 c002 2
001 c003 1
002 c001 1
002 c003 3
004 c001 1
004 c002 5
*/
SELECT * FROM (
SELECT * FROM DEMO d
PIVOT (SUM(数量) FOR 存货编码 in([c001],[c002],[c003])) p
) t where (t.c001>=1 and t.c001<=2)
and (t.c002>=1 and t.c002<=4)
and t.c003 is null