排除具有匹配的较新记录的 mysql 表记录 [英] Exclude mysql table records that have matching, newer records

问题描述

我有一个系统可以查询各个坐标处的游戏活动数据库表.我需要查询某个玩家的活动，但我需要排除任何与相同坐标处的新条目匹配的活动(其他玩家或其他玩家，随便什么都行).

I have a system that queries a db table of game activity at various coordinates. I have a need to query for activities by a certain player but I need to exclude any that match newer entries at the same coordinates (by other players or not, just anything).

示例查询是:

SELECT * FROM prism_actions
WHERE world = 'world'
AND (action_type = 'block-place')
AND (player = 'viveleroi')
AND (x BETWEEN -448.7667627678472 AND -438.7667627678472)
AND (y BETWEEN 62.0 AND 72.0)
AND (z BETWEEN -291.17236958025796 AND -281.17236958025796)
ORDER BY x,y,z ASC
LIMIT 0,1000000

我试过使用子查询和内部连接使其工作，但无法获得它.我也确实需要能够加快速度.

I've tried making it work with a subquery and inner join but just can't get it. I really need to be able to make this speedy as well.

本质上，当有另一条记录具有相同的 X、Y、Z 但具有新的 action_time 时，我需要此查询来排除坐标 X、Y、Z 处的任何记录.

Essentially I need this query to exclude any records at coords X,Y,Z when there's another record with same X,Y,Z but with a new action_time.

我还考虑过在输入新匹配项时使相同 x,y,z 处的记录过期的某种方法，但这似乎也没有我希望的那么有效.

I've also considered some way of expiring records at the same x,y,z when a new match is entered but that seems not quite as efficient as I'd like as well.

推荐答案

您可以通过加入过滤掉最新"坐标的子查询来实现:

You can do this by joining on a subquery that filters out the 'latest' coordinates:

SELECT prism_actions.*
FROM prism_actions
JOIN (
    SELECT x, y, z, max(action_time) as action_time
    FROM prism_actions
    GROUP BY x, y, z) latest
  ON prism_actions.action_time = latest.action_time
  AND prism_actions.x = latest.x
  AND prism_actions.y = latest.y
  AND prism_actions.z = latest.z
WHERE prism_actions.world = 'world'
AND (prism_actions.action_type = 'block-place')
AND (prism_actions.player = 'viveleroi')
AND (prism_actions.x BETWEEN -448.7667627678472 AND -438.7667627678472)
AND (prism_actions.y BETWEEN 62.0 AND 72.0)
AND (prism_actions.z BETWEEN -291.17236958025796 AND -281.17236958025796)
ORDER BY x,y,z ASC
LIMIT 0,1000000

这篇关于排除具有匹配的较新记录的 mysql 表记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！