如何订购此特定的内连接？
现在我正在创建一个在线游戏，其中我列出了玩家的最后一次转会。
处理球员历史记录的表有history_join_date和history_end_date列。
当history_end_date被填充时，它意味着球员离开了俱乐部，当它像默认值(0000-00-00 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']));
}正如您所看到的，在转移顺序中，日期没有严格遵循(22-Aug => 21-Aug => 22-Aug)。
SQL中遗漏了什么？
致以问候！
回答 2
Stack Overflow用户
发布于 2012-08-23 07:43:55
问题是您基于两个不同的值进行排序。因此，您的结果首先按history_end_date排序，当结束日期相等时(即，当它是默认值)时，再按history_join_date排序
(请注意，您的第一个结果是所有ends，然后您的后续结果都是joins，并且每个子集都是正确排序的)。
你对这个数据结构有多大的控制力？您可以重新构造历史记录表，使其只有一个日期，历史记录类型为JOINED或END...您也许能够查看joined_date和end_date并对其进行排序……
根据问题中的内容，我编写了以下DDL & Data：
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);解决方案一-历史事件视图
这显然不是唯一的解决方案(您必须评估适合您的需求的选项，但您可以在MySQL中为您的历史事件创建一个视图，并加入它并使用它进行排序，如下所示：
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()是一个分组函数...实际上并不是你想要的)
Stack Overflow用户
发布于 2012-08-23 11:12:34
我想你想要的是：
ORDER BY MAX(history_join_date, history_end_date)https://stackoverflow.com/questions/12082824
复制相似问题
腾讯云开发者
