mysql - 我可以重命名由内部连接生成的表吗？

Question

我有两个表，它们的结构如下:

表 1:

+----+-----------------------+----------------------+
| id | rank_before_promotion | rank_after_promotion |
+----+-----------------------+----------------------+
| 01 |         GR01          |         GR02         |
| 02 |         GR03          |         GR04         |
| 03 |         GR03          |         GR05         |
| 04 |         GR06          |         GR07         |
+---------------------------------------------------+

表 2:

+------+------+
| rank | area | 
+------+------+
| GR01 | 1000 |    
| GR02 | 1200 |     
| GR03 | 1400 |     
| GR04 | 1600 | 
+-------------+

我想用这个 sql 计算 rank_before_promotion 的面积和 rank_after_promotion 的面积之间的差异:

     SELECT id, rank_before_promoton, rank_after_promotion, tmp1.area-tmp2.area 
     FROM (table1 INNER JOIN table2 ON rank_before_promotion = rank) AS tmp1,
          (table1 INNER JOIN table2 ON rank_after_promotion = rank) AS tmp2;

当我运行此 sql 时出现一些错误。我可以重命名连接生成的表吗？有没有打印join生成的表的方法？

所需的输出将是:

+----+-----------------------+----------------------+--------+
| id | rank_before_promotion | rank_after_promotion | differ |
+----+-----------------------+----------------------+--------+
| 01 |         GR01          |         GR02         |   200  |
| 02 |         GR03          |         GR04         |   200  |
| 03 |         GR03          |         GR05         |   400  |
| 04 |         GR06          |         GR07         |   200  |
+---------------------------------------------------+--------+

Answer 1

我不完全理解你试图从另一个字符串中减去一个字符串来实现什么，但这是你执行的查询的一个版本:

 SELECT id, rank_before_promotion, rank_after_promotion, b.area-c.area 
 FROM table1 a
 INNER JOIN table2 b
    ON a.rank_before_promotion = b.rank
 INNER JOIN table2 c
    ON a.rank_after_promotion = c.rank;

演示:SQL Fiddle