mysql - 连接年和周函数

Question

mysql> select concat(yearval,weekval) as val1,concat(year(curdate()),week(curdate())) as val2 from wbr_weekly;
+-------+--------+
| val1  | val2   |
+-------+--------+
| 20161 | 201614 |
| 20162 | 201614 |
| 20163 | 201614 |
| 20164 | 201614 |
| 20165 | 201614 |
| 20161 | 201614 |
| 20162 | 201614 |
| 20163 | 201614 |
| 20164 | 201614 |
| 20165 | 201614 |
| 20161 | 201614 |
| 20162 | 201614 |
| 20163 | 201614 |
| 20164 | 201614 |
| 20165 | 201614 |
+-------+--------+
15 rows in set (0.00 sec)

和

mysql> select concat(yearval,weekval) as val1 from wbr_weekly where concat(yearval,weekval) < concat(year(curdate()),week(curdate()));
+-------+
| val1  |
+-------+
| 20161 |
| 20161 |
| 20161 |
+-------+
3 rows in set (0.00 sec)

我的目标是返回少于当前周的所有周。我认为第二个查询会做我想要的事情。但事实并非如此。有人可以解释一下我的查询出了什么问题吗？

Answer 1

在字符串比较中，“20161”、“20162”等在字典顺序上始终小于“201614”。您需要用前导零填充周数才能进行比较:“201601”、“201602”等:

select concat(yearval,weekval) as val1 from wbr_weekly 
    where concat(yearval, right(concat('0', weekval), 2)) 
      < concat(year(curdate()),week(curdate()));