mySQL 子查询出现限制错误

Question

我有两个游戏部分，但遇到了问题。第一部分的比赛什么时候开始，我不想在第二部分再次展示她。对于第一部分中的表演游戏，我正在使用:

SELECT *,platform.platformName FROM games 
JOIN platform ON(platform.PlatformID=games.GamePlatform) 
WHERE GameDate <= NOW() AND GameSlide = 0 
GROUP BY GameName 
HAVING 1 
ORDER BY `games`.`GameDate` DESC LIMIT 8

对于第二部分的表演游戏:

SELECT *,platform.platformName 
FROM games 
JOIN platform ON(platform.PlatformID=games.GamePlatform) 
WHERE platform.PlatformID = 2 AND GameSlide = 0 
GROUP BY GameName 
HAVING 1 
order by rand(dayofyear(CURRENT_DATE)) 
LIMIT 8

我尝试创建这样的子查询:

SELECT *,platform.platformName 
FROM games 
JOIN platform ON(platform.PlatformID=games.GamePlatform) 
WHERE games.GameID 
NOT IN(
       SELECT GameID 
       FROM games 
       WHERE GameDate <= NOW() AND GameSlide = 0 
       GROUP BY GameName 
       HAVING 1 
       ORDER BY `games`.`GameDate` DESC LIMIT 8
      ) AND platform.PlatformID = 2 AND GameSlide = 0 
GROUP BY GameName 
HAVING 1 
order by rand(dayofyear(CURRENT_DATE)) LIMIT 8

但 MySQL 调用错误:#1235 - 此版本的 MariaDB 尚不支持“LIMIT & IN/ALL/ANY/SOME 子查询”

你能帮我一下吗？我为我的英语感到抱歉。我尽力了。

Answer 1

我已经做到了。我用选择创建了一个新 View 。

CREATE VIEW Novinky AS SELECT games.GameID FROM games WHERE games.GameDate <= NOW() AND games.GameSlide = 0 GROUP BY games.GameName HAVING 1 ORDER BY `games`.`GameDate` DESC LIMIT 8

然后将 NOT IN 与 SELECT from View 结合使用:

SELECT *,platform.platformName FROM games JOIN platform ON(platform.PlatformID=games.GamePlatform) WHERE GameID NOT IN (SELECT * FROM novinky) AND platform.PlatformID = 2 AND GameSlide = 0 GROUP BY GameName HAVING 1 order by rand(dayofyear(CURRENT_DATE)) LIMIT 8