比较时间戳的值并为它们中的每一个分配一个值，以防它们发生变化

CREATE TABLE operations ( id int auto_increment primary key, time_stamp DATE, product VARCHAR(255), plan_week VARCHAR(255));INSERT INTO operations (time_stamp, product, plan_week)VALUES ("2020-01-01", "Product_A", "CW01"),("2020-01-01", "Product_B", "CW01"),("2020-01-01", "Product_C", "CW01"),("2020-03-15", "Product_A", "CW01"),("2020-03-15", "Product_B", "CW02"),("2020-03-15", "Product_C", "CW02"),("2020-03-15", "Product_D", "CW01");预期结果: time_stamp | product | plan_week | week_switch ---------------|---------------|---------------|----------------- 2020-01-01 | Product_A | CW01 | no 2020-01-01 | Product_B | CW01 | yes 2020-01-01 | Product_C | CW01 | yes | | | 2020-03-15 | Product_A | CW01 | no 2020-03-15 | Product_B | CW02 | yes 2020-03-15 | Product_C | CW02 | yes 2020-03-15 | Product_D | CW01 | no在上面的结果中，我想列出表中的所有 products 并将两个 time_stamps 相互比较.如果一个产品的 plan_week 在两个 time_stamps 之间切换，我希望在名为 week_switch 的附加列中使用 yes 被插入，如果不是，则插入no 这个词.In the above result I want to list all products from the table and compare the two time_stamps to each other. If the plan_week of one product has switched between the both time_stamps I want that in the additional column called week_switch the word yes gets inserted and if not the word no gets inserted.我尝试使用此查询，但无法使其工作:I tried to go with this query but could not make it work:SELECT time_stamp, product, plan_week, (CASE WHEN MIN(plan_week) <> MAX(plan_week) THEN 'yes' ELSE 'no' END) AS week_switchFROM operationsGROUP BY 1,2;我需要改变什么才能得到预期的结果?What do I need to change to get the expected result?推荐答案我只会使用窗口函数:select o.*, (case when min(plan_week) over (partition by product) = max(plan_week) over (partition by product) then 'no' else 'yes' end) as switchedfrom operations o; 这篇关于比较时间戳的值并为它们中的每一个分配一个值，以防它们发生变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！