时间戳

首页 > 解决方案 > 如何加入多值列？

sql - 如何加入多值列？

问题描述

我有两张桌子：

Game and Developer

我的游戏表有以下列：

Release Date Origin Date DeveloperID

我的开发人员表有以下列：

DevId Games

有了数据，表格可能看起来像这样：

Game:
Release Date Origin Date GameId
  11/20/2017  11/09/2015    G-1
  06/07/1999  03/04/1995    G-2
  08/31/2015  02/09/2010    G-3

Developer 表可能如下所示：

DevId Games
 D-1   G-1, G-2
 D-2   G-3
 D-3   G1, G2, G3

游戏可以有多个值，它们是在下拉列表中选择的。

我想在 Games 值和 Game 表的 GameId 之间创建关系，并将与 Dev 关联的每个游戏的发布日期和原始日期连接起来。

所以对于 D1，我想要类似的东西

D1 Dates - 11/20/2017, 11/09/2017, 06/07/1999, 03/04/1995 since G-1 and G-2 are associated with D1 
D2 Dates - only 08/31/2015, 02/09/2010
D3 Dates - 11/20/2017, 11/09/2015, 06/07/1999, 03/04/1995, 08/31/2015, 092/09/2010 since all of the Games are associated with this Dev.

我现在所拥有的并没有削减它。它没有正确分组，仅列出 6 行

11/20/2017
11/09/2015
06/07/1999 
03/04/1995
08/31/2015
02/09/2010

这是我的尝试：

SELECT [Origin Date] & ',' & [Release Date] FROM Game INNER JOIN Developer ON Game.GameId = Developer.Games.Value;

任何帮助，将不胜感激

标签： sqlms-access

解决方案

这假设游戏数限制为 3。如果不是这种情况，您可能必须编写另一个子查询来查找最大游戏数并构造等效数量的列。 

WITH cte AS
(
SELECT DevID, g_1, g_2, g_3
FROM
(
SELECT DevID, ROW_NUMBER() OVER() AS row_num1 FROM DevID
) a
LEFT JOIN
(
SELECT a[1] AS g_1, a[2] AS g_2, a[3] AS g_3, ROW_NUMBER() OVER() AS row_num2
FROM
(
SELECT STRING_TO_ARRAY(games, ',')
FROM DevID
) AS dt(a)
) b
ON a.row_num1 = b.row_num2
) 

SELECT DevId, ARRAY_AGG(release_date) AS release_dates, ARRAY_AGG(origin_date) AS origin_dates
FROM
(
SELECT DevID, game, Release_date, Origin_date
FROM
(
SELECT DevID, LTRIM(game) AS game
FROM
(
SELECT DevId, g_1 AS game FROM cte WHERE g_1 IS NOT NULL
UNION 
SELECT DevId, g_2 AS game FROM cte WHERE g_2 IS NOT NUll
UNION
SELECT DevId, g_3  AS game FROM cte WHERE g_3 IS NOT NULL
) a1
) al
LEFT JOIN
Game g1
ON al.game = g1.GameId
) al1 
GROUP BY DevId

输出：

DevId   ReleaseDates     OriginDates
"D-2"   "{2015-08-31}"  "{2010-02-09}"
"D-3"   "{2017-11-20,1999-06-07,2015-08-31}"    "{2015-11-09,1995-03-04,2010-02-09}"
"D-1"   "{2017-11-20,1999-06-07}"   "{2015-11-09,1995-03-04}"

推荐阅读