SQL左联接仅先匹配 [英] SQL Left Join first match only

问题描述

我对具有多个联接的大量大表(行和列)进行了查询，但是其中一个表具有一些重复的数据行，这导致我的查询出现问题.由于这是来自另一个部门的只读实时供稿，因此我无法修复该数据，但是我正尝试阻止该查询中的问题.

I have a query against a large number of big tables (rows and columns) with a number of joins, however one of tables has some duplicate rows of data causing issues for my query. Since this is a read only realtime feed from another department I can't fix that data, however I am trying to prevent issues in my query from it.

鉴于此，我需要将此废话数据作为左联接添加到我的良好查询中.数据集如下:

Given that, I need to add this crap data as a left join to my good query. The data set looks like:

IDNo    FirstName   LastName    ...
-------------------------------------------
uqx     bob     smith
abc     john        willis
ABC     john        willis
aBc     john        willis
WTF     jeff        bridges
sss     bill        doe
ere     sally       abby
wtf     jeff        bridges
...

(大约2打列和10万行)

(about 2 dozen columns, and 100K rows)

我的第一个直觉是执行一次与众不同的操作，这给了我约8万行:

My first instinct was to perform a distinct gave me about 80K rows:

SELECT DISTINCT P.IDNo
FROM people P

但是当我尝试以下操作时，我将所有行取回:

But when I try the following, I get all the rows back:

SELECT DISTINCT P.*
FROM people P

OR

SELECT 
    DISTINCT(P.IDNo) AS IDNoUnq 
    ,P.FirstName
    ,P.LastName
    ...etc.    
FROM people P

然后我以为我会在所有列上执行FIRST()聚合函数，但是这也感觉不对.语法上我在这里做错了吗?

I then thought I would do a FIRST() aggregate function on all the columns, however that feels wrong too. Syntactically am I doing something wrong here?

更新: 只是要注意:这些记录是重复的，基于上面列出的ID的非键/非索引字段. ID是一个文本字段，尽管具有相同的值，但与引起问题的其他数据的大小写不同.

Update: Just wanted to note: These records are duplicates based on a non-key / non-indexed field of ID listed above. The ID is a text field which although has the same value, it is a different case than the other data causing the issue.

推荐答案

原来我做错了，我需要首先对重要列执行嵌套选择，然后进行单独选择以防止垃圾列破坏我的好数据的独特"数据.以下内容似乎已解决了该问题...但是稍后我将尝试使用完整的数据集.

Turns out I was doing it wrong, I needed to perform a nested select first of just the important columns, and do a distinct select off that to prevent trash columns of 'unique' data from corrupting my good data. The following appears to have resolved the issue... but I will try on the full dataset later.

SELECT DISTINCT P2.*
FROM (
  SELECT
      IDNo
    , FirstName
    , LastName
  FROM people P
) P2

以下是一些要求的播放数据: http://sqlfiddle.com/#!3/050e0d/3

Here is some play data as requested: http://sqlfiddle.com/#!3/050e0d/3

CREATE TABLE people
(
       [entry] int
     , [IDNo] varchar(3)
     , [FirstName] varchar(5)
     , [LastName] varchar(7)
);

INSERT INTO people
    (entry,[IDNo], [FirstName], [LastName])
VALUES
    (1,'uqx', 'bob', 'smith'),
    (2,'abc', 'john', 'willis'),
    (3,'ABC', 'john', 'willis'),
    (4,'aBc', 'john', 'willis'),
    (5,'WTF', 'jeff', 'bridges'),
    (6,'Sss', 'bill', 'doe'),
    (7,'sSs', 'bill', 'doe'),
    (8,'ssS', 'bill', 'doe'),
    (9,'ere', 'sally', 'abby'),
    (10,'wtf', 'jeff', 'bridges')
;

这篇关于SQL左联接仅先匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！