問題描述
SQL在友誼表中插入值基於 (SQL insert value in friendship table based on)
I’m taking a database course and I’ve got stuck on one of the questions. “For all cases where A is friends with B, and B is friends with C, add a new friendship for the pair A and C”. This is as far as I’ve got
INSERT INTO
friend
SELECT DISTINCT
f1.ID1, f2.ID1
FROM
friend f1
JOIN friend f2 using (ID2),
friend
WHERE
f1.ID1 <> f2.ID1
AND friend.ID1 <> f1.ID1
AND friend.ID2 <> f1.ID2
The schema is here http://www.sqlfiddle.com/#!5/cf8b5/23
I wonder if somebody could give me a few hints on how to proceed. Thanks.
‑‑‑‑‑
參考解法
方法 1:
I think you have the basic concepts.
I'd do it like the following:
INSERT INTO friend
(ID1, ID2)
SELECT DISTINCT f1.ID1, f2.ID2
FROM friend f1
INNER JOIN f2
ON f1.ID2 = f2.ID1
AND f1.ID1 <> f2.ID1
AND f1.ID2 <> f2.ID2
WHERE f1.ID1 <> f2.ID2
方法 2:
I think it may be:
INSERT INTO friend
(ID1, ID2)
SELECT DISTINCT h.ID, f2.ID2
FROM Highschooler h
inner join friend f1 on(h.ID=f1.ID1)
inner join friend f2 on(f1.ID2=f2.ID1)
where h.ID <> f2.ID2
and not exists(select *
from friend f3
where f3.ID1=h.ID
and f3.ID2 = f2.ID2)
方法 3:
First of all, I don't see the point of having both FRIEND and LIKES tables.
If friendship is represented by two highschoolers liking each other than one table with two ID columns is sufficient.
方法 4:
insert into friend
select f1.id1, f2.id2
from friend f1 join friend f2 on f1.id2 = f2.id1
where f1.id1 <> f2.id2
except
select * from friend
(by NinaC、Marlin Pierce、sep、Lupuss、B‑Y)