两列合并保留其他列。然后用另一台合并
问题描述:
// friendships
+-----------------+-----------------+-----------------+
| fid | person1 | person2 |
|-----------------+-----------------|-----------------+
+-----------------+-----------------+-----------------+
| 1 | personid | personid |
|-----------------+-----------------|-----------------+
| 2 | personid | personid |
|-----------------+-----------------|-----------------+
// persons
+-----------------+-----------------+-----------------+
| pid | firstname | lastname |
|-----------------+-----------------|-----------------+
+-----------------+-----------------+-----------------+
| 1 | name | name |
|-----------------+-----------------|-----------------+
| 2 | name | name |
|-----------------+-----------------|-----------------+
1)我想在友谊表,它含有一定PERSONID所有行。此ID可以在person1或person2列中。该fid
列应保留,但person
列应该只有一个,例如:
Select fid, person1 as person, person2 as person FROM friendships
WHERE person1 = some_personid
OR person2 = some_personid;
(2个人从该查询栏应该只有一个)。我怎么做?
2)我想加入人表的结果从步骤1 ON fid.person = persons.pid
。
答
如果我理解正确,您需要所需人员的每个朋友的名字和姓氏,无论此人是否在person1
或person2
之下。
在这种情况下,如果友谊关系不是对称的,你可以做到这一点与子查询
select *
from persons p
join (
select fid, person1 as person, person2 as otherPerson
from friendship
where person1 = 'yourPerson'
union all
select fid, person2 as person, person1 as otherPerson
from friendship
where person2 = 'yourPerson'
) f
on p.pid = f.otherPerson
如果其对称,查询更容易,因为person2
下与期望的人的每一行都会有与期望的人在person1
下对应的行。
select *
from friendship f
join person p
on f.person2 = p.pid
where person1 = 'yourPerson'
答
对于第一步,在where过滤器中使用OR子句来指示应保留哪些记录。然后在你的选择中使用一个case语句来选择哪个人。
select fid
, case person1 when some_personid then person1 else person2 end as person
from friendships
where (person1 = some_personid
or person2 = some_personid)
对于第二步,在从人员选择和使用子查询从步骤1加入到表。
select *
from persons p
inner join (
select fid
, case person1 when some_personid then person1 else person2 end as person
from friendships
where (person1 = some_personid
or person2 = some_personid)
) f on f.person = p.pid
希望这有助于!
友谊关系是否对称?我的意思是如果A是B的朋友,也是A的B朋友? –