create table minux
(id varchar(10),countnum int)
go
insert into minux values('10001',50)
insert into minux values('10002',50)
insert into minux values('10001',20)
insert into minux values('10002',40)
go
select id,countnum=max(countnum)-min(countnum)
from minux
group by id
go
drop table minux
id countnum
---------- -----------
10001 30
10002 10
测试数据如下:
create table t1(编号 char(5),数量 int)
insert into t1 values('10001', 50)
insert into t1 values('10002', 50)
go
create table t2(编号 char(5),数量 int)
insert into t2 values('10001', 20)
insert into t2 values('10002', 40)
go
select a.编号,数量=sum(a.数量) from
(select 编号,数量 from t1 union all
select 编号,数量=-数量 from t2) a
group by a.编号
go
drop table t1
go
drop table t2
测试数据如下:
create table t1(编号 char(5),数量 int)
insert into t1 values('10001', 50)
insert into t1 values('10002', 50)
go
create table t2(编号 char(5),数量 int)
insert into t2 values('10001', 20)
insert into t2 values('10002', 40)
go
select a.编号,sum(a.数量) from
(select 编号,数量 from t1 union all
select 编号,数量=-数量 from t2) a
group by a.编号
go
drop table t1
go
drop table t2
select a.编号,
(a.数量-(case when b.总数 is null then 0 else b.总数 end)) as 数量
from table1 a left join
( select 编号, sum(数量) as 总数
from table2
group by 编号)
) b
on a.编号=b.编号