如何使用 Update in Case 语句和列作为别名
P粉214089349
P粉214089349 2023-09-05 17:54:59
0
2
458

我有一个名为 sales_data 的表,其中有 3 列(id int、udf varchar(20)、date_of_sale datetime)。 我试图通过将时间调整为 6 小时来查找 date_of_sale 列的工作日,现在我必须将 udf 列更新为与 date_of_sale 对应的工作日。我有一个选择查询的想法,但如何更新 udf 列?

select weekday(subtime(s.date_of_sale ,'6:0:0')) as putdata, CASE WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=0 THEN 'Sunday' WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=1 THEN 'Monday' WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=2 THEN 'Tuesday' WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=3 THEN 'Wednesday' WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=4 THEN 'Thursday' WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=5 THEN 'Friday' WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=6 THEN 'Saturday' END as udf from sales_data s;

P粉214089349
P粉214089349

全部回复 (2)
P粉256487077

添加生成列https://dev.mysql.com/doc/refman/8.0/en/create-table- generated-columns.html)以简化处理,并避免数据不一致:

alter table sales_data add weekday varchar(10) GENERATED ALWAYS AS (CASE WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=0 THEN 'Sunday' WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=1 THEN 'Monday' WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=2 THEN 'Tuesday' WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=3 THEN 'Wednesday' WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=4 THEN 'Thursday' WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=5 THEN 'Friday' WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=6 THEN 'Saturday' END );

查看演示:https://dbfiddle.uk/2d5iIvBv

(我没有得到相同的工作日,也许是区域设置?)

    P粉035600555

    您的上述查询已经差不多了。您只需要添加更新语句即可。

    下面的查询应该适合您。

    update sales_data set udf = case when weekday(subtime(date_of_sale,'6:0:0')) = 0 then 'Sunday' when weekday(subtime(date_of_sale,'6:0:0')) = 1 then 'Monday' when weekday(subtime(date_of_sale,'6:0:0')) = 2 then 'Tuesday' when weekday(subtime(date_of_sale,'6:0:0')) = 3 then 'Wednesday' when weekday(subtime(date_of_sale,'6:0:0')) = 4 then 'Thursday' when weekday(subtime(date_of_sale,'6:0:0')) = 5 then 'Friday' when weekday(subtime(date_of_sale,'6:0:0')) = 6 then 'Saturday' end;
      最新下载
      更多>
      网站特效
      网站源码
      网站素材
      前端模板
      关于我们 免责声明 Sitemap
      PHP中文网:公益在线PHP培训,帮助PHP学习者快速成长!