MySQL 收集数据 JOIN

Question

我目前有这两张表

CREATE TABLE IF NOT EXISTS `players` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `group_id` int(11) NOT NULL DEFAULT '1',
  `account_id` int(11) NOT NULL DEFAULT '0',
  `level` int(11) NOT NULL DEFAULT '1',
  `vocation` int(11) NOT NULL DEFAULT '0',
  ...
) ENGINE=InnoDB;

和

CREATE TABLE IF NOT EXISTS `player_storage` (
  `player_id` int(11) NOT NULL DEFAULT '0',
  `key` int(10) unsigned NOT NULL DEFAULT '0',
  `value` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`player_id`,`key`),
  FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

我要执行下面的查询

SELECT
  a.value, 
  b.name,
  b.level, 
  b.vocation
FROM
  player_storage a,
  players b
  LEFT JOIN players_online c AS t
     ON c.player_id = b.id
WHERE
  a.player_id = b.id
  AND a.key = 6723
ORDER BY a.value DESC LIMIT 20

我还想看看该记录是否存在于表 players_online 上

CREATE TABLE IF NOT EXISTS `players_online` (
  `player_id` int(11) NOT NULL,
  PRIMARY KEY (`player_id`)
) ENGINE=MEMORY;

但是我的查询似乎有效，但如果 player_id 存在于 players_online 上则无法获取

我只得到字段值，名字，等级，职业，但没有得到与 players_online 相关的单个字段

Answer 1

您应该添加一个与“在线”表相关的字段。

例如:

SELECT ...,
IF(c.player_id IS NULL, 'offline', 'online') online
FROM ...

当然，您必须解决 players_online c As t 中的双别名问题

SQLFiddle