现有两个表
du1
大票号 数量 剩余 状态
2001 100 20 作废
2002 100 100 作废
2003 100 80 正常
2004 100 100 正常
2005 100 40 正常
du2
大票号 已开发票
2001 60
2003 100
2005 40
先想查处未开完发票的大票和数量如何查SQL如何写思路
1、现状态‘作废’但因已发货,要显示如2001
2、现状态‘作废’但因未发货,不显示如2002
3、新票状态‘正常’要显示 如2004
4、现状态为‘正常’未开完票的如2005
结果是
大票号 数量 剩余 已开票 剩余开票
2001 100 20 60 20
2004 100 100 0 100
2005 100 40 40 60
du1
大票号 数量 剩余 状态
2001 100 20 作废
2002 100 100 作废
2003 100 80 正常
2004 100 100 正常
2005 100 40 正常
du2
大票号 已开发票
2001 60
2003 100
2005 40
先想查处未开完发票的大票和数量如何查SQL如何写思路
1、现状态‘作废’但因已发货,要显示如2001
2、现状态‘作废’但因未发货,不显示如2002
3、新票状态‘正常’要显示 如2004
4、现状态为‘正常’未开完票的如2005
结果是
大票号 数量 剩余 已开票 剩余开票
2001 100 20 60 20
2004 100 100 0 100
2005 100 40 40 60
from du1 left join (
select 大票号,sum(已开发票) as 已开发票
from du2
group by 大票号) as t
on du1.大票号 = t.大票号
from du1 left join (
select 大票号,sum(已开发票) as 已开发票
from du2
group by 大票号) as t
on du1.大票号 = t.大票号
where not (du1.数量=du1剩余 and 状态 = '作废')
and not (状态 = '正常' and du1.数量=isnull(t.已开票,0))
from du1 left join (
select 大票号,sum(已开发票) as 已开发票
from du2
group by 大票号) as t
on du1.大票号 = t.大票号
where not (du1.数量=du1.剩余 and du1.状态 = '作废')
and not (du1.状态 = '正常' and du1.数量=isnull(t.已开票,0))
我就没有想那样复杂。select 大票号,数量,剩余,已开票,剩余开票=数量-已开票
from du1
left join du2
on du1.大票号=du2.大票号
where ( 状态=正常 and du1.数量-du2.已开票<>0)
or
( 状态=作废 and du1.大票号 not in (select 大票号 from du2 ))
CREATE TABLE du1
(大票号 VARCHAR(20),数量 int,剩余 int, 状态 VARCHAR(30))
INSERT INTO du1
SELECT '2001',100,20,'作废' UNION ALL
SELECT '2002',100,100,'作废' UNION ALL
SELECT '2003',100,80,'正常' UNION ALL
SELECT '2004',100,100,'正常' UNION ALL
SELECT '2005',100,40,'正常' CREATE TABLE du2
(大票号 VARCHAR(20),已开发票 int)
INSERT INTO du2
SELECT '2001',60 UNION ALL
SELECT '2003',100 UNION ALL
SELECT '2005',40select du1.大票号,数量,剩余,已开发票=isnull(已开发票,0),剩余开票=数量-isnull(已开发票,0)
from du1
left join du2
on du1.大票号=du2.大票号
where ( 状态='正常' and (du1.数量-du2.已开发票<>0 or du1.数量=du1.剩余))
or
( 状态='作废' and du1.大票号 in (select 大票号 from du2 ))
insert into @du1 select '2001',100,20,'作废'
union all select '2002',100,100,'作废'
union all select '2003',100,80,'正常'
union all select '2004',100,100,'正常'
union all select '2005',100,40,'正常'
declare @du2 table (大票号 int,已开发票 int)
insert into @du2 select 2001,60
union all select 2003,100
union all select 2005,40
select * from
(select a.大票号,a.数量,a.剩余,a.状态 ,ISNULL(b.已开发票,0) 已开票 from @du1 a
left join @du2 b on a.大票号=b.大票号) A
where (状态='作废' and 已开票<>0) or (状态='正常' and 已开票<数量)(3 行受影响)
大票号 数量 剩余 状态 已开票
----------- ----------- ----------- ---------- -----------
2001 100 20 作废 60
2004 100 100 正常 0
2005 100 40 正常 40(3 行受影响)
drop table du1
go
if exists (select * from dbo.sysobjects where id=object_id('du2')and objectproperty(id,'istable')=1)
drop table du2
go
--判断du1,du2表是否存在
create table du1(
大票号 varchar(10) primary key,
数量 int,
剩余 int,
状态 varchar(10)
)
go
create table du2(
大票号 varchar(10),
已开票 int
)
go
--创建表du1,du2
insert into du1 values('2001',100,20,'作废' )
insert into du1 values('2002',100,100,'作废' )
insert into du1 values('2003',100,80,'正常' )
insert into du1 values('2004',100,100,'正常' )
insert into du1 values('2005',100,40,'正常' )
insert into du2 values('2001',60)
insert into du2 values('2003',100)
insert into du2 values('2005',40)
go
--插入表值
select a.大票号,a.数量,a.剩余,isnull(b.已开票,0)as 已开票,(isnull(a.数量,0)-isnull(c.剩余,0)-isnull(b.已开票,0))as 剩余开票
from du1 a
left outer join du2 b on a.大票号=b.大票号
left outer join (
select a.大票号,a.剩余 from du1 a where a.状态='作废') c on c.大票号=a.大票号
where(isnull(a.数量,0)-isnull(c.剩余,0)-isnull(b.已开票,0))>0
--查询语句
/*-------------------------------------------结果
大票号 数量 剩余 已开票 剩余开票
2001 100 20 60 20
2004 100 100 0 100
2005 100 40 40 60
*/
du1
大票号 数量 剩余 状态
2001 100 20 作废
2002 100 100 作废
2003 100 80 正常
2004 100 100 正常
2005 100 40 正常
du2
大票号 已开发票
2001 60
2003 100
2005 40
先想查处未开完发票的大票和数量如何查SQL如何写 思路
1、现状态‘作废’但因已发货,要显示如2001
2、现状态‘作废’但因未发货,不显示如2002
3、新票状态‘正常’要显示 如2004
4、现状态为‘正常’未开完票的如2005
结果是
大票号 数量 剩余 已开票 剩余开票
2001 100 20 60 20
2004 100 100 0 100
2005 100 40 40 60
select a.大票号, a.数量, a.剩余, isnull(b.已开发票,0) as 已开票, (case when a.状态 ='作废' then a.剩余 else a.数量- isnull(b.已开发票,0) end) as 剩余开票
from du1 a left join du2 b on a.大票号=b.大票号
where (a.状态='正常' and isnull(b.已开发票,0)<a.数量) or (a.状态 ='作废' and a.剩余<a.数量)