选择加入2个表的语句，按日期和状态搜索(Select statement joining 2 tables, searching by date, and status)

好吧我觉得我搞砸了某个地方，但也许有人可以发现我的错误，因为我对我正在做的事情一无所知。

我有2个桌面玩家和RegionPlayer（参见底部的结构）

我试图找到一段时间内没有一个区域的球员被看到。玩家可以休假，然后只有8天。如果在那段时间内没有看到某个区域的玩家，我希望sql搜索返回regionID，以及该区域中最近看到的人。

现在我认为这样做的方法是从每个地区获得2个结果，每个结果都为我提供了最近看过谁度假的玩家，以及没有休假的人。

但是，虽然我认为这会给我这一点，但似乎并没有。

SELECT RegionPlayer.Regionid, Players.key, Players.Name, Players.Seen, Players.Vacation FROM RegionPlayer JOIN Players ON Players.Key = RegionPlayer.Playerid where ( RegionPlayer.Status = 1 ) GROUP BY RegionPlayer.Regionid DESC, Players.Vacation DESC ORDER BY Players.Seen DESC

然后我需要能够分辨出一段时间没有见过的人，这应该给我这个。现在我知道我可以将两个查询链接在一起，但我不知道如何，自从我上次将这些努力付诸于sql语句以来已经很多年了。

Select Players.key FROM Players WHERE (( Players.Vacation != 1 ) AND ( Players.Seen <= (NOW() - INTERVAL 8 DAY ) )) OR (( Players.Vacation != 0 ) AND ( Players.Seen <= (NOW() - INTERVAL 58 DAY ) ))

有没有更好的方法来做到这一点，我记得像视图，存储过程和函数这样的东西，它们中的一个或多个会更好吗？

表结构。请原谅，桌子的名称和一些结构，这是为什么在1/2瓶葡萄酒是一个坏主意之后决定深夜的事情的一个例子。

CREATE TABLE IF NOT EXISTS `Players` ( `key` int(11) NOT NULL, `Name` varchar(255) NOT NULL, `Vacation` varchar(1) NOT NULL, `Seen` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `Modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) CREATE TABLE IF NOT EXISTS `RegionPlayer` ( `Key` int(11) NOT NULL, `Playerid` int(11) NOT NULL, `Regionid` int(11) NOT NULL, `Type` varchar(1) NOT NULL, `Status` int(1) NOT NULL DEFAULT '1', `Modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `Created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' )

OK I think I have messed up somewhere but maybe someone can spot my error, because I have little clue of what I am doing.

I have 2 Tables Players and RegionPlayer (see bottom for structure)

I am trying to find when a none of the players on a region have been seen in a while. Players can be on vacation which gives then 58 days, else its only 8 days. If none of the players on a region have been seen in that time, I want the sql search to return the regionID, as well as the most recent person on that region who was seen.

Now I think that way to do this is to get 2 results from each region, each providing me the most recent player seen who was on vacation, and who was not on vacation.

But while, I thought this would give me that, it doesn't seem to.

SELECT RegionPlayer.Regionid, Players.key, Players.Name, Players.Seen, Players.Vacation FROM RegionPlayer JOIN Players ON Players.Key = RegionPlayer.Playerid where ( RegionPlayer.Status = 1 ) GROUP BY RegionPlayer.Regionid DESC, Players.Vacation DESC ORDER BY Players.Seen DESC

Then I am going to need to be able to tell who has not been seen in a while, this should give me that. Now I know I can link both queries together, but I have no idea how, it has been many years since I last had to put this much effort into sql statements.

Select Players.key FROM Players WHERE (( Players.Vacation != 1 ) AND ( Players.Seen <= (NOW() - INTERVAL 8 DAY ) )) OR (( Players.Vacation != 0 ) AND ( Players.Seen <= (NOW() - INTERVAL 58 DAY ) ))

Is There a better way of doing this, I sort of remember things like views, and store procedures, and functions, would one or more of them be better?

Table Structure. Please forgive, the names, of the tables and some of the structure, This is an example of why deciding things late at night after 1/2 a bottle of wine is a bad idea.

CREATE TABLE IF NOT EXISTS `Players` ( `key` int(11) NOT NULL, `Name` varchar(255) NOT NULL, `Vacation` varchar(1) NOT NULL, `Seen` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `Modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) CREATE TABLE IF NOT EXISTS `RegionPlayer` ( `Key` int(11) NOT NULL, `Playerid` int(11) NOT NULL, `Regionid` int(11) NOT NULL, `Type` varchar(1) NOT NULL, `Status` int(1) NOT NULL DEFAULT '1', `Modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `Created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' )

最满意答案

我已经提出了一个SQLFiddle 。

回答您的基本要求的查询，似乎是：列出过去8天内没有看到活跃玩家的所有区域，并且在过去58天内没有看到空出的玩家，还提供该区域中最后一位玩家的数据：

SELECT r.* FROM ( SELECT rp.Regionid, p.Key, p.Name, p.Vacation, p.Seen FROM RegionPlayer rp JOIN Players p ON p.Key = rp.Playerid WHERE rp.Status = 1 GROUP BY rp.Regionid ORDER BY p.Seen DESC ) r WHERE ((r.Vacation != 1) AND (r.Seen <= (NOW()-INTERVAL 8 DAY))) OR ((r.Vacation != 0) AND (r.Seen <= (NOW()-INTERVAL 58 DAY)));

我从您的SQL中推测，只应考虑Status为1的RegionPlayer行。

在SQLFiddle上我创建了一些具有不同组合的区域，这个查询完成了它的工作。

至于你的第一个SQL语句。你说它不能按预期工作，但对我来说似乎是这样做的...最后一个看到活跃的玩家和最后一个看到每个地区空置的玩家。排序可能不会使它非常易读，但确实如此。

I've put up an SQLFiddle.

The query that answers your basic requirement, which seems to be: list all regions that have no active player seen in the last 8 days and no vacated player seen in the last 58 days, giving also the data of the last seen player in that region:

SELECT r.* FROM ( SELECT rp.Regionid, p.Key, p.Name, p.Vacation, p.Seen FROM RegionPlayer rp JOIN Players p ON p.Key = rp.Playerid WHERE rp.Status = 1 GROUP BY rp.Regionid ORDER BY p.Seen DESC ) r WHERE ((r.Vacation != 1) AND (r.Seen <= (NOW()-INTERVAL 8 DAY))) OR ((r.Vacation != 0) AND (r.Seen <= (NOW()-INTERVAL 58 DAY)));

I desumed from your SQL that only RegionPlayer rows with a Status of 1 should be considered.

On the SQLFiddle I've create a bit of regions with different combinations, and this query does its job.

As to your first SQL statement. You say it doesn't work as expected, but to me it seems to do it... the last seen active player and last seen vacated player for each region. The sorting may not make it very readable, but it does do that.

更多推荐