MySQL - 将查询结果分配给变量

Question

我有这个问题:

given N users subscribed to a website, if the user who visits the page is logged, show a list of suggestions on the basis of friends in common between X and Y.

我问我的信息技术教授如何解决这个问题，但他只懂SQL，而我需要MySQL代码(我使用PhpMyAdmin)。

如何将这段 SQL 代码转换为 MySQL 代码？

“QUALIAMICI”的代码(它告诉我 X 的 friend 是什么(Y 是 id 列表))

CREATE PROCEDURE QUALIAMICI ( X integer ) RETURNS ( Y integer ) AS
BEGIN SUSPEND; END^

ALTER PROCEDURE QUALIAMICI ( X integer ) RETURNS ( Y integer ) AS
begin
  for 
    select idDestinatario from amicizie where idmittente=:x 
    union 
    select idMittente from amicizie where iddestinatario=:x
    into :y
  do suspend;
end^

“AMICI_COMUNI”代码(它告诉我 X 和 Y 之间的共同 friend )

CREATE PROCEDURE AMICI_COMUNI ( X integer, Y integer ) AS
BEGIN SUSPEND; END^

ALTER PROCEDURE AMICI_COMUNI ( X integer, Y integer ) AS
begin
  insert into Uno (id_amico) select * from QUALIAMICI(:x);
  insert into Due (id_amico) select * from QUALIAMICI(:y);
  insert into Tre (id_amico) select * from Uno where id_amico in (select id_amico from due);
end^

如何将其转换为 MySQL？我对 MySQL 知之甚少，所以我希望您解释一下建议。

P.S.:即使我没有将变量翻译成英文，你能理解这个算法吗？P.P.S.:我很抱歉英语不好，我是意大利人:S

Answer 1

您正在创建一个包含 X 的 friend 的表和一个包含 Y 的 friend 的表，然后获取两个表中的 id 列表。下面介绍了如何在 MySQL 中使用带有子查询的单个语句来完成此操作。

SET @x = 123; -- id for user X
SET @y = 456; -- id for user Y

CREATE TEMPORARY TABLE friendsInCommon (id int);

INSERT INTO friendsInCommon (id)
SELECT id
FROM
  (SELECT idDestinatario AS id
  FROM amicizie
  WHERE idMittente = @x
  UNION
  SELECT idMittente
  FROM amicizie
  WHERE idDestinatario = @x) AS XAmicizie
WHERE id IN
  (SELECT idDestinatario AS id
  FROM amicizie
  WHERE idMittente = @y
  UNION
  SELECT idMittente
  FROM amicizie
  WHERE idDestinatario = @y);

SELECT id FROM friendsInCommon;