如何使用 Update in Case 語句和列作為別名-PHP中文網路問答

文章專題學習下載問答程式設計字典遊戲最近更新

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

如何使用 Update in Case 語句和列作為別名

P粉214089349 2023-09-05 17:54:59

455

我有一個名為 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

全部回覆 (2)

P粉2564870772023-09-06 14:26:54 2樓

新增產生欄位（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

#（我沒有得到相同的工作日，也許是區域設定？）

點贊+0

新增回覆

P粉0356005552023-09-06 14:04:15 1樓

您上述的查詢已經差不多了。您只需要新增更新語句即可。

下面的查詢應該適合您。

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;

點贊+0

新增回覆