insert c (id,plus,negative) select a.id,case when a.[count]-b.[out]>=0 then a.[count]-b.[out] end,case when a.[count]-b.[out]<0 then b.[out]-a.[count] end from a,b where a.id=b.id
insert into tableC(id,plus,negative) select a.id,case when a.count-b.out>=0 then a.count-b.out end, case a.count-b.count<0 then b.out-a.count end from a,b where a.id=b.id
insert c (id,plus,negative) select a.id,(case when a.[count]-b.[out]>=0 then a.[count]-b.[out] end),(case when a.[count]-b.[out]<0 then b.[out]-a.[count] end) from a join b on a.id = b.id
insert into tableC(id,plus,negative) select A.id,case when a.count-b.out>=0 then a.count-b.out end, case when a.count-b.out>=0 then a.count-b.out end from A,B where A.id=B.id
create table C([id] int,plus int,negative int,other varchar(100)) create table A([id] int,[count] int) insert into A([id],[count]) values(1,100) insert into A([id],[count]) values(2,200) insert into A([id],[count]) values(3,300) insert into A([id],[count]) values(4,400) create table B([id] int, out int) insert into B([id],out) values(2,20) insert into B([id],out) values(4,600) insert into C([id],plus,negative) select A.id,(case when (A.[count]-B.out)>0 then (A.[count]-B.out) end),(case when (A.[count]-B.out)<0 then (B.out-A.[count]) end) from A,B where A.id=B.id select * from C 结果为:id plus negative other ----------- ----------- ----------- -------------------- 2 180 0 NULL 4 0 200 NULL(2 row(s) affected)
select a.id,case when a.count-b.out>=0 then a.count-b.out end,
case a.count-b.count<0 then b.out-a.count end
from a,b where a.id=b.id
select a.id,(case when a.[count]-b.[out]>=0 then a.[count]-b.[out] end),(case when a.[count]-b.[out]<0 then b.[out]-a.[count] end)
from a join b on a.id = b.id
select A.id,case when a.count-b.out>=0 then a.count-b.out end,
case when a.count-b.out>=0 then a.count-b.out end
from A,B where A.id=B.id
create table C([id] int,plus int,negative int,other varchar(100))
create table A([id] int,[count] int)
insert into A([id],[count])
values(1,100)
insert into A([id],[count])
values(2,200)
insert into A([id],[count])
values(3,300)
insert into A([id],[count])
values(4,400)
create table B([id] int, out int)
insert into B([id],out)
values(2,20)
insert into B([id],out)
values(4,600)
insert into C([id],plus,negative)
select A.id,(case when (A.[count]-B.out)>0 then (A.[count]-B.out) end),(case when (A.[count]-B.out)<0 then (B.out-A.[count]) end)
from A,B where A.id=B.id
select * from C
结果为:id plus negative other
----------- ----------- ----------- --------------------
2 180 0 NULL
4 0 200 NULL(2 row(s) affected)