mysql 正确从多个表中拉取数据，不重复不正确的数据

Question

场景/用例

老师允许他们的学生与其他老师和年级的 child 一起玩游戏，并通过玩游戏赚取积分。小组由老师组成， children 被分配到小组中。

因此，教师有一个帐户，因此是一个用户。 User 有 UserInfo 并且有 Kid 与之关联。教师(例如 User)然后选择一个 Kid 放入 PlayerGroup。

问题

我已经得到包含数据库的 db-fiddle 构建和我对查询的尝试(我意识到查询不是很好，抱歉!)。我有点能够提取我想要的数据，但是每个 child 都显示在每个老师的下面，所以每个 child 都被复制了 3 次。我提出的查询仅适用于 3 个 child ，组最多可以有 5 个，所以我需要稍后更新它。

我尝试过使用 JOIN、GROUP BY，使用 IN 而不是“=”，但我无法获得所需的输出，即使搜索 Google 和 StackOverflow 也是如此。

期望的输出

我只想提取特定组(例如 playergroupid = 200)的 child 信息和他们的老师信息，而不显示重复项和错误信息。

在评论数据库结构之前...

此代码作为 future 数据库的示例快速设置。请不要对区分大小写、选择的类型等发表评论，因为这些将在实际构建之前更新。我只是在寻求查询帮助。感谢您的理解。

好的，我们开始吧!

DB-Fiddle 构建并准备好一切!

https://www.db-fiddle.com/f/n3ZEncyTteSoTVeybSaRbq/0

CREATE TABLE User(
userid int,
username varchar(255),
PRIMARY KEY (userid)
);

CREATE TABLE UserInfo(
userid int,
name varchar(255),
bio varchar(255),
kid1 int,
kid2 int,
kid3 int,
kid4 int,
kid5 int,
PRIMARY KEY (userid),
FOREIGN KEY (userid) REFERENCES User(userid)
);

CREATE TABLE PlayerGroup(
playergroupid int,
createdby int,
teacher1id int,
kid1id int,
teacher2id int,
kid2id int,
teacher3id int,
kid3id int,
PRIMARY KEY (playergroupid),
FOREIGN KEY (createdby) REFERENCES User(userid)
);

CREATE TABLE Kid(
kidid int,
points int,
kidname varchar(255),
kidgrade varchar(255),
teacherid int,
PRIMARY KEY (kidid),
FOREIGN KEY (teacherid) REFERENCES User(userid)
);

现在是 SQL 查询

SET @v_groupid = 200;

Select  ui.name as "Teacher Name", kid.kidname as "Kid Name", kid.points, kid.kidgrade
FROM (
  Select kidname, points, kidgrade
  FROM Kid
  WHERE kidid=
    (
    Select kid1id
    FROM PlayerGroup
    WHERE playergroupid = @v_groupid
    )
    OR kidid = 
    (
    Select kid2id
    FROM PlayerGroup
    WHERE playergroupid = @v_groupid
    )
    OR kidid = 
    (
    Select kid3id
    FROM PlayerGroup
    WHERE playergroupid = @v_groupid
    )
)
as kid,
(
  SELECT name
  FROM UserInfo
  WHERE userid = 
    (
    Select teacher1id
    FROM PlayerGroup
    WHERE playergroupid = @v_groupid
    )
    OR userid = 
    (
    Select teacher2id
    FROM PlayerGroup
    WHERE playergroupid = @v_groupid
    )
    OR userid = 
    (
    Select teacher3id
    FROM PlayerGroup
    WHERE playergroupid = @v_groupid
    )
)as ui

更新

添加我设想的表结构

Answer 1

您的结构使查询变得困难。而不是 UserInfo 有 5 列，创建

CREATE TABLE Class (
userid int,
kidid int,
PRIMARY KEY (userid, kidid)
);

也许类有一个名称，它可能是一个不同的表，但是 kidid->userid 关系似乎在各个地方都是重复的。

与 PlayerGroup 相同，删除名称列表和一个名为 PlayGroupMembers 的单独表来定义成员:

CREATE PlayGroupMembers (
playergroupid int,
userid int,
kidid int,
PRIMARY KEY (playergroupid, userid, kidid)

(这里的 kidid 的 userid 真的只是类映射吗？还是 kid.teacherid？)

然后结果变得更容易，例如:

SELECT pg.*, ui.*, k.*
FROM PlayGroupMembers pgm
JOIN PlayerGroup pg
  ON pgm.playergroupid = pg.playergroupid
JOIN UserInfo ui
  ON pgm.userid = ui.userid
JOIN kid k
  ON pgm.kidid = k.kidid
WHERE pgm.playergroupid = @v_groupid