1. 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 行受影响)*/
2. 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 update b set qty=case when a.qty<b.qty then b.qty-a.qty else 0 end from t2 b inner join t1 a on a.id=b.id select * from t2 go drop table t1,t2 /* ID qty ----------- ----------- 184147 22 184214 0 184280 198 184322 0(4 行受影响) */
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 , 120create 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 --1.query ;with cte_play as ( select T1.id,T1.qty,T2.qty as qty2, tmp=T2.qty-T1.qty from T1 left join T2 on T1.id=T2.id ) select id,case when tmp>=0 then qty else qty2 end as qty,note='OK' from cte_play union all select id,-tmp,'NO' from cte_play where tmp<0 order by id /* 184147 28 OK 184214 198 OK 184214 8 NO 184280 2 OK 184322 80 OK 184322 40 NO */--2.updateupdate T2 set qty=case when T2.qty-T1.qty<0 then 0 else T2.qty-T1.qty end from T1 where T2.id=T1.id -- select * from T2 /* 184147 22 184214 0 184280 198 184322 0 */GO drop table T1,T2
out_ku出库表 in_ku库存表if exists(select 1 from sysobjects where id=object_id(out_ku)) drop table out_ku else create table out_ku(id int,qty int) if exists(select 1 from sysobjects where id=object_id(in_ku)) drop table in_ku else create table in_ku(id int,qty int)insert into out_ku select 184147,28 union all select 184214,206 union all select 184280,2 union all select 184322,120insert into in_ku select 184147,50 union all select 184214,198 union all select 184280,200 union all select 184322,80 一: ;with cte as( select b.id,case when a.qty<=b.qty then a.qty else b.qty end as 数量, 'OK' as note from in_ku a,out_ku b where a.id=b.id union select b.id,case when a.qty<=b.qty then b.qty-a.qty else 0 end as 数量, 'NO' as note from in_ku a,out_ku b where a.id=b.id ) select * from cte where 数量>0二: update in_ku set qty=case when a.qty>=b.qty then a.qty-b.qty else 0 end from in_ku a,out_ku b where a.id=b.idselect * from in_ku
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 行受影响)*/
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
update b set qty=case when a.qty<b.qty then b.qty-a.qty else 0 end from t2 b inner join t1 a on a.id=b.id
select * from t2
go
drop table t1,t2
/*
ID qty
----------- -----------
184147 22
184214 0
184280 198
184322 0(4 行受影响)
*/
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 , 120create 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
--1.query
;with cte_play
as
(
select T1.id,T1.qty,T2.qty as qty2, tmp=T2.qty-T1.qty
from T1
left join T2
on T1.id=T2.id
)
select id,case when tmp>=0 then qty else qty2 end as qty,note='OK'
from cte_play
union all
select id,-tmp,'NO'
from cte_play
where tmp<0
order by id
/*
184147 28 OK
184214 198 OK
184214 8 NO
184280 2 OK
184322 80 OK
184322 40 NO
*/--2.updateupdate T2
set qty=case when T2.qty-T1.qty<0 then 0 else T2.qty-T1.qty end
from T1
where T2.id=T1.id
--
select * from T2
/*
184147 22
184214 0
184280 198
184322 0
*/GO
drop table T1,T2
out_ku出库表
in_ku库存表if exists(select 1 from sysobjects where id=object_id(out_ku))
drop table out_ku
else
create table out_ku(id int,qty int)
if exists(select 1 from sysobjects where id=object_id(in_ku))
drop table in_ku
else
create table in_ku(id int,qty int)insert into out_ku
select 184147,28
union all
select 184214,206
union all
select 184280,2
union all
select 184322,120insert into in_ku
select 184147,50
union all
select 184214,198
union all
select 184280,200
union all
select 184322,80
一:
;with cte as(
select b.id,case when a.qty<=b.qty then a.qty else b.qty end as 数量, 'OK' as note from in_ku a,out_ku b where a.id=b.id
union
select b.id,case when a.qty<=b.qty then b.qty-a.qty else 0 end as 数量, 'NO' as note from in_ku a,out_ku b where a.id=b.id
)
select * from cte where 数量>0二:
update in_ku set qty=case when a.qty>=b.qty then a.qty-b.qty else 0 end from in_ku a,out_ku b where a.id=b.idselect * from in_ku