如何检索 SQL Server 中一个表的任何字段中不存在的数据? [英] How to retrieve data which is not present in any field of one table in SQL Server?
问题描述
我想从 Table1
例如我有两张桌子
1.#tempLastSold
2.项目关系
#tempLastSold 记录
ID ExtendedDescription StoreID160641 FA012 1400//ID 存在于 ItemRelation 的 ID 中160641 FA012 2001//ID 存在于 ItemRelation 的 ID 中160632 FA003 1400//ID存在于ItemRelation的ChildID3中160632 FA003 2001//ID 存在于 ItemRelation 的 ChildID3 中156824 25298 2001//ItemRelation的ID中存在ID158430 161-18-132 1302//ID存在于ItemRelation的ChildID2中
<块引用>
注意 #tempLastSold 的 ID 可以出现在 ItemRelation 的任何字段中(可以是 ID,ChildID1,ChildID2,ChildID3)表
ItemRelation 记录
ID ChildID1 ChildID2 ChildID3160641 160642 空 空160631 160634 160633 160632156824 空 空 空158433 158431 158430 空1 1A 1B 1C2 2A 2B 2C2 3A 3B 3C
我想显示 #tempLastSold
中不存在的 ItemRelation 的记录#tempLastSold 拥有数百万条记录,
ItemRelation 有 3 万条记录
我试过这个代码
选择ir.ID,ir.ItemLookupCode,ir.ChildID1,ir.ChildItemLookupCode1,ir.ChildID2,ir.ChildItemLookupCode2,ir.ChildID3,ir.ChildItemLookupCode来自 [HQMatajer].[dbo].[ItemRelation] ir左加入#tempLastSold tLs on tLs.ID != ir.id在 tLs1.ID 上左加入 #tempLastSold tLs1 !=ir.ChildID1在 tLs2.ID 上左加入 #tempLastSold tLs2 !=ir.ChildID2在 tLs3.ID 上左加入 #tempLastSold tLs3 != ir.ChildID3
但它检索了超过 60,000 条记录.
使用单个 left join
:
选择ir.ID,ir.ChildID1,ir.ChildID2,ir.ChildID3来自 [dbo].[ItemRelation] ir左加入#tempLastSold tLs在 tLs.ID = ir.id或 tLs.ID = try_convert(int,ChildID1)或 tLs.ID = try_convert(int,ChildID2)或 tLs.ID = try_convert(int,ChildID3)其中 tls.id 为空;
rextester 演示:http://rextester.com/SBA83066>
返回:
+----+----------+----------+----------+|身份证 |儿童ID1 |ChildID2 |ChildID3 |+----+----------+----------+---------+|1 |1A |1B |1C ||2 |2A |2B |2C ||2 |3A |3B |3C |+----+----------+----------+---------+
使用 not exists()
代替,结果相同
选择ir.ID,ir.ChildID1,ir.ChildID2,ir.ChildID3来自 [dbo].[ItemRelation] ir哪里不存在(选择 1来自#tempLastSold tLs其中 tLs.ID = ir.id或 tLs.ID = try_convert(int,ChildID1)或 tLs.ID = try_convert(int,ChildID2)或 tLs.ID = try_convert(int,ChildID3))
I want to retrieve a data which is not present in any field from Table1
For example I have two table
1. #tempLastSold
2. ItemRelation
#tempLastSold records
ID ExtendedDescription StoreID
160641 FA012 1400 //ID present in ID of ItemRelation
160641 FA012 2001 //ID present in ID of ItemRelation
160632 FA003 1400 //ID present in ChildID3 of ItemRelation
160632 FA003 2001 //ID present in ChildID3 of ItemRelation
156824 25298 2001 //ID present in ID of ItemRelation
158430 161-18-132 1302 //ID present in ChildID2 of ItemRelation
Note Id of #tempLastSold may present in any field (may be ID,ChildID1,ChildID2,ChildID3) of ItemRelation Table
ItemRelation records
ID ChildID1 ChildID2 ChildID3
160641 160642 Null Null
160631 160634 160633 160632
156824 Null Null Null
158433 158431 158430 Null
1 1A 1B 1C
2 2A 2B 2C
2 3A 3B 3C
I wants to display the records of ItemRelation which is not present in #tempLastSold
#tempLastSold having millions of records,
ItemRelation having 30 thousands recorsds
I tried this code
select ir.ID,
ir.ItemLookupCode,
ir.ChildID1,
ir.ChildItemLookupCode1,
ir.ChildID2,
ir.ChildItemLookupCode2,
ir.ChildID3,
ir.ChildItemLookupCode
FROM [HQMatajer].[dbo].[ItemRelation] ir
left join #tempLastSold tLs on tLs.ID != ir.id
left join #tempLastSold tLs1 on tLs1.ID !=ir.ChildID1
left join #tempLastSold tLs2 on tLs2.ID !=ir.ChildID2
left join #tempLastSold tLs3 on tLs3.ID != ir.ChildID3
But it retrieved the records more than 60,000.
Using a single left join
:
select ir.ID,
ir.ChildID1,
ir.ChildID2,
ir.ChildID3
from [dbo].[ItemRelation] ir
left join #tempLastSold tLs
on tLs.ID = ir.id
or tLs.ID = try_convert(int,ChildID1)
or tLs.ID = try_convert(int,ChildID2)
or tLs.ID = try_convert(int,ChildID3)
where tls.id is null;
rextester demo: http://rextester.com/SBA83066
returns:
+----+----------+----------+----------+
| ID | ChildID1 | ChildID2 | ChildID3 |
+----+----------+----------+----------+
| 1 | 1A | 1B | 1C |
| 2 | 2A | 2B | 2C |
| 2 | 3A | 3B | 3C |
+----+----------+----------+----------+
using not exists()
instead, same result
select ir.ID,
ir.ChildID1,
ir.ChildID2,
ir.ChildID3
from [dbo].[ItemRelation] ir
where not exists (
select 1
from #tempLastSold tLs
where tLs.ID = ir.id
or tLs.ID = try_convert(int,ChildID1)
or tLs.ID = try_convert(int,ChildID2)
or tLs.ID = try_convert(int,ChildID3)
)
这篇关于如何检索 SQL Server 中一个表的任何字段中不存在的数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!