表1
id code stock kc
1 11 3 4
2 22 4 19
3 33 5 5
4 44 7 2
5 55 9 7
表二
id num
4 3
2 6
1 1
2 9
5 2
5 5
如何得到所有的 stock+num<>kc的记录,就像id code stock num kc
4 44 7 0 2
5 55 9 7 7
id code stock kc
1 11 3 4
2 22 4 19
3 33 5 5
4 44 7 2
5 55 9 7
表二
id num
4 3
2 6
1 1
2 9
5 2
5 5
如何得到所有的 stock+num<>kc的记录,就像id code stock num kc
4 44 7 0 2
5 55 9 7 7
4 3
这不是有3个吗
select a.id,a.code,a.stock,b.num
from tablea a
left join (select id,sum(num) as num from tableb group by id) b
on a.id = b.id and a.kc <> a.stock + b.num
left join
(
select id,sum(num)as num from t2 group by id
)b
on a.id=b.id
where a.stock+b.num<>a.kc
create table A(id int, code int, stock int, kc int)
insert into A values(1, 11 , 3 , 4 )
insert into A values(2, 22 , 4 , 19)
insert into A values(3, 33 , 5 , 5 )
insert into A values(4, 44 , 7 , 2 )
insert into A values(5, 55 , 9 , 7 )
create table B(id int, num int)
insert into B values(4, 3 )
insert into B values(2, 6 )
insert into B values(1, 1 )
insert into B values(2, 9 )
insert into B values(5, 2 )
insert into B values(5, 5 )
goselect a.* from a,b where a.id = b.id and a.stock + b.num <> a.kc
/*
id code stock kc
----------- ----------- ----------- -----------
4 44 7 2
2 22 4 19
2 22 4 19
5 55 9 7
5 55 9 7(所影响的行数为 5 行)
*/select a.* from a,(select id , num = sum(num) from b group by id) t where a.id = t.id and a.stock + t.num <> a.kc
/*
id code stock kc
----------- ----------- ----------- -----------
4 44 7 2
5 55 9 7(所影响的行数为 2 行)
*/drop table A,B
create table A(id int, code int, stock int, kc int)
insert into A values(1, 11 , 3 , 4 )
insert into A values(2, 22 , 4 , 19)
insert into A values(3, 33 , 5 , 5 )
insert into A values(4, 44 , 7 , 2 )
insert into A values(5, 55 , 9 , 7 )
create table B(id int, num int)
insert into B values(4, 3 )
insert into B values(2, 6 )
insert into B values(1, 1 )
insert into B values(2, 9 )
insert into B values(5, 2 )
insert into B values(5, 5 )
goselect a.id , a.code , a.stock , t.num , a.kc from a,(select id , num = sum(num) from b group by id) t where a.id = t.id and a.stock + t.num <> a.kc
/*
id code stock num kc
----------- ----------- ----------- ----------- -----------
4 44 7 3 2
5 55 9 7 7(所影响的行数为 2 行)
*/drop table A,B
insert into a select 1,11,3,4
union all
select 2,22,4,19
union all
select 3,33,5,5
union all
select 4,44,7,2
union all
select 5,55,9,7create table b([id] int,num int)
insert into b select 4,3
union all
select 2,6
union all
select 1,1
union all
select 2,9
union all
select 5,2
union all
select 5,5select * from a
select * from bselect a.[id],a.code,a.stock,b.num,a.kc
from a
left join (select [id],sum(num) num from b group by [id] )b
on a.[id] = b.[id]
--借楼上数据一用
create table A(id int, code int, stock int, kc int)
insert into A values(1, 11 , 3 , 4 )
insert into A values(2, 22 , 4 , 19)
insert into A values(3, 33 , 5 , 5 )
insert into A values(4, 44 , 7 , 2 )
insert into A values(5, 55 , 9 , 7 )
create table B(id int, num int)
insert into B values(4, 3 )
insert into B values(2, 6 )
insert into B values(1, 1 )
insert into B values(2, 9 )
insert into B values(5, 2 )
insert into B values(5, 5 )
select a.* from A a
left join
(
select id,sum(num)as num from B group by id
)b
on a.id=b.id
where a.stock+b.num<>a.kc/*
id code stock kc
----------- ----------- ----------- -----------
4 44 7 2
5 55 9 7(所影响的行数为 2 行)*/
LZ可能不是要ID 为4,5的,只是举个例子而己。
(
select id,sum(num) as num from 表2 group by ID
) as b on a.id=b.id where a.stock+b.num<>a.kC
表1
id code stock kc
1 11 3 4
2 22 4 19
3 33 4 5
4 44 7 2
5 55 9 7
表二
id num state
4 3 0
2 6 1
1 1 1
2 9 1
5 2 1
5 5 0
如何得到所有的 stock+num(state=0时候num算0) <> kc的记录,就像 id code stock num kc 3 33 4 0 5
4 44 7 0 2
5 55 9 2 7
[code=SQL]create table A(id int, code int, stock int, kc int)
insert into A values(1, 11 , 3 , 4 )
insert into A values(2, 22 , 4 , 19)
insert into A values(3, 33 , 4 , 5 )
insert into A values(4, 44 , 7 , 2 )
insert into A values(5, 55 , 9 , 7 )
create table B(id int, num int,state int)
insert into B values(4, 3 ,0 )
insert into B values(2, 6 ,1)
insert into B values(1, 1 ,1)
insert into B values(2, 9 ,1)
insert into B values(5, 2 ,1)
insert into B values(5, 5 ,0)
select a.id,a.code,a.stock,isnull(b.num,0)as num,a.kc from A a
left join
(
select id,sum(case when state=0 then 0 else num end)as num from B group by id
)b
on a.id=b.id
where a.stock+isnull(b.num,0)<>a.kc/*
id code stock num kc
----------- ----------- ----------- ----------- -----------
3 33 4 0 5
4 44 7 0 2
5 55 9 2 7(所影响的行数为 3 行)
*/[/code]
insert into A values(1, 11 , 3 , 4 )
insert into A values(2, 22 , 4 , 19)
insert into A values(3, 33 , 4 , 5 )
insert into A values(4, 44 , 7 , 2 )
insert into A values(5, 55 , 9 , 7 )
create table B(id int, num int,state int)
insert into B values(4, 3 ,0 )
insert into B values(2, 6 ,1)
insert into B values(1, 1 ,1)
insert into B values(2, 9 ,1)
insert into B values(5, 2 ,1)
insert into B values(5, 5 ,0)select a.id,a.code,a.stock,a.kc,bb.num
from a
left join (select id,sum(case when state =0 then 0 else num end) as num from b group by id) bb
on a.id = bb.id
where a.kc <> a.stock + isnull(bb.num,0)
drop table a,b/*id code stock kc num
----------- ----------- ----------- ----------- -----------
3 33 4 5 NULL
4 44 7 2 0
5 55 9 7 2(所影响的行数为 3 行)
*/[/code]