从三个简单查询创建复杂查询 [英] Create complex query from three simple queries

问题描述

小背景:我大部分时间都在研究这个问题，我很高兴我几乎找到了解决方案，只是我似乎无法将最终的 MySQL 查询放在一起.这都是关于 MySQL 语法的，所以我相信不需要给出表模式等.

Small background: I've been working on this problem most of the day already, and I'm very excited that I have almost found a solution, only I cannot seem to put the final MySQL query together. This is all about MySQL syntax, so I believe that no table schemas, etc. need to be given.

查询部分:

1)

SELECT id, globalId, date, serverId, gamemodeId, mapId FROM levelsloaded

2)

(
(SELECT id, globalId, date, serverId, playerId, 'playerjoins' AS origin 
FROM playerjoins WHERE playerId = 2224 AND date <= levelsloaded.date 
ORDER BY date DESC)
UNION ALL
(SELECT id, globalId, date, serverId, playerId, 'playerleaves' AS origin 
FROM playerleaves WHERE playerId = 2224 AND date <= levelsloaded.date 
ORDER BY date DESC)
ORDER BY date DESC LIMIT 1) below

3)

(
(SELECT id, globalId, date, serverId, playerId, 'playerjoins' AS origin 
FROM playerjoins WHERE playerId = 2224 AND date >= levelsloaded.date 
ORDER BY date ASC)
UNION ALL
(SELECT id, globalId, date, serverId, playerId, 'playerleaves' AS origin 
FROM playerleaves WHERE playerId = 2224 AND date >= levelsloaded.date 
ORDER BY date ASC)
ORDER BY date ASC LIMIT 1) above

如果需要，这里是模式:

Here are the schemas anyway if they're needed:

CREATE TABLE `levelsloaded` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `globalId` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `serverId` int(11) NOT NULL,
  `gamemodeId` int(11) NOT NULL,
  `mapId` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `globalId` (`globalId`),
  KEY `date` (`date`),
  KEY `serverId` (`serverId`),
  KEY `gamemodeId` (`gamemodeId`),
  KEY `mapId` (`mapId`),
  CONSTRAINT `levelsloaded_ibfk_1` FOREIGN KEY (`serverId`) REFERENCES `servers` (`serverId`),
  CONSTRAINT `levelsloaded_ibfk_2` FOREIGN KEY (`gamemodeId`) REFERENCES `gamemodes` (`gamemodeId`),
  CONSTRAINT `levelsloaded_ibfk_3` FOREIGN KEY (`mapId`) REFERENCES `maps` (`mapId`)
) ENGINE=InnoDB AUTO_INCREMENT=1104 DEFAULT CHARSET=latin1

<小时>

CREATE TABLE `playerjoins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `globalId` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `serverId` int(11) NOT NULL,
  `playerId` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `globalId` (`globalId`),
  KEY `date` (`date`),
  KEY `serverId` (`serverId`),
  KEY `playerId` (`playerId`),
  CONSTRAINT `playerjoins_ibfk_1` FOREIGN KEY (`serverId`) REFERENCES `servers` (`serverId`),
  CONSTRAINT `playerjoins_ibfk_2` FOREIGN KEY (`playerId`) REFERENCES `players` (`playerId`)
) ENGINE=InnoDB AUTO_INCREMENT=64983 DEFAULT CHARSET=latin1

<小时>

CREATE TABLE `playerleaves` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `globalId` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `serverId` int(11) NOT NULL,
  `playerId` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `globalId` (`globalId`),
  KEY `date` (`date`),
  KEY `serverId` (`serverId`),
  KEY `playerId` (`playerId`),
  CONSTRAINT `playerleaves_ibfk_1` FOREIGN KEY (`serverId`) REFERENCES `servers` (`serverId`),
  CONSTRAINT `playerleaves_ibfk_2` FOREIGN KEY (`playerId`) REFERENCES `players` (`playerId`)
) ENGINE=InnoDB AUTO_INCREMENT=45676 DEFAULT CHARSET=latin1

现在进入问题:

我想以这样的方式将查询部分 (1) 与部分 (2) 和 (3) 结合起来，这样 levelsloaded 中的一行只会在 below.origin = ' 时返回playerjoin' AND above.origin = 'playerleave'.我目前被困在将表格和 IF 部分结合起来.

I want to combine query part (1) with parts (2) and (3) in such a way that a row from levelsloaded will only be returned if below.origin = 'playerjoin' AND above.origin = 'playerleave'. I am stuck at the moment with combining the tables and the IF-part.

推荐答案

虽然这不是最有效的方法，但您可以这样做.

Though it is not the most efficient way you can do it this way.

SELECT id, globalId, date, serverId, gamemodeId, mapId 
FROM levelsloaded l
    JOIN above a ON 1 = 1
    JOIN below b ON 1 = 1
WHERE b.origin = 'playerjoin' AND a.origin = 'playerleave'

这篇关于从三个简单查询创建复杂查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！