补充
我有两张表 表1
BankAccount有关的列如下ID(主键,自增) Account(收入)
1 10000
2 10000
3 10000
4 20000
5 25000
6 35000
7 40000
8 50000
表2:CompAccount有关的列如下ID(主键,自增) Account(收入)
1 10000
2 10000
3 20000
4 20000
5 20000
6 30000
7 40000
8 80000现在我要查询 1:在BankAccount单里Account存在,而在CompAccount单里Account没有存在的数据
如BankAccount ID 为:5,6,8 的Account为25000,35000,50000
这几张单的Account在CompAccount里并不存在,符合条件。
但现在有个问题,Account为10000的单 BankAccount里有3条,而CompAccount里只有两条
按照ID顺序 ID为3的单在CompAccount里也不存在所以查询结果应该是 3,5,6,8我该如何才能查询出正确结果?请教,谢谢
我有两张表 表1
BankAccount有关的列如下ID(主键,自增) Account(收入)
1 10000
2 10000
3 10000
4 20000
5 25000
6 35000
7 40000
8 50000
表2:CompAccount有关的列如下ID(主键,自增) Account(收入)
1 10000
2 10000
3 20000
4 20000
5 20000
6 30000
7 40000
8 80000现在我要查询 1:在BankAccount单里Account存在,而在CompAccount单里Account没有存在的数据
如BankAccount ID 为:5,6,8 的Account为25000,35000,50000
这几张单的Account在CompAccount里并不存在,符合条件。
但现在有个问题,Account为10000的单 BankAccount里有3条,而CompAccount里只有两条
按照ID顺序 ID为3的单在CompAccount里也不存在所以查询结果应该是 3,5,6,8我该如何才能查询出正确结果?请教,谢谢
如BankAccount ID 为:5,6,8 的Account为25000,35000,50000 select A.* from BankAccount a where cast(id as varchar) + ',' + cast(Account as varchar) not in
(select cast(id as varchar) + ',' + cast(Account as varchar) from CompAccount)
insert into tb1 values(1, 10000 )
insert into tb1 values(2, 10000 )
insert into tb1 values(3, 10000 )
insert into tb1 values(4, 20000 )
insert into tb1 values(5, 25000 )
insert into tb1 values(6, 35000 )
insert into tb1 values(7, 40000 )
insert into tb1 values(8, 50000 )
create table tb2(ID int,Account int)
insert into tb2 values(1, 10000 )
insert into tb2 values(2, 10000 )
insert into tb2 values(3, 20000 )
insert into tb2 values(4, 20000 )
insert into tb2 values(5, 20000 )
insert into tb2 values(6, 30000 )
insert into tb2 values(7, 40000 )
insert into tb2 values(8, 80000 )
go
select A.* from tb1 a where cast(id as varchar) + ', ' + cast(Account as varchar) not in
(select cast(id as varchar) + ', ' + cast(Account as varchar) from tb2)
drop table tb1,tb2/*
ID Account
----------- -----------
3 10000
5 25000
6 35000
8 50000(所影响的行数为 4 行)
*/
create table tb1(ID int,Account int)
insert into tb1 values(1, 10000 )
insert into tb1 values(2, 10000 )
insert into tb1 values(3, 10000 )
insert into tb1 values(4, 20000 )
insert into tb1 values(5, 25000 )
insert into tb1 values(6, 35000 )
insert into tb1 values(7, 40000 )
insert into tb1 values(8, 50000 )
create table tb2(ID int,Account int)
insert into tb2 values(1, 10000 )
insert into tb2 values(2, 10000 )
insert into tb2 values(3, 20000 )
insert into tb2 values(4, 20000 )
insert into tb2 values(5, 20000 )
insert into tb2 values(6, 30000 )
insert into tb2 values(7, 40000 )
insert into tb2 values(8, 80000 )
go
select px = identity(int ,1,1) , * into t1 from tb1 order by account,id
select px = identity(int ,1,1) , * into t2 from tb2 order by account,idselect A.id,account from t1 a where cast(px as varchar) + ', ' + cast(Account as varchar) not in
(select cast(px as varchar) + ', ' + cast(Account as varchar) from t2)
drop table tb1,tb2,t1,t2/*
ID Account
----------- -----------
3 10000
5 25000
6 35000
8 50000(所影响的行数为 4 行)
*/
insert into tb1 values(11, 10000 )
insert into tb1 values(12, 10000 )
insert into tb1 values(13, 10000 )
insert into tb1 values(14, 20000 )
insert into tb1 values(15, 25000 )
insert into tb1 values(16, 35000 )
insert into tb1 values(17, 40000 )
insert into tb1 values(18, 50000 )
create table tb2(ID int,Account int)
insert into tb2 values(1, 10000 )
insert into tb2 values(2, 10000 )
insert into tb2 values(3, 20000 )
insert into tb2 values(4, 20000 )
insert into tb2 values(5, 20000 )
insert into tb2 values(6, 30000 )
insert into tb2 values(7, 40000 )
insert into tb2 values(8, 80000 ) select px=identity(int, 1,1),* into #t1 from tb1 order by account,id
select px=identity(int ,1,1),* into #t2 from tb2 order by account,id
select a.id,a.account from #t1 a where not exists(select * from #t2 b where a.px=b.px and a.account=b.account)
drop table tb1,tb2,#t1,#t2