有条件加入对多个域在同一个表

Question

我有，你有两个表如下两个表的情况下，无论是为person_id或organisation_id填充

TABLE_1

email_id, person_id, organisation_id, email_address Usage 
1   NULL  12    [email protected] WorkEmail 
2   12   NULL    [email protected] WorkEmail 
3   13   NULL    [email protected] WorkEmail 
4   14   NULL    [email protected] WorkEmail 
5   NULL  13    [email protected] WorkEmail 
6   14   NULL    [email protected] PersonalEmail 
7   NULL  14    [email protected] PersonalEmail 
8   13   NULL    [email protected] WorkEmail 

TABLE_2

registration_id, person_id, organisation_id, name, registration_Date 
1    NULL  12    ORG12 10/05/2013 
2    12   NULL    P12 10/05/2013 
3    13   NULL    P13 10/05/2013 
4    14   NULL    P14 10/05/2013 
5    NULL  13    O13 10/05/2013 
6    NULL  14    O14 10/05/2013 

我需要一个选择statment，这将使我的每个登记备案的workemail;其中，登记备案有一个以上的工作电子邮件地址 那么第一个记录应（表1 EMAIL_ID 3和8例如记录）进行选择：

registration_id, person_id, organisation_id, name, email address 
1    NULL  12   ORG12 [email protected] 
2    12   NULL   P12 [email protected] 
3    13   NULL   P13 [email protected] 
4    14   NULL   P14 [email protected] 
5    NULL  13   O13 [email protected] 
6    NULL  14   O14 NULL 

我曾尝试做以下，但如果不是很确定这是最有效的方式;此外，它并不十分给我我需要什么：

SELECT t1.registration_id, t1.person_id, t1.organisation_id, t1.name, t2.email_Address 
FROM table2 t1 
LEFT JOIN table1 ON t2.person_id = t1.person_id 
    OR 
    t2.organisation_id = t1.organisation_id 

Answer 1

修订答

/* setup */ 
create table Table_1 
(
     email_id bigint not null --identity(1,1) 
    , person_id bigint 
    , organisation_id bigint 
    , email_address nvarchar(256) not null 
    , Usage nvarchar(16) not null 
) 
insert Table_1 (email_id, person_id, organisation_id, email_address, Usage) 
     select 1   ,NULL  ,12    ,'[email protected]' ,'WorkEmail' 
union select 2   ,12   ,NULL    ,'[email protected]' ,'WorkEmail' 
union select 3   ,13   ,NULL    ,'[email protected]' ,'WorkEmail' 
union select 4   ,14   ,NULL    ,'[email protected]' ,'WorkEmail' 
union select 5   ,NULL  ,13    ,'[email protected]' ,'WorkEmail' 
union select 6   ,14   ,NULL    ,'[email protected]' ,'PersonalEmail' 
union select 7   ,NULL  ,14    ,'[email protected]' ,'PersonalEmail' 
union select 8   ,13   ,NULL    ,'[email protected]' ,'WorkEmail' 

create table Table_2 
(
     registration_id bigint not null --identity(1,1) 
    , person_id bigint 
    , organisation_id bigint 
    , name nvarchar(32) not null 
    , registration_Date date not null 
) 
insert Table_2 (registration_id, person_id, organisation_id, name, registration_Date) 
     select 1    ,NULL  ,12    ,'ORG12' ,'10/05/2013' 
union select 2    ,12   ,NULL    ,'P12' ,'10/05/2013' 
union select 3    ,13   ,NULL    ,'P13' ,'10/05/2013' 
union select 4    ,14   ,NULL    ,'P14' ,'10/05/2013' 
union select 5    ,NULL  ,13    ,'O13' ,'10/05/2013' 
union select 6    ,NULL  ,14    ,'O14' ,'10/05/2013' 


/* get the results */ 
SELECT t2.registration_id, t2.person_id, t2.organisation_id, t2.name, t1.email_Address 
FROM table_2 t2 
left outer join 
(
    select person_id, organisation_id, email_address 
    from Table_1 a 
    inner join 
    (
     select MIN(email_id) email_id 
     from Table_1 
     where Usage = 'WorkEmail'  
     group by person_id, organisation_id 
    ) b 
    on a.email_id = b.email_id  
) t1 
    ON t2.person_id = t1.person_id 
    OR t2.organisation_id = t1.organisation_id 

原来的答案

我认为这是你以后：

select x.registration_id, x.person_id, x.organisation_id, x.name, x.email_Address 
from 
(
    SELECT t2.registration_id, t2.person_id, t2.organisation_id, t2.name, t1.email_Address, t1.usage 
    , row_number() over (partition by t2.registration_id, t1.usage order by t1.email_id) r 
    FROM table_2 t2 
    LEFT JOIN table_1 t1 
     ON t2.person_id = t1.person_id 
     OR t2.organisation_id = t1.organisation_id 
) x 
where (x.r = 1 and x.usage = 'WorkEmail') --limit to the first email address if there are multiple work email matches for the same registration (table2) record 
or x.usage <> 'WorkEmail' --if it's not work email, don't limit the number 

Answer 2

PS。添加第二个答案以涵盖另一点。不要在任何地方都要有Organisation_ID和Person_ID来保存两者的详细信息，请创建一个名为Party的表格，为每个组织和个人提供唯一的ID - 然后将其与组织/人员记录相关联。现在，您可以简单地使用任意表格中的任何一个表格，您希望能够将人员ID和组织标识关联起来。

这是从OO一个共同的模式（面向对象的）世界称为政党格局或甲方模型（谷歌这些条款，以了解更多）。

看一看/下面的示例代码以获得的是如何工作的一个更好的主意/任何问题，请给我留言：

/* setup */ 
if OBJECT_ID('Registrations') is not null drop table Registrations 
if OBJECT_ID('PartyContact') is not null drop table PartyContact 
if OBJECT_ID('ContactType') is not null drop table ContactType 
if OBJECT_ID('Organisation') is not null drop table Organisation 
if OBJECT_ID('Person') is not null drop table Person 
if OBJECT_ID('Party') is not null drop table Party 

go 
create table ContactType 
(
    contactType_id int not null identity(1,1) constraint PK_ContactType primary key clustered 
    , name nvarchar(16) not null constraint UK_ContactType_Name unique 
) 
go 
set identity_insert ContactType on 
insert ContactType (contactType_id, name) 
     select 1, 'WorkEmail' 
union select 2, 'PersonalEmail' 
union select 3, 'Mobile/Cell' 
set identity_insert ContactType off 
go 

create table Party 
(
    party_id bigint not null identity(1,1) constraint PK_Party primary key clustered 
    , name nvarchar(256) not null --this is duplicating the name on the Person/Organisation tables; normally this denormalisation would be bad practice, but here it assists in making data available in the table from which it will be referenced 
    --any other attributes which you want to be common to all parties 
) 
go 
set identity_insert Party on 
insert Party (party_id, name) 
     select 12, 'Rob Ottow' 
union select 13, 'Ann Droid' 
union select 14, 'Si Bermann' 
union select 112, 'Global Mega Org' 
union select 113, 'GeoTech Solutions' 
union select 114, 'Think Ink inc.' 
set identity_insert Party off 
go 

create table Person 
(
    person_id bigint not null identity(1,1) constraint PK_Person primary key clustered 
    , name nvarchar(256) not null 
    , party_id bigint not null constraint FK_Person_PartyId references Party(party_id) 
           constraint UK_Person_PartyId unique 
    , dob date 
) 
go 
set identity_insert Person on 
insert Person (person_id, name, party_id, dob) 
     select 2, 'Rob Ottow' , 12, '1984-12-25' 
union select 3, 'Ann Droid' , 13, null --it's impolite to give a woman's age 
union select 4, 'Si Bermann', 14, '1973-06-12' 
set identity_insert Person off 
go 

create table Organisation --en-gb spelling since that's where I'm from 
(
    organisation_id bigint not null identity(1,1) constraint PK_Organisation primary key clustered 
    , name nvarchar(256) not null 
    , party_id bigint not null constraint FK_Organisation_PartyId references Party(party_id) 
    , taxNumber nchar(12) not null 
) 
go 
set identity_insert Organisation on 
insert Organisation (organisation_id, name, party_id, taxNumber) 
     select 1, 'Global Mega Org' , 112, '123456789012' 
union select 2, 'GeoTech Solutions' , 113, '' 
union select 3, 'Think Ink inc.' , 114, '9' 
set identity_insert Organisation off 
go 

create table PartyContact 
(
     partyContact_id bigint not null identity(1,1) constraint PK_PartyContract primary key clustered 
    , party_id bigint not null constraint FK_PartyContract_PartyId foreign key references Party(party_id) 
    , contactDetails nvarchar(256) not null 
    , contactType_id int not null constraint FK_PartyContract_ContactTypeId foreign key references ContactType(contactType_id) 
) 
go 
set identity_insert PartyContact on 
insert PartyContact (partyContact_id, party_id, contactDetails, contactType_id) 
     select 1   ,112  ,'[email protected]' ,1 
union select 2   ,12   ,'[email protected]' ,1 
union select 3   ,13   ,'[email protected]' ,1 
union select 4   ,14   ,'[email protected]' ,1 
union select 5   ,113  ,'[email protected]' ,1 
union select 6   ,14   ,'[email protected]' ,2 
union select 7   ,114  ,'[email protected]' ,2 
union select 8   ,13   ,'[email protected]' ,1 
union select 9   ,13   ,'' ,3 
set identity_insert PartyContact off 
go 

create table Registrations 
(
     registration_id bigint not null identity(1,1) constraint PK_Registrations primary key clustered 
    , party_id bigint not null constraint FK_Registrations_PartyId references Party(party_id) 
    , name nvarchar(32) not null 
    , registration_Date date not null 
) 
go 
set identity_insert Registrations on 
insert Registrations (registration_id, party_id, name, registration_Date) 
     select 1    ,112  ,'ORG12' ,'10/05/2013' 
union select 2    ,12   ,'P12' ,'10/05/2013' 
union select 3    ,13   ,'P13' ,'10/05/2013' 
union select 4    ,14   ,'P14' ,'10/05/2013' 
union select 5    ,113  ,'O13' ,'10/05/2013' 
union select 6    ,114  ,'O14' ,'10/05/2013' 
set identity_insert Registrations off 
go 

/* get the results */ 
SELECT r.registration_id, r.party_id, p.person_id, o.organisation_id, r.name, c.contactDetails 
FROM Registrations r 
left outer join Person p on p.party_id = r.party_id 
left outer join Organisation o on o.party_id = r.party_id 
left outer join 
(
    select party_id, contactDetails 
    from PartyContact a 
    inner join 
    (
     select MIN(partyContact_id) partyContact_id 
     from PartyContact 
     where contactType_id in (select contactType_id from ContactType where name = 'WorkEmail') 
     group by party_id 
    ) b 
    on a.partyContact_id = b.partyContact_id  
) c 
    ON r.party_id = c.party_id