php - 如何订购这个特定的内部联接？

Question

现在我正在创建一个在线游戏，其中列出了球员的最后转会。

处理玩家历史记录的表具有历史加入日期和历史结束日期列。

当history_end_date被填满时，这意味着玩家离开了俱乐部，当它像默认值(0000-00-00 00:00:00)并且history_join_date有某个日期时，这意味着玩家加入了俱乐部(在那个日期) )。

现在，我有以下查询:

SELECT 
    player_id,
    player_nickname,
    team_id,
    team_name,
    history_join_date,
    history_end_date
FROM 
    players
        INNER JOIN history
            ON history.history_user_id = players.player_id
        INNER JOIN teams
            ON history.history_team_id = teams.team_id
ORDER BY 
    history_end_date DESC, 
    history_join_date DESC
LIMIT 7

但是，此查询返回类似的内容(使用上面的 PHP 进行过滤):

(22-Aug-2012 23:05): Folha has left Portuguese Haxball Team.
(22-Aug-2012 00:25): mancini has left United.
(21-Aug-2012 01:29): PatoDaOldSchool has left Reign In Power.
(22-Aug-2012 23:37): Master has joined Born To Win.
(22-Aug-2012 23:28): AceR has joined Born To Win.
(22-Aug-2012 23:08): Nasri has joined Porto Club of Haxball.
(22-Aug-2012 18:53): Lloyd Banks has joined ARRIBA.

PHP 过滤器:

foreach ($transfers as $transfer) {

//has joined
if($transfer['history_end_date']<$transfer['history_join_date']) {
    $type = ' has joined ';
    $date = date("d-M-Y H:i", strtotime($transfer['history_join_date']));
} else {
    $type = ' has left ';
    $date = date("d-M-Y H:i", strtotime($transfer['history_end_date']));
}

如您所见，在转账订单中，并未严格遵循日期(8 月 22 日 => 8 月 21 日 => 8 月 22 日)。

我在 SQL 中缺少什么？

问候!

Answer 1

问题是您根据两个不同的值进行排序。因此，您的结果首先按history_end_date排序，当结束日期相等时(即，当它是默认值时)，然后按history_join_date排序

(请注意，您的第一个结果都是结束，然后您的后续结果都是连接，并且每个子集都正确排序)。

您对此数据结构有多少控制权？您也许能够重组历史记录表，以便只有一个日期和 JOINED 或 END 历史记录类型...您也许能够创建 join_date 和 end_date 的 View 并对其进行排序...

<小时/>

根据您在问题中的内容，我编写了以下 DDL 和数据:

create table players (
    player_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    player_nickname VARCHAR(255) NOT NULL UNIQUE
);

create table teams (
    team_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    team_name VARCHAR(255) NOT NULL UNIQUE
);

create table history (
    history_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    history_user_id INT NOT NULL, history_team_id INT NOT NULL, 
    history_join_date DATETIME NOT NULL, 
    history_end_date DATETIME NOT NULL DEFAULT "0000-00-00 00:00:00"
);

insert into players VALUES 
    (1,'Folha'),
    (2,'mancini'),
    (3,'PatoDaOldSchool'),
    (4,'Master'),
    (5,'AceR'),
    (6,'Nasri'),
    (7,'Lloyd Banks');

insert into teams VALUES 
    (1,'Portuguese Haxball Team'),
    (2,'United'),
    (3,'Reign In Power'),
    (4,'Born To Win'),
    (5,'Porto Club of Haxball'),
    (6,'ARRIBA');

insert into history VALUES 
    (DEFAULT,1,1,'2012-08-01 00:04','2012-08-22 23:05'),
    (DEFAULT,2,2,'2012-08-21 19:04','2012-08-22 00:25'),
    (DEFAULT,3,3,'2012-08-19 01:29','2012-08-21 01:29'),
    (DEFAULT,4,4,'2012-08-22 23:37',DEFAULT),
    (DEFAULT,5,4,'2012-08-22 23:28',DEFAULT),
    (DEFAULT,6,5,'2012-08-22 23:08',DEFAULT),
    (DEFAULT,7,6,'2012-08-22 18:53',DEFAULT);

<小时/>

解决方案一 - 历史事件 View

这显然不是唯一的解决方案(您必须评估适合您需求的选项，但您可以在 MySQL 中为您的历史事件创建一个 View 并加入它并使用它进行排序，类似于以下内容:

create view historyevent (
    event_user_id,
    event_team_id,
    event_date,
    event_type
) AS
    SELECT 
        history_user_id,
        history_team_id,
        history_join_date,
        'JOIN' 
    FROM history
    UNION
    SELECT
        history_user_id,
        history_team_id,
        history_end_date,
        'END'
    FROM history 
    WHERE history_end_date <> "0000-00-00 00:00:00";

您的选择将变为:

SELECT 
    player_id,
    player_nickname,
    team_id,
    team_name,
    event_date,
    event_type
FROM players
INNER JOIN historyevent
        ON historyevent.event_user_id = players.player_id
INNER JOIN teams
        ON historyevent.event_team_id = teams.team_id
ORDER BY 
    event_date DESC;

这样做的好处是您可以为同一玩家同时加入和离开。

<小时/>

解决方案二 - 伪列。使用 IF 结构选择一列或另一列。

SELECT 
    player_id,
    player_nickname,
    team_id,
    team_name,
    history_join_date,
    history_end_date,
    IF(history_end_date>history_join_date,history_end_date,history_join_date) as order_date
FROM 
    players
    INNER JOIN history
        ON history.history_user_id = players.player_id
    INNER JOIN teams
        ON history.history_team_id = teams.team_id
ORDER BY 
    order_date DESC;

根据@Barmar的答案构建，您还可以使用 GREATEST()选择最大的论据。 ( MAX() 是一个分组函数......实际上不是您正在寻找的)