mysql - 将带有月份的 varchar 转换为日期

Question

mysql> select * from tr;+------+| mnt  |+------+| jun  || mar  || jan  || aug  |+------+4 rows in set (0.00 sec)mysql> select * from tr1;+------+------+| mnt  | id   |+------+------+| aug  |   11 || jan  |   12 || mar  |   15 || apr  |   16 |+------+------+4 rows in set (0.00 sec)it worked for this query..mysql> select * from tr join tr1 on tr.mnt=tr1.mnt;+------+------+------+| mnt  | mnt  | id   |+------+------+------+| aug  | aug  |   11 || jan  | jan  |   12 || mar  | mar  |   15 |+------+------+------+3 rows in set (0.00 sec)mysql> select * from tr2;+------------+------+| mn         | id   |+------------+------+| 2009-02-14 |   11 || 2009-03-03 |   12 || 2009-08-08 |   12 |+------------+------+3 rows in set (0.00 sec)mysql> insert into tr2 values('2009-01-01',14);Query OK, 1 row affected (0.06 sec)

但它不适用于此..

mysql> select * from tr join tr2 on tr.mnt=MONTHNAME(tr2.mn);Empty set (0.00 sec)

请给出确切的查询..

Answer 1

MONTHNAME(date) 返回一个 varchar，因此“yes” - 您可以比较它们。

考虑这个语法:

select *
from A
join B on B.InvoiceMonth = MONTHNAME(A.Mnth);