表A
no order_no batch_no job_no item1 2009000 20090101 090101001 10000000
2 2009000 20090101 090101002 10000001
3 2009000 20090101 090102003 10000002
4 2009002 20090103 090102002 10000001
5 2009003 20090104 090102003 10000001
6 2009004 20090105 090102004 10000001
7 2009005 20090106 090102005 10000005表B
no order_no batch_no job_no item1 2009000 20090101 090101001 10000000
2 2009000 20090101 090101002 10000001
3 2009000 20090101 090102001 10000000
4 2009002 20090103 090102002 10000001
5 2009003 20090104 090102003 10000001
6 2009004 20090105 090102004 10000001
7 2009005 20090106 090102005 10000005
表B是有表A根据order_no输入batch_no手工找出job_no生成的,理论上和表A是相同的。
但有时手工输入batch_no后会选错job_no(因为一个order_no下面可能存在多个job_no,详见表A中order_no为2009000的记录),表B的记录就和表A 不一样了。
问题:如何通过查询语句找出和表A不同的记录。
谢谢大家了!
我试过:
select *
from B
where not exists(select * from A where order_no=b.order_no and batch_no=b.batch_no and job_no=b.job_no and item=b.item)
没有查询出不同记录。
no order_no batch_no job_no item1 2009000 20090101 090101001 10000000
2 2009000 20090101 090101002 10000001
3 2009000 20090101 090102003 10000002
4 2009002 20090103 090102002 10000001
5 2009003 20090104 090102003 10000001
6 2009004 20090105 090102004 10000001
7 2009005 20090106 090102005 10000005表B
no order_no batch_no job_no item1 2009000 20090101 090101001 10000000
2 2009000 20090101 090101002 10000001
3 2009000 20090101 090102001 10000000
4 2009002 20090103 090102002 10000001
5 2009003 20090104 090102003 10000001
6 2009004 20090105 090102004 10000001
7 2009005 20090106 090102005 10000005
表B是有表A根据order_no输入batch_no手工找出job_no生成的,理论上和表A是相同的。
但有时手工输入batch_no后会选错job_no(因为一个order_no下面可能存在多个job_no,详见表A中order_no为2009000的记录),表B的记录就和表A 不一样了。
问题:如何通过查询语句找出和表A不同的记录。
谢谢大家了!
我试过:
select *
from B
where not exists(select * from A where order_no=b.order_no and batch_no=b.batch_no and job_no=b.job_no and item=b.item)
没有查询出不同记录。
no order_no batch_no job_no item 1 P2009000D 20090101 090101001 10000000
2 P2009000D 20090101 090101002 10000001
3 P2009000D 20090101 090102001 10000000
4 J2009002A 20090103 090102002 10000001
5 P2009003D 20090104 090102003 10000001
6 P2009004 20090105 090102004 10000001
7 P2009005 20090106 090102005 10000005
这应该怎么写呢?
if object_id('[A]') is not null drop table [A]
go
create table [A]([no] int,[order_no] int,[batch_no] datetime,[job_no] varchar(9),[item] int)
insert [A]
select 1,2009000,'20090101','090101001',10000000 union all
select 2,2009000,'20090101','090101002',10000001 union all
select 3,2009000,'20090101','090102003',10000002 union all
select 4,2009002,'20090103','090102002',10000001 union all
select 5,2009003,'20090104','090102003',10000001 union all
select 6,2009004,'20090105','090102004',10000001 union all
select 7,2009005,'20090106','090102005',10000005
if object_id('[B]') is not null drop table [B]
go
create table [B]([no] int,[order_no] varchar(9),[batch_no] datetime,[job_no] varchar(9),[item] int)
insert [B]
select 1,'P2009000D','20090101','090101001',10000000 union all
select 2,'P2009000D','20090101','090101002',10000001 union all
select 3,'P2009000D','20090101','090102001',10000000 union all
select 4,'J2009002A','20090103','090102002',10000001 union all
select 5,'P2009003D','20090104','090102003',10000001 union all
select 6,'P2009004D','20090105','090102004',10000001 union all
select 7,'P2009005D','20090106','090102005',10000005
---查询---
select *
from B
where not exists(select * from A where order_no=cast(substring(b.order_no,PATINDEX('%[0-9]%',b.order_no),7) as int) and batch_no=b.batch_no and job_no=b.job_no and item=b.item)---结果---
no order_no batch_no job_no item
----------- --------- ------------------------------------------------------ --------- -----------
3 P2009000D 2009-01-01 00:00:00.000 090102001 10000000(所影响的行数为 1 行)
这样?
--> -->
declare @A table([no] int,[order_no] nvarchar(9),[batch_no] Datetime,[job_no] nvarchar(9),[item] int)
Insert @A
select 1,2009000,'20090101',N'090101001',10000000 union all
select 2,2009000,'20090101',N'090101002',10000001 union all
select 3,2009000,'20090101',N'090102003',10000002 union all
select 4,2009002,'20090103',N'090102002',10000001 union all
select 5,2009003,'20090104',N'090102003',10000001 union all
select 6,2009004,'20090105',N'090102004',10000001 union all
select 7,2009005,'20090106',N'090102005',10000005
--> -->
declare @B table([no] int,[order_no] nvarchar(9),[batch_no] Datetime,[job_no] nvarchar(9),[item] int)
Insert @B
select 1,N'P2009000D','20090101',N'090101001',10000000 union all
select 2,N'P2009000D','20090101',N'090101002',10000001 union all
select 3,N'P2009000D','20090101',N'090102001',10000000 union all
select 4,N'J2009002A','20090103',N'090102002',10000001 union all
select 5,N'P2009003D','20090104',N'090102003',10000001 union all
select 6,N'P2009004D','20090105',N'090102004',10000001 union all
select 7,N'P2009005D','20090106',N'090102005',10000005
select * from @B b
where not exists
(select 1 from @A where B.order_no like '%'+Order_no+'%' and batch_no=B.batch_no and job_no=B.job_no) /*
no order_no batch_no job_no item
----------- --------- ----------------------- --------- -----------
3 P2009000D 2009-01-01 00:00:00.000 090102001 10000000
*/