mysql - 使用 if 选择输出值的 SQL 字符串连接

Question

有一个名为 blubb 的表，其中包含 3 个字段:id、year 和 week

CREATE TABLE blubb (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `year` int(11) NOT NULL,
  `week` int(11) NOT NULL
);


INSERT INTO blubb (year, week) VALUES (2017, 1);
INSERT INTO blubb (year, week) VALUES (2017, 2);
INSERT INTO blubb (year, week) VALUES (2017, 3);
INSERT INTO blubb (year, week) VALUES (2017, 4);
INSERT INTO blubb (year, week) VALUES (2017, 5);
INSERT INTO blubb (year, week) VALUES (2017, 6);
INSERT INTO blubb (year, week) VALUES (2017, 7);
INSERT INTO blubb (year, week) VALUES (2017, 8);
INSERT INTO blubb (year, week) VALUES (2017, 9);
INSERT INTO blubb (year, week) VALUES (2017,10);
INSERT INTO blubb (year, week) VALUES (2017,11);
INSERT INTO blubb (year, week) VALUES (2017,12);
INSERT INTO blubb (year, week) VALUES (2017,13);
INSERT INTO blubb (year, week) VALUES (2017,14);
INSERT INTO blubb (year, week) VALUES (2017,15);

目标是获得一个 select 语句，该语句将 year 和 week 连接起来，并在 和 之间使用 W这将始终选择两位数的星期。因此，如果周值是 1 我需要 01

我是这样完成的:

SELECT DISTINCT 
    IF(week < 10, CONCAT(year, 'W0', week), CONCAT(year, 'W', week) ) as year_week,
    year,
    week
FROM
    blubb

结果和预期结果:

year_week   year    week
2017W01     2017    1
2017W02     2017    2
2017W03     2017    3
2017W04     2017    4
2017W05     2017    5
2017W06     2017    6
2017W07     2017    7
2017W08     2017    8
2017W09     2017    9
2017W10     2017    10
2017W11     2017    11
2017W12     2017    12
2017W13     2017    13
2017W14     2017    14
2017W15     2017    15

问题是:是否有另一种更好、更干净的方法来解决这个问题？谢谢

SQL Fiddle here

Answer 1

使用LPAD:

SELECT
    CONCAT(year, 'W', LPAD(week, 2, '0'))as year_week,
    year,
    week
FROM
    blubb