问题描述
我有一张桌子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
所以基本上,我想结合配偶和客户的名字,但我想排除配偶:
So basically, I want to combine the names of the spouse and client, but I want to exclude the spouse:
预期结果:
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
但结果是:
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
这是一些模拟数据:
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.
注意:我已经编辑了我的模拟脚本以在结果中包含 3, NoSpouse Morales, null.此外,对于丈夫/妻子,也不需要特别的标准.列表中最先被提取的人不应包括相关配偶.
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