MySQL使用虚拟列作为连接点？

Peter 发表于 Dev

彼得

我有以下三个表：

每月：

|  u_id   |  memb_type  |  run_day |
------------------------------------
|   1     |       1     |    410   |
|   2     |       1     |    410   |
|   1     |       2     |    510   |
|   2     |       1     |    510   |
|   1     |       2     |    610   |
|   2     |       1     |    610   |

memb_types：

|    id   |      name      |
----------------------------
|    1    |      member    |
|    2    |  active_member |

用户：

|   id    |   join_date    |
----------------------------
|    1    |    2015-03-01  |
|    2    |    2015-04-04  |

我尝试编写的查询将显示memb_type用户加入后的头两个月是什么。棘手的部分monthly.run_day不是有效的日期时间，它基本上需要创建多个虚拟列，我现在与此类似，并且遇到了麻烦：

SELECT
      user.id,
      user.join_date,
      MONTH(user.join_date + INTERVAL 1 MONTH) AS `first_month`,
      "???" AS `first_month_memb_type`,
      MONTH(user.join_date + INTERVAL 2 MONTH) AS `second_month`,
      "???" AS `second_month_memb_type`
 FROM 
      user
 INNER JOIN
      monthly
 ON 
      user.id = monthly.u_id
 INNER JOIN
      memb_types
 ON
      monthly.memb_type = memb_types.id

我不确定如何使用生成的数据MONTH(user.join_date + INTERVAL 1 MONTH)并将其重新连接到连接的monthly表上run_day（SUBSTRING(monthly.run_day, 1, 1)我们需要将月份中的值匹配到上的值run_day），但是我不知道如何将其重新连接到该monthly表两次用于first_month_memb_type和second_month_memb_type。

根据需求，期望的输出如下（为简洁起见，缩写为）：

| id | join_date  | fm | fm_type | sm |    sm_type    |
-------------------------------------------------------
| 1  | 2015-03-01 | 4  |  member |  5 | active_member |
| 2  | 2015-04-04 | 5  |  member |  6 |    member     |

任何想法如何做到这一点？

帕拉

可以将这些函数用作连接谓词，这意味着我们只需要连接几次额外的时间就可以连续获取第一个月和第二个月，而且一次即可-而不是每个u_id包含两行。

   select u.id, 
       u.join_date, 
       month(u.join_date + interval 1 month) fm, 
       mt1.name fm_type,
       month(u.join_date + interval 2 month) 2m,
       mt2.name sm_type
  from user u
    inner join monthly m1
      on month(u.join_date + interval 1 month) = substring(m1.run_day, 1, 1)
        and m1.u_id = u.id
    inner join monthly m2
      on month(u.join_date + interval 2 month) = substring(m2.run_day, 1, 1)
        and m2.u_id = u.id
    inner join memb_types mt1
      on m1.memb_type = mt1.id
    inner join memb_types mt2
      on m2.memb_type = mt2.id

演示小提琴

注意：如果您的表变得特别大-将函数的结果连接起来并不是一件很有趣的事情，在这种情况下，您可能希望考虑将那些“虚拟”列（而不是虚拟的）列为索引，然后对其进行索引。

编辑如评论中所指出，如果run_day是一个数字字段，则使用数学而不是子字符串可能会有所不同：

select u.id, 
       u.join_date, 
       month(u.join_date + interval 1 month) fm, 
       mt1.name fm_type,
       month(u.join_date + interval 2 month) 2m,
       mt2.name sm_type
  from user u
    inner join monthly m1
      on month(u.join_date + interval 1 month) = floor(m1.run_day / 100)
        and m1.u_id = u.id
    inner join monthly m2
      on month(u.join_date + interval 2 month) = floor(m2.run_day / 100)
        and m2.u_id = u.id
    inner join memb_types mt1
      on m1.memb_type = mt1.id
    inner join memb_types mt2
      on m2.memb_type = mt2.id