MySQL使用虚拟列作为连接点? [英] MySQL Use Virtual Columns as Join Points?

问题描述

我有以下三个表格:

每月:

|  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:

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并非有效的日期时间，它基本上需要创建多个虚拟列，而我现在与此类似，并且被卡住了:

The query I'm attempting to write will show what memb_type the user was the first two months of them joining. The tricky part is monthly.run_day is not a valid datetime, and it basically requires creating multiple virtual columns I have something along the lines of this right now and I'm stuck:

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)生成的数据并将其联接回到在run_day上联接的monthly表中(SUBSTRING(monthly.run_day, 1, 1)我们需要匹配月份中的值吗?到run_day)，但是我不知道如何将first_month_memb_type和second_month_memb_type两次连接到monthly表中.

I'm not exactly sure how to use the data generated from MONTH(user.join_date + INTERVAL 1 MONTH) and join that back to the monthly table joined on the run_day (SUBSTRING(monthly.run_day, 1, 1) Does what we need to match the value from the month to the run_day) But I don't know how to join that back to the monthly table twice for the first_month_memb_type and the second_month_memb_type.

根据需要，期望的输出如下(为简便起见，缩写为):

Based on the needs the desired output would be as follows (Abbreviated for brevity):

| 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     |

有什么想法可以做到这一点吗?

Any ideas how to accomplish this?

推荐答案

可以将这些函数用作连接谓词，这意味着我们只需要额外花费两次，即可将第一个月和第二个月全部行，并且只有一次-而不是每个u_id为两行.

It's okay to use those functions as the join predicates, which means we just need to join a couple of extra times to get your first and second months all in a row, and only once - instead of two rows per 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

演示小提琴

注意:如果您的表变得特别大-将函数的结果联接起来并不是一件很有趣的事情，在这种情况下，您不妨考虑将这些虚拟"列(而不是虚拟的)列为索引，然后对其进行索引.

Note: if your table gets particularly large - it's not going to be much fun joining on the results of functions, in which case you may wish to consider making those 'virtual' columns, not so virtual, and then indexing them.

修改 正如评论中指出的那样，如果run_day是一个数字字段，则使用数学而不是子字符串可能会有所不同:

edit As pointed out in the comments, it may make a slight difference if run_day is a numeric field, to use math rather than substring:

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

这篇关于MySQL使用虚拟列作为连接点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！