MySQL中使用CASE语句出现输入不匹配错误，请求技术协助

你的SQL报错核心是两个MySQL语法规范问题导致的：

• 字符串常量需用单引号：MySQL默认不认可双引号表示字符串，双引号会被解析成列名/表名这类标识符，所以你写的("women","girls","cloth")、"BED"这类双引号包裹的内容，必须改成单引号。
• table是保留关键字：直接用table作为表名会触发语法冲突，需要用反引号`把它包裹起来。

修正后的SQL如下：

with first_cte as
(
  select * from `table` 
  where category in ('women','girls','cloth')
),
second_cte as 
(
  select *, case when upper(product_type)='BED' and upper(gender)='MALE' THEN 'Male'
  when upper(product_type)='BED' and upper(gender)='FEMALE' THEN 'Female'
  when upper(product_type)='BED' and upper(gender)='UNISEX' THEN 'Unisex'
  else 'Not defined'
  end as suggestion from first_cte
)
select *, row_number() over(partition by suggestion order by suggestion) as row_num from second_cte;

额外补充：row_number()里的order by suggestion逻辑上可行，但如果想让排序结果更稳定，建议替换成有唯一值的列（比如主键），避免相同suggestion的行排序结果随机变化。

内容的提问来源于stack exchange，提问作者Bielsa