mysql 根据时间戳设置别名

osh3o9ms 于 2022-11-21 发布在 Mysql

关注(0)|答案(2)|浏览(108)

我尝试为列challenge_name设置别名，尤其是遇到基于时间戳/日期的“challenge”（如challenge 1、challenge 2等）时。如果不是“challenge”，我希望将列名设置为与challenge_name相同
这是我目前的输入

+-----------+----------------+
|   date    | challenge_name |
+-----------+----------------+
| 1/11/2022 | find the bug   |
| 1/12/2022 | challenge      |
| 1/13/2022 | kill pill      |
| 1/14/2022 | hello copter   |
| 1/15/2022 | challenge      |
| 1/16/2022 | miami          |
| 1/17/2022 | hello world    |
| 1/18/2022 | challenge      |
| 1/19/2022 | dominc         |
| 1/20/2022 | challenge      |
+-----------+----------------+

这是我的预期输出

+-----------+----------------+--------------+
|   date    | challenge_name | updated_name |
+-----------+----------------+--------------+
| 1/11/2022 | find the bug   | find the bug |
| 1/12/2022 | challenge      | challenge 1  |
| 1/13/2022 | kill pill      | kill pill    |
| 1/14/2022 | hello copter   | hello copter |
| 1/15/2022 | challenge      | challenge 2  |
| 1/16/2022 | miami          | miami        |
| 1/17/2022 | hello world    | hello world  |
| 1/18/2022 | challenge      | challenge 3  |
| 1/19/2022 | dominc         | dominc       |
| 1/20/2022 | challenge      | challenge 4  |
+-----------+----------------+--------------+

mysql

来源：https://stackoverflow.com/questions/74436515/setting-alias-name-as-per-timestamp

2条答案

按热度按时间

cwdobuhd1#

Rank将为每个特定的challenge_name组分配一个等级，我没有只过滤掉challenge_name = challenge时的值，因为它们是根据case条件过滤的

select `date`,
       challenge_name,
       case  when challenge_name = 'challenge' then concat('challenge ', rank() over(partition by challenge_name order by `date` asc )) 
             else challenge_name end  as challenge_name                               
  from test
  order by `date` asc ;

https://dbfiddle.uk/oRJ0bQh7

赞(0）回复(0）举报 2022-11-21

7cwmlq892#

以下方法可能有效：

-- Fuzzy match
CASE 
    WHEN REGEXP_LIKE(challenge_name, 'challenge') THEN 
        CONCAT('challenge ', 
               ROW_NUMBER() OVER (
                   PARTITION BY REGEXP_LIKE(challenge_name, 'challenge')
                   ORDER BY date
               )
        )
    ELSE challenge_name
END AS updated_name

编辑

在窗口函数中添加了order by子句

赞(0）回复(0）举报 2022-11-21

我来回答

mysql 根据时间戳设置别名

2条答案

相关问题

热门标签

最新问答