mysql - SQL 查询将每一行重复 n 次并向其添加来自另一个表的数据

Question

我有一个 SQL 查询，其结果表包含 700 个用户 ID 和相应的游戏 ID(用户玩的游戏；总共 1000 个游戏)的列表。

一名用户可能玩过不止一款游戏，并且一个游戏 ID 可能与多个用户 ID 相关联。我在数据库中有另一个表，其中包含所有 gameid 及其各自属性的列表。

我希望我的最终结果表如下:第 1 列包含所有 700 个用户 ID(每个用户 ID 重复的次数与与用户关联的游戏 ID 的数量相同)，第 2 列包含相应的游戏 ID 和游戏 ID 表中的其他游戏 ID。针对每个用户 ID 的这些其他游戏 ID 应与针对该用户 ID 已放置的游戏 ID 不同。

表1

Userid      Gameid    
1              3    
1              4    
2             63    
2             76    
6             99    
6             63

表2

Gameid     gamename
1           GTA    
2           DOTA    
1000        xyz

结果表

Userid  Gameid       Gamename
1         3          AOE    
1         4          Roadrash    
1         1          GTA    
1         2          DOTA    
1         5          qbc     
so on up to 
1       1000         xyz

表 1 中的所有用户 ID 也是如此。

结果表中每个用户 ID 重复 1000 次，每个用户 ID 的前 n 个 gameid 是表 1 中的游戏 ID(特定用户玩过的游戏)，其余的 gameid 来自表 2 中的 1000 个游戏 ID(因此每个用户映射到所有游戏 ID，其中排名靠前的是他们玩过的游戏)。

数据设置的想法是在结果表的末尾包含一列具有二进制值的列(1 -> 用户已经玩过游戏，0 -> 用户还没有玩过游戏)。该表将用于机器学习。

Answer 1

将结果表构建为用户玩过的游戏和用户未玩过的游戏的并集，如下所示:

  select UserId, GameId, GameName, Played
  from (
    -- games played
    select usr.UserId, gme.GameId, gme.GameName, 1 as Played
    from USERS usr
    inner join GAMES gme on usr.GameId = gme.GameId
    union
    -- games not played
    select usr.UserId, gme.GameId, gme.GameName, 0 as played
    from USERS usr, GAMES gme
    where gme.GameId not in (
      select usr2.GameId
      from USERS usr2
       where usr2.UserId = usr.UserId
    )
  )
  order by Userid, Played desc, Gameid

外部选择用于对结果集进行排序，以便首先提供所玩的游戏。