mysql - 如何减去从表中选择的两个值

Question

我有一个 MySQL 表，如下所示:

CREATE TABLE IF NOT EXISTS `ladderStandard` (
  `charId` mediumint(8) unsigned NOT NULL,
  `poeRank` smallint(5) unsigned NOT NULL,
  `lvl` tinyint(3) unsigned NOT NULL,
  `exp` int(10) unsigned NOT NULL,
  `ladderTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `ladderStandard` (`charId`, `poeRank`, `lvl`, `exp`, `ladderTime`) VALUES
(10000, 10000, 91, 2108226870, '2015-02-06 23:37:11'),
(10001, 10001, 91, 2108221545, '2015-02-06 23:37:11'),
(10002, 10002, 91, 2108219833, '2015-02-06 23:37:11'),
(10003, 10003, 91, 2108192924, '2015-02-06 23:37:11'),
(10004, 10004, 91, 2108154502, '2015-02-06 23:37:11'),
(10005, 10005, 91, 2108153763, '2015-02-06 23:37:11'),
(10000, 9998, 91, 2108226870, '2015-02-06 23:58:21'),
(10001, 9999, 91, 2108221545, '2015-02-06 23:58:21'),
(10002, 10000, 91, 2108219833, '2015-02-06 23:58:21'),
(10003, 10001, 91, 2108192924, '2015-02-06 23:58:21'),
(10004, 10002, 91, 2108154502, '2015-02-06 23:58:21'),
(10005, 10003, 91, 2108153763, '2015-02-06 23:58:21'),

我有两个疑问:

SELECT charId, exp FROM ladderStandard WHERE ladderTime = '2015-02-06 23:37:11';
SELECT charId, exp FROM ladderStandard WHERE ladderTime = '2015-02-06 23:58:21';

现在我想减去这两个查询以获得 charId 和 exp.from.first.query - exp.from.second.query

这是 sqlfiddle.com 上的示例.

Answer 1

您需要连接两个查询然后进行数学计算

SELECT a.charid,
       a.exp - b.exp
FROM   (SELECT charId,
               exp
        FROM   ladderStandard
        WHERE  ladderTime = '2015-02-06 23:37:11') a
       JOIN (SELECT charId,
                    exp
             FROM   ladderStandard
             WHERE  ladderTime = '2015-02-06 23:58:21') b
         ON a.charid = b.charid 

SqlFiddle Demo