問題描述

我有一張桌子Person:

PersonID | FirstName | LastName
-------------------------------
1        |   John    |  Doe
2        |   Jane    |  Doe
3        |  NoSpouse | Morales
4        | Jonathan  | Brand
5        | Shiela    | Wife

還有一個 Relationship 表:

RelationshipID | PersonID | Type | RelatedPersonID
1              |    1     |  3   |     2
2              |    2     |  3   |     1
3              |    4     |  3   |     5
4              |    5     |  3   |     4

所以基本上，我想結(jié)合配偶和客戶的名字，但我想排除配偶:

So basically, I want to combine the names of the spouse and client, but I want to exclude the spouse:

預(yù)期結(jié)果:

1,  John and Jane Doe, 2
----------------------
3, NoSpouse Morales, null
-----------------------
4, Jonathan and Shiela Brand, 5

我試過了:

SELECT p.PersonID,
    Case when spouse.PersonID is not null 
        THEN p.FirstName + ' and ' + spouse.FirstName + ' ' + p.LastName
    ELSE p.FirstName + ' ' + p.LastName END as ClientName,
    spouse.PersonID as RelatedPersonID
FROM Person p
LEFT JOIN Relationship r on p.PersonID = r.PersonID
LEFT JOIN Person spouse on r.RelatedPersonID = spouse.PersonID
WHERE r.Type = 3 OR spouse.PersonID is null

但結(jié)果是:

1,  John and Jane Doe, 2
----------------------
2,  Jane and John Doe, 1
----------------------
3, NoSpouse Morales, null
-----------------------
4, Jonathan and Shiela Brand, 5
-------------------------------
5, Shiela and Jonathan Wife, 4

這是一些模擬數(shù)據(jù):

create table Person(
    PersonID int primary key,
    FirstName varchar(max),
    LastName varchar(max)
)
insert into Person values 
(1, 'John', 'Doe'), 
(2, 'Jane', 'Doe'), 
(3, 'NoSpouse', 'Morales'), 
(4, 'Jonathan', 'Brand'), 
(5,'Shiela','Wife')

create table Relationship (
    RelationshipID int,
    PersonID int references Person(PersonID),
    Type int,
    RelatedPersonID int references Person(PersonID)
)
insert into Relationship values 
(1, 1, 3, 2),
(2, 2, 3, 1),
(3, 4, 3, 5),
(4, 5, 3, 4)

SELECT p.PersonID,
    Case when spouse.PersonID is not null 
        THEN p.FirstName + ' and ' + spouse.FirstName + ' ' + p.LastName
    ELSE p.FirstName + ' ' + p.LastName END as ClientName,
    spouse.PersonID as RelatedPersonID
FROM Person p
LEFT JOIN Relationship r on p.PersonID = r.PersonID
LEFT JOIN Person spouse on r.RelatedPersonID = spouse.PersonID
WHERE r.Type = 3 OR spouse.PersonID is null

drop table Relationship
drop table Person

提前感謝您的幫助和時間.

thanks in advance for your help and time.

注意:我已經(jīng)編輯了我的模擬腳本以在結(jié)果中包含 3, NoSpouse Morales, null.此外，對于丈夫/妻子，也不需要特別的標(biāo)準(zhǔn).列表中最先被提取的人不應(yīng)包括相關(guān)配偶.

NOTE: I've edited my mock script to include 3, NoSpouse Morales, null in the results. Also, there is no particular criteria needed to which is husband/wife. Whoever was fetched first in the list should not include the related spouse.

推薦答案

如果必須包含一個而另一個排除，請嘗試添加子句

If one has to be included while the other excluded, try adding a clause

AND r.PersonID < r.RelatedPersonID 

因為 ID 不相等，這將只包括其中一個:

since the IDs will not be equal and this will include only either one:

 SELECT p.PersonID,
 Case when spouse.PersonID is not null 
    THEN p.FirstName + ' and ' + spouse.FirstName + ' ' + p.LastName
 ELSE p.FirstName + ' ' + p.LastName END as ClientName,
 spouse.PersonID as RelatedPersonID
FROM Person p
LEFT JOIN Relationship r on p.PersonID = r.PersonID
LEFT JOIN Person spouse on r.RelatedPersonID = spouse.PersonID
WHERE (r.Type = 3 AND r.PersonID < r.RelatedPersonID)  OR spouse.PersonID is null

這篇關(guān)于選擇表中的人并排除妻子但合并他們的名字的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！