MySQL 联合、连接和限制

Question

我有以下查询:

SELECT result.globalId AS id, result.date, p1.playerName AS player, p2.playerName AS   target, w.weaponIngameName AS weapon, result.headshot, s.serverName AS server, result.origin
FROM (
(

SELECT globalId, date, serverId, playerId, targetId, weaponId, headshot, 'playerkills' AS origin
FROM playerkills
WHERE (
playerkills.playerId =976
OR playerkills.targetId =976
)
ORDER BY date DESC
)
UNION (

SELECT globalId, date, serverId, playerId, NULL , NULL , NULL , 'playersuicides' AS origin
FROM playersuicides
WHERE (
playersuicides.playerId =976
)
ORDER BY date DESC
)
)result
LEFT JOIN players p1 ON result.playerId = p1.playerId
LEFT JOIN players p2 ON result.targetId = p2.playerId
LEFT JOIN weapons w ON result.weaponId = w.weaponId
LEFT JOIN servers s ON result.serverId = s.serverId
ORDER BY result.date DESC , result.globalId DESC

在应用任何 JOIN 之前，我想限制 result 联合最多有 300 个结果。我该如何实现？

在 (...) result 之后简单地添加 LIMIT 300 是行不通的，它会给出以下 MySQL 错误:You have an error in your SQL syntax ;查看与您的 MySQL 服务器版本对应的手册，了解在第 9 行的“LEFT JOIN players p1 ON result.playerId = p1.playerId LEFT JOIN players p2 ON r”附近使用的正确语法

Answer 1

将查询更改为以下内容时，它确实有效:

SELECT result.globalId AS id, result.date, p1.playerName AS player, p2.playerName AS   target, w.weaponIngameName AS weapon, result.headshot, s.serverName AS server, result.origin
FROM (
(

SELECT globalId, date, serverId, playerId, targetId, weaponId, headshot, 'playerkills' AS origin
FROM playerkills
WHERE (
playerkills.playerId =976
OR playerkills.targetId =976
)
ORDER BY date DESC
)
UNION (

SELECT globalId, date, serverId, playerId, NULL , NULL , NULL , 'playersuicides' AS origin
FROM playersuicides
WHERE (
playersuicides.playerId =976
)
ORDER BY date DESC
) ORDER BY date DESC LIMIT 300) result
LEFT JOIN players p1 ON result.playerId = p1.playerId
LEFT JOIN players p2 ON result.targetId = p2.playerId
LEFT JOIN weapons w ON result.weaponId = w.weaponId
LEFT JOIN servers s ON result.serverId = s.serverId
ORDER BY result.date DESC , result.globalId DESC

请注意，我在调用表 result 之前添加了 ORDER BY date DESC LIMIT 300，因此现在大致发生以下情况:

1) UNION 中的单个 SELECT 正在运行。

2) UNION 用于合并结果。

3) ORDER BY 和 LIMIT 被应用于 UNION。

4) UNION 被命名为result。

5) 查询的其余部分发生。