select
a.id,
qty=a.qty-isnull(b.qty,0)
from (select id,sum(qty) qty from tableB group by id) a
left join (select id,sum(qty) qty from tableA group by id) b
on a.id=b.id
where a.qty-b.qty<>0
a.id,
qty=a.qty-isnull(b.qty,0)
from (select id,sum(qty) qty from tableB group by id) a
left join (select id,sum(qty) qty from tableA group by id) b
on a.id=b.id
where a.qty-b.qty<>0
解决方案 »
- 如何用一条语句查询重复记录
- 分页问题~sql sever+php
- SQL2005函数不能返回表变量,大家用怎么处理多行的返回内容呢?
- 整条SQL语句,谢谢
- 我的sql server运行程序老是占用cpu 95%左右,查看版本是Microsoft SQL Server 2000 - 8.00.760
- 请问怎样显示表MyUsers的所有已经建立的约束?
- 求生成序列号触发器语句
- SQL Server 2005 SP2之后有哪些补丁,如何在MS网站上搜索到
- 如何用SQL 语句取得 某天(如;2004-04-08)的星期日期?
- DB数据库最多可以有多少条记录
- 用什么办法可以删除数据库里面所记录的所有数据?
- !批量插入数据的重复问题
id,
a.qty-b.qty as qty
from
(select id,sum(qty) as qty from A group by id) a,
(select id,sum(qty) as qty from B group by id) b
where
a.id=b.id
and
a.qty-b.qty<>0
drop table tablea
go
create table tablea([id] int,[qty] int)
insert tablea select 1,4
insert tablea select 1,7
insert tablea select 1,29
insert tablea select 2,12
insert tablea select 2,15
insert tablea select 3,14
go
if object_id('tableb')is not null
drop table tableb
go
create table tableb([id] int,[qty] int)
insert tableb select 1,8
insert tableb select 1,7
insert tableb select 1,15
insert tableb select 2,13
insert tableb select 2,14
insert tableb select 3,17
go
select
a.id,
qty=a.qty-isnull(b.qty,0)
from (select id,sum(qty) qty from tableB group by id) a
left join (select id,sum(qty) qty from tableA group by id) b
on a.id=b.id
where a.qty-b.qty<>0
/*
id qty
----------- -----------
1 -10
3 3(2 行受影响)
*/
from
(select id,sum(qty) as qty from A group by id) a,
(select id,sum(qty) as qty from B group by id) b
where a.id=b.id and a.qty-b.qty<>0
select ID,sum(qty) as sumqtya from tablea group by ID) a,
(select ID,sum(qty) as sumqtyb from tableb group by ID) b
where a.ID = b.ID
group by a.ID
having sum(sumqtya-sumqtyb) <> 0
left join B on A.id = B.id
group by A.id having sum(B.qty) - sum(A.qty) <> 0
left join B on A.id = B.id
group by A.id having sum(B.qty) - sum(A.qty) <> 0