时间戳

首页 > 解决方案 > 查找此表结构的缺失记录的更便宜的方法是什么？

sql-server - 查找此表结构的缺失记录的更便宜的方法是什么？

问题描述

我有一个主数据流表，其格式类似于下面的流表。每个idtype都有一组可能的idname。

我试图找出哪个id缺少idname。我还创建了一个表，其中包含所有idtype及其idname值，如Stream Info Table所示。

我对查找这些值的解决方案很灵活，它不是一个固定的查找表，但我需要知道缺少哪个和 id 而不仅仅是缺失值的数量。

Stream Table
╔════╦═════════════════════════╦══════════╦════════╦═══════════╗
║ id ║       idtimestamp       ║  idtype  ║ idname ║  idvalue  ║
╠════╬═════════════════════════╬══════════╬════════╬═══════════╣
║  1 ║ 2020-05-15 10:49:58.773 ║ external ║ body   ║ round     ║
║  1 ║ 2020-05-15 10:49:58.773 ║ external ║ genre  ║ all       ║
║  1 ║ 2020-05-15 10:49:58.773 ║ external ║ size   ║ uniform   ║
║  2 ║ 2020-05-15 10:49:58.773 ║ internal ║ batch  ║ batchname ║
║  2 ║ 2020-05-15 10:49:58.773 ║ internal ║ link   ║ quad      ║
╚════╩═════════════════════════╩══════════╩════════╩═══════════╝

Stream Info Table
╔══════════╦════════╗
║  idtype  ║ idname ║
╠══════════╬════════╣
║ external ║ blue   ║
║ external ║ body   ║
║ external ║ genre  ║
║ external ║ size   ║
║ internal ║ batch  ║
║ internal ║ link   ║
║ internal ║ whyte  ║
╚══════════╩════════╝

对于给定的数据集，我期望输出这些数据：

╔════╦═════════════════════════╦══════════╦════════╦
║ id ║       idtimestamp       ║  idtype  ║ idname ║  
╠════╬═════════════════════════╬══════════╬════════╬
║  1 ║ 2020-05-14 20:48:50.540 ║ external ║ blue   ║ 
║  2 ║ 2020-05-14 20:48:50.540 ║ internal ║ whyte  ║  
╚════╩═════════════════════════╩══════════╩════════╩

示例数据集：

drop table if exists #stream_data
create table #stream_data (id int, idtimestamp datetime, idtype varchar(30), idname varchar(60), idvalue varchar(100))
insert into #stream_data
select '1' , getdate() , 'external' , 'body' , 'round' union
select '1' , getdate() , 'external' , 'size' , 'uniform' union
select '1' , getdate() , 'external' , 'genre' , 'all' union
select '2' , getdate() , 'internal' , 'batch' , 'batchname' union
select '2' , getdate() , 'internal' , 'link' , 'quad' 


drop table if exists #stream_info 
create table #stream_info (idtype varchar(30), idname varchar(60))
insert into #stream_info
select 'external' , 'body' union
select 'external' , 'size' union
select 'external' , 'genre' union
select 'external' , 'blue' union
select 'internal' , 'batch' union 
select 'internal' , 'link' union
select 'internal' , 'whyte'

标签： sql-servertsqljoinlookupmissing-data

解决方案

DECLARE     @StreamData TABLE   (id int, idtimestamp datetime, idtype varchar(30), idname varchar(60), idvalue varchar(100))
INSERT INTO @StreamData VALUES
            ('1' , getdate() , 'external' , 'body'  , 'round'       )
        ,   ('1' , getdate() , 'external' , 'genre' , 'all'         )
        ,   ('1' , getdate() , 'external' , 'size'  , 'uniform'     )
        ,   ('2' , getdate() , 'internal' , 'batch' , 'batchname'   )
        ,   ('2' , getdate() , 'internal' , 'link'  , 'quad'        )

DECLARE     @StreamInfo TABLE   (idtype varchar(30), idname varchar(60))
INSERT INTO @StreamInfo VALUES
            ('external' , 'blue'    )
        ,   ('external' , 'body'    )
        ,   ('external' , 'genre'   )
        ,   ('external' , 'size'    )
        ,   ('internal' , 'batch'   )
        ,   ('internal' , 'link'    )
        ,   ('internal' , 'whyte'    )

-- With LEFT JOIN you are getting all of the rows of the StreamInfo Table
-- With the WHERE Clause you are just getting those without a matching row in the StreamData Table
SELECT  SI.*
FROM           @StreamInfo SI
    LEFT JOIN  @StreamData SD   ON  SD.idtype   =   SI.idtype
                                AND SI.idname   =   SD.idname
WHERE           SD.idname IS NULL

推荐阅读