mysql - sql : finding how many objects were stolen by a player?

Question

数据库设计:

fiddle :http://sqlfiddle.com/#!2/4f23b3

我有一张表 players

CREATE TABLE players (
    id MEDIUMINT(7) unsigned AUTO_INCREMENT PRIMARY KEY ,
    name VARCHAR(30)
) ENGINE = InnoDB

一个表对象

CREATE TABLE objects (
    id INT(9) unsigned AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30),
    type VARCHAR(20)
) ENGINE = InnoDB

还有一个描述对象和玩家之间的一对多关系的表(一个玩家可以有多个对象)，但它不是严格的一对多，我们稍后会看到:

CREATE TABLE playerobjects (
    objectid INT(9) unsigned NOT NULL,
    playerid MEDIUMINT(7) unsigned NOT NULL,
    `date` DATE NOT NULL,
    PRIMARY KEY(objectid,`date`)
) ENGINE = InnoDB

上表可能难以理解，但这就是它的全部含义:

• 玩家可以在任何给定日期拥有多个对象
• 一个对象在任何给定日期只能属于一个玩家
• 一个玩家可以偷另一个玩家的元素，这意味着表playerobjects 将有两个不同playerids 和不同日期的被盗元素条目
• 一件元素每天只能被盗一次

因此，我们可以从上述准则推断出一个对象可能有多个所有者，但每天只有一个。也有可能玩家 1 从玩家 2 那里偷了一些东西，第二天玩家 2 从玩家 1 那里偷回了这个东西。

我想做什么:

现在，我想从上面的数据库中找出玩家偷走了多少元素以及目前拥有的元素。我还想制作一个排行榜，显示排名靠前的玩家。被盗元素。

就是说，如果 Bob 先拥有该对象，但 Emily 从 Bob 那里偷走了它，然后 Sheldon 从 Emily 那里偷了它，那么查询应该只显示该对象是 Sheldon 在某个日期偷走的，没有别的，因为 Emily 确实偷了该对象来自 Bob，但那是很久以前的事了，该对象的当前所有者是 Sheldon。

示例代码

INSERT INTO  players (name) VALUES ('Bob') , #id 1
        ('Emily') ,  #id  2
        ('Sheldon'); #id 3

INSERT INTO objects (name,type) VALUES ('Choco vanilla','ice cream'), #1 
        ('Butterscotch','ice cream'), #2
        ('Nexus 4','Mobile Phone'), #3
        ('Snoopy','pet'), #4
        ('minecraft','game'); #5

INSERT INTO playerobjects (playerid,objectid,date) VALUES (1,1,'2013-05-15'), 
        (2,2,'2013-05-15'), 
        (3,3,'2013-05-15'), 
        (1,4,'2013-05-15'), 
        (2,1,'2013-05-16'),
        (1,5,'2013-05-16'),
        (3,1,'2013-05-17'), 
        (1,3,'2013-05-18'), 
        (3,3,'2013-05-19'), 
        (3,5,'2013-05-19'),
        (2,5,'2013-05-20');

id 为 3 的个人的预期结果，行:

        (3,1,'2013-05-17'), 
        (3,3,'2013-05-19')

id 为 2 的个体的预期结果，行:

        (2,5,'2013-05-20')

玩家 1 的预期结果:无，因为他偷的东西又被他偷走了，只剩下他没有从任何人那里偷来的东西。

排行榜查询的预期结果:

 sheldon 2
 emily   1
 bob     0

我的想法:

排行榜查询，给出了错误的信息，而且我认为效率低下:

SELECT tpo.playerid,COUNT(*) as steals
FROM `playerobjects` AS tpo

LEFT JOIN `playerobjects` AS tpo2 ON (tpo.objectid = tpo2.objectid AND tpo.date < tpo2.date)
LEFT JOIN `playerobjects` AS tpo3 ON (tpo.objectid = tpo3.objectid AND tpo.date > tpo3.date)

WHERE tpo2.objectid IS NULL
AND tpo3.objectid IS NOT NULL

GROUP BY (tpo.playerid)
ORDER BY steals DESC

查询单个玩家和偷窃的详细信息，给出错误的信息:

SELECT tpo.objectid,tpo.date
FROM `playerobjects` AS tpo

LEFT JOIN `playerobjects` AS tpo2 ON (tpo.objectid = tpo2.objectid AND tpo.date < tpo2.date)
LEFT JOIN `playerobjects` AS tpo3 ON (tpo.objectid = tpo3.objectid AND tpo.date > tpo3.date)

WHERE tpo2.objectid IS NULL
AND tpo3.objectid IS NOT NULL

为什么这些查询会给出错误的信息？因为它们还计算了对象的先前记录。将它与之前的 Bob Emily Sheldon 示例联系起来，虽然它应该只返回 Sheldon 从 Emily 那里偷走元素时的记录，但它也会显示 Emily 从 Bob 那里偷走元素的记录。我不知道如何在不使用复杂的子查询的情况下解决这个问题，这会使查询效率更低。我真的希望有更好的方法来做到这一点。

Answer 1

select tpo.objectid, tpo.date from playerobjects  as tpo where (select count(*) from playerobjects as tpo2 where ((tpo2.date>tpo.date) and (tpo2.objectid=tpo.objectid)))=0 and playerid=3;

希望对你有帮助