select * from t1,12 where t1.id=t2.id and t1.数量-t2.数量<>0 union select * from t1 where id not in(select id from t2);
select t1.* from t1,12 where t1.id=t2.id and t1.数量-t2.数量<>0 union select * from t1 where id not in(select id from t2);
select 表1.id , 表1.数量 - t.数量 from 表1, (select id , sum(数量) as 数量 from 表2 group by id) t where 表1.id = t.id and 表1.数量 - t.数量 <> 0
--try: select 表1.* from 表1 left join (select id,sum(数量) as 数量 from 表2 group by id) T on 表1.id=T.id where 表1.数量>isnull(T.数量,0)
select a.id ,(a.数量 - isnull (b.数量,0) )as [数量] from 表1 a left join (select id ,sum(数量) as [数量] from 表2 group by id ) b where a.数量 <> b.数量
create table t1(id int,num int) insert into t1 select 1,20 insert into t1 select 2,30 insert into t1 select 3,40 insert into t1 select 4,50 create table t2(id int,num int) insert into t2 select 1,20 insert into t2 select 2,5 insert into t2 select 2,5 insert into t2 select 3,10 insert into t2 select 3,10select t1.id,t1.num-isnull(T.num,0) as num from t1 left join (select id,sum(num) as num from t2 group by id) T on t1.id=T.id where t1.num>isnull(T.num,0)drop table t1,t2id num ----------- ----------- 2 20 3 20 4 50
if object_id('pubs..表1') is not null drop table 表1 gocreate table 表1 ( ID int, 数量 int )insert into 表1(ID,数量) values(1,20) insert into 表1(ID,数量) values(2,30) insert into 表1(ID,数量) values(3,40) insert into 表1(ID,数量) values(4,50)if object_id('pubs..表2') is not null drop table 表2 gocreate table 表2 ( ID int, 数量 int )insert into 表2(ID,数量) values(1,20) insert into 表2(ID,数量) values(2,5) insert into 表2(ID,数量) values(2,5) insert into 表2(ID,数量) values(3,10) insert into 表2(ID,数量) values(3,10)select 表1.id , 表1.数量 - t.数量 as 数量 from 表1, (select id , sum(数量) as 数量 from 表2 group by id) t where 表1.id = t.id and 表1.数量 - t.数量 <> 0 union all select 表1.* from 表1 where id not in (select distinct id from 表2 )drop table 表1,表2id 数量 ----------- ----------- 2 20 3 20 4 50(所影响的行数为 3 行)
select a.id ,(a.num - isnull (b.num,0) )as [num] from t1 a left join (select id ,sum(num) as [num] from t2 group by id ) b on a.id =b.id where a.num- isnull(b.num,0) <>0id num ----------- ----------- 2 20 3 20 4 50(所影响的行数为 3 行)
select a.ID,c.数量 from table1 a, (select id,数量=sum(数量) from table2 group by ID ) c where (a.数量-c.数量>0 and a.id=c.id) union select b.id,b.数量 from table1 b where b.id not in(select id from table2)
union select * from t1 where id not in(select id from t2);
union select * from t1 where id not in(select id from t2);
(select id , sum(数量) as 数量 from 表2 group by id) t
where 表1.id = t.id and 表1.数量 - t.数量 <> 0
select 表1.*
from 表1
left join
(select id,sum(数量) as 数量 from 表2 group by id) T
on 表1.id=T.id
where 表1.数量>isnull(T.数量,0)
select a.id ,(a.数量 - isnull (b.数量,0) )as [数量]
from 表1 a
left join (select id ,sum(数量) as [数量] from 表2 group by id ) b
where a.数量 <> b.数量
insert into t1 select 1,20
insert into t1 select 2,30
insert into t1 select 3,40
insert into t1 select 4,50
create table t2(id int,num int)
insert into t2 select 1,20
insert into t2 select 2,5
insert into t2 select 2,5
insert into t2 select 3,10
insert into t2 select 3,10select t1.id,t1.num-isnull(T.num,0) as num
from t1
left join
(select id,sum(num) as num from t2 group by id) T
on t1.id=T.id
where t1.num>isnull(T.num,0)drop table t1,t2id num
----------- -----------
2 20
3 20
4 50
drop table 表1
gocreate table 表1
(
ID int,
数量 int
)insert into 表1(ID,数量) values(1,20)
insert into 表1(ID,数量) values(2,30)
insert into 表1(ID,数量) values(3,40)
insert into 表1(ID,数量) values(4,50)if object_id('pubs..表2') is not null
drop table 表2
gocreate table 表2
(
ID int,
数量 int
)insert into 表2(ID,数量) values(1,20)
insert into 表2(ID,数量) values(2,5)
insert into 表2(ID,数量) values(2,5)
insert into 表2(ID,数量) values(3,10)
insert into 表2(ID,数量) values(3,10)select 表1.id , 表1.数量 - t.数量 as 数量 from 表1,
(select id , sum(数量) as 数量 from 表2 group by id) t
where 表1.id = t.id and 表1.数量 - t.数量 <> 0
union all
select 表1.* from 表1 where id not in (select distinct id from 表2 )drop table 表1,表2id 数量
----------- -----------
2 20
3 20
4 50(所影响的行数为 3 行)
from t1 a
left join (select id ,sum(num) as [num] from t2 group by id ) b on a.id =b.id
where a.num- isnull(b.num,0) <>0id num
----------- -----------
2 20
3 20
4 50(所影响的行数为 3 行)
from table1 a,
(select id,数量=sum(数量) from table2 group by ID ) c
where (a.数量-c.数量>0 and a.id=c.id)
union
select b.id,b.数量
from table1 b where b.id not in(select id from table2)