mysql 增加虚拟列_MySQL使用虚拟列作为加入点？

I have the three following tables:

monthly:

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

user:

| id | join_date |

----------------------------

| 1 | 2015-03-01 |

| 2 | 2015-04-04 |

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

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?

解决方案

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.

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