create table t1(ID int,qty int)
insert into t1 select 184147,28
insert into t1 select 184214,206
insert into t1 select 184280,2
insert into t1 select 184322,120
create table t2(ID int,qty int)
insert into t2 select 184147,50
insert into t2 select 184214,198
insert into t2 select 184280,200
insert into t2 select 184322,80
go
select a.id,case when a.qty<b.qty then a.qty else b.qty end qty,'OK' as note from t1 a left join t2 b on a.id=b.id
union all
select a.id,a.qty-b.qty,'NO' from t1 a inner join t2 b on a.id=b.id and a.qty>b.qty order by id
go
drop table t1,t2
/*
id qty note
----------- ----------- ----
184147 28 OK
184214 198 OK
184214 8 NO
184280 2 OK
184322 80 OK
184322 40 NO(6 行受影响)*/
ID qty note
183201 NULL OK
184230 NULL OK
184281 NULL OK
184767 NULL OK
184775 NULL OK如果库存表里没有这个编号,就提示为空,上面附的是我的一些测试数据,如果库存表无此ID,也正常写出来不能为空
出库表
ID qty
183288 80则显示为
ID qty note
183288 80 NO
insert into t1 select 184147,28
insert into t1 select 184214,206
insert into t1 select 184280,2
insert into t1 select 184322,120
create table t2(ID int,qty int)
insert into t2 select 184147,50
insert into t2 select 184214,198
insert into t2 select 184280,200
insert into t2 select 184322,80
go
select a.id,case when a.qty<b.qty then a.qty else b.qty end qty,'OK' as note from t1 a left join t2 b on a.id=b.id
union all
select a.id,a.qty-b.qty,'NO' from t1 a inner join t2 b on a.id=b.id and a.qty>b.qty order by id
go
drop table t1,t2
/*
id qty note
----------- ----------- ----
184147 28 OK
184214 198 OK
184214 8 NO
184280 2 OK
184322 80 OK
184322 40 NO(6 行受影响)*/
ID qty note
183201 NULL OK
184230 NULL OK
184281 NULL OK
184767 NULL OK
184775 NULL OK如果库存表里没有这个编号,就提示为空,上面附的是我的一些测试数据,如果库存表无此ID,也正常写出来不能为空
出库表
ID qty
183288 80则显示为
ID qty note
183288 80 NO
create table t1(ID int,qty int)
insert into t1 select 184147,28
insert into t1 select 184214,206
insert into t1 select 184280,2
insert into t1 select 184322,120
create table t2(ID int,qty int)
insert into t2 select 184147,50
insert into t2 select 184214,198
--insert into t2 select 184280,200
insert into t2 select 184322,80
go
select a.id,case when b.qty is null then a.qty else case when a.qty<b.qty then a.qty else b.qty end end qty,case when b.qty is null then 'NO' else 'OK' end as note from t1 a left join t2 b on a.id=b.id
union all
select a.id,a.qty-b.qty,'NO' from t1 a inner join t2 b on a.id=b.id and a.qty>b.qty order by id
go
drop table t1,t2
/*
id qty note
----------- ----------- ----
184147 28 OK
184214 198 OK
184214 8 NO
184280 2 NO
184322 80 OK
184322 40 NO(6 行受影响)*/