mysql - 如何使用mysql从存储过程中删除重复项

请检查这些表。


FAMILY表：FamilyID（pk），familyName
PARENT表：ParentID（pk），FamilyID（fk），用户名，电子邮件，密码
KID表：KidID（pk），FamilyID（fk），用户名，电子邮件，密码


下面是从特定姓氏中获取父母和子女的存储过程。在此，从3个表中检索数据。

CREATE DEFINER=`root`@`localhost` PROCEDURE `prc_Dashboard`(IN `inputuserName` VARCHAR(255))
BEGIN
SET @familyID = (SELECT familyID FROM PARENT WHERE userName = inputuserName);

SELECT
    F.familyName AS FamilyName,
    P.userName AS Parents,
    K.userName AS Kids
FROM
    PARENT P
INNER JOIN
    FAMILY F ON P.FamilyID = F.FamilyID
LEFT OUTER JOIN
    KID K ON K.FamilyID = F.FamilyID
WHERE
   P.FamilyID = @familyID AND F.FamilyID = @familyID
GROUP BY
    FamilyName, Parents,Kids;
END

输出应为



相反，它会创建家庭，父母和孩子的副本，并给我6条记录。

最佳答案

您想将两个列表合并在列中。这不是很“关系”的事情。可以，但不能使用join。相反，您可以使用聚合来执行此操作：

select family_name, max(parent) as parent, max(kid) as kid
from ((select family_name, user_name as parent, NULL as kid
              (@rn := if(@f = family_name, @rn + 1,
                         if(@f := family_name, 1, 1)
                        )
               ) as rn
       from family f join
            parents p
            using (familyid) cross join
            (select @f := '', @rn := 0) params
       order by family_name
      ) union all
      (select family_name, NULL, k.user_name,
              (@rnk := if(@fk = family_name, @rnk + 1,
                         if(@fk := family_name, 1, 1)
                        )
               ) as rnk
       from family f join
            kids k
            using (familyid) cross join
            (select @fk := '', @rnk := 0) params
       order by family_name
      )
     ) pk
group by family_name, rn;