查询以选择未出现在数据库中的记录 [英] Query to select records that do not appear in DB

问题描述

我试图将丢失的数据添加到数据库中，因此试图通过查询来收集这些丢失的数据。

I am trying to add missing data to the DB and am therefore trying to collect this missing data through queries.

在我的Excel工作表中，我有两列： col A 填充有组件代码，而 col J 保留其各自的资产类型代码。

In my excel sheet I have two columns: col A is populated with component codes, while col J holds its respective asset type codes.

我想在一个查询中找到所有不存在的组件资产类型组合。

I want to find all non-existing component-asset type combinations in one query.

当前，我像下面的示例那样遍历excel表：

Currently, I am looping through the excel sheet like in the following example:

arr = Range("B3:J5000")
For i = 1 To 5000-2
    Set rs = cnn.Execute("Select Top 1 AT.Code From astComponents C Join astAssetTypes AT ON AT.Id = C.AssetTypeId Where C.Code = '" & arr(i, 1) & "' And AT.Code = '" & arr(i, 9) & "'")
    If rs.EOF Then
        'Missing data was found
    End If
Next i

但这不是令我满意。我想最后得到一个记录集，其中缺少组件-资产类型组合。这将大大提高程序速度（速度提高了120倍）

This is however not to my satisfaction. I want to end up with a recordset with the component-asset type combinations that are missing. This would significantly improve the speed of my program (120 times as fast)

我尝试使用 Except ，但这导致没有我期望得到的记录：

I tried to use Except but this resulted in no records where I did expect them:

Select Distinct C.Code, AT.Code 
FROM dbo.astComponents C 
JOIN dbo.astAssetTypes AT ON AT.Id = C.AssetTypeId 

WHERE (C.Code= '0738.D100' AND AT.Code = '0738.M00_03.03') Or (C.Code= '0738.D101' AND AT.Code = '0738.L00_04.04')

Except Select C.Code, AT.Code From astComponents C Join astAssetTypes AT ON AT.Id = C.AssetTypeId

EDIT

如前所述，期望的输出应该是缺少的组件-资产类型组合。示例：

EDIT
As already mentioned, the desired output should be the missing component-asset type combinations. Example:

Component      Asset Type
0738.D101      0990.D10_03.03
0150.C101      0738.L00_04.04

SQL提琴

任务：检查以下组件-资产类型组合是否存在提琴数据库。否则，请将这些组合添加到输出中。

SQL-Fiddle
Task: check whether the component-asset type combinations below exist in the fiddle database. If not, add these combinations to the output.

Component      Asset Type
0738.D100      0990.D10_03.03
0738.D101      0990.D10_03.03
0150.C101      0738.L00_04.04
0738.L004      0738.M00_03.03
0990.D103      0738.M00_03.03

推荐答案

不是最终答案，因为下面的方法很慢

在这种方法中，我使用了一个循环，该循环填充了一个变量，该变量有助于使用所有应检查的组件-资产类型组合来填充临时表。然后，我使用CTE将数据库中的值与temp表进行比较。

In this method I use a loop that populates a variable which helps populating a temp table with all component-asset type combinations which should be checked. I then compare the values in the database with the temp table using a CTE.

创建临时表并执行第二个查询持续5秒钟（ #Temp 中有1250条记录），我认为它太慢了

Creating the temp table and executing the second query lasts for 5 seconds (with 1250 records in #Temp), which I deem to be too slow

arr = Range("B3:J" & LRow)
For i = 1 To LRow - 2
    ComponentCodeTemp = ComponentCodeTemp & "Insert Into #Temp (Component, AssetType) Values ('" & arr(i, 1) & "','" & arr(i, 9) & "');"
Next i

'Query
Set rs = getdata("Create Table #Temp(Component nvarchar(50) Collate Latin1_General_BIN, AssetType nvarchar(50) Collate Latin1_General_BIN) " & ComponentCodeTemp & _
    "; With Compare As (Select T.* From #Temp T), DBD As (Select Distinct Compare.Component,  Compare.AssetType From astComponents C Join astAssetTypes AT ON AT.Id = C.AssetTypeId Right Join #Temp Compare ON Compare.AssetType = AT.Code And Compare.Component = C.Code Where C.Code is null) " & _
    "Select * From DBD")

SQL查询的更好标记：

A better markup for the SQL query:

WITH 
    Compare AS (
                Select T.* FROM #Temp T
               ),
    DBD AS (
            Select Distinct Compare.Component,  Compare.AssetType From astComponents C
            Join astAssetTypes AT ON AT.Id = C.AssetTypeId
            Right Join #Temp Compare ON Compare.AssetType = AT.Code And Compare.Component = C.Code
            Where C.Code is null
            )
Select * From DBD

这篇关于查询以选择未出现在数据库中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！