时间戳

首页 > 解决方案 > 关于case函数的问题是mysql: split value from one field to two

mysql - 关于case函数的问题是mysql: split value from one field to two

问题描述

我有一项业务任务将一个字段分成两个：

SUBSTRING_INDEX(SUBSTRING_INDEX(cal_participants.name, ' ', 1), ' ', -1) 作为会员优先，

这行得通。输出是一个 4 位数字，它是一个区号，例如 7591。

现在，我需要创建一个 CASE 函数来返回实际位置名称，并将位置与团队相关联。

我试过这个：

(CASE 
WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(cal_participants.name, ' ', 1), ' ', -1) as memberfirst = 7591 THEN "Europe"
END)

而且我需要在上述位置创建一个别名（当 7591 是欧洲时，然后“欧洲作为团队名称）

SELECT 

cal_participants.participant_id as "member_id",

cal_participants.name as "member_name",

cal_meeting.meeting_id as "reference",

cal_meeting.created_at as "date",

cal_meeting.startdatetime as "start time",

cal_meeting.enddatetime as "end time",

cal_meeting.status as "status",

cal_meeting.deleted_at as "deleted time",

campaigns.name as "campaign name",

SUBSTRING_INDEX(SUBSTRING_INDEX(cal_participants.name, ' ', 1), ' ', -1) as memberfirst,

(CASE 
WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(cal_participants.name, ' ', 1), ' ', -1) as memberfirst = 7591 THEN "Europe"
END)


FROM cal_meeting

JOIN cal_participants on cal_meeting.participant_id = cal_participants.participant_id

JOIN campaigns on cal_meeting.campaign = campaigns.id

我想要的是：

1) memberfirst 为 7591 时返回“Europe”的函数

2）将那个（欧洲）与一个团队联系起来

我收到此错误：错误：（pymysql.err.ProgrammingError）（1064，u'您的 SQL 语法有错误；请查看与您的 MySQL 服务器版本相对应的手册，以获取在 \'as memberfirst = 附近使用的正确语法7591 THEN "Test"\nEND)\n\nFROM cal_meeting\n\nJOIN cal_participants \' at line 18')

标签： mysqlsqlcase

解决方案

您不需要为 case 内的列提供别名 

(CASE 
WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(cal_participants.name, ' ', 1), ' ', -1) = 7591 
   THEN "Europe"
END)

最终您可以在结束时添加别名 

(CASE 
WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(cal_participants.name, ' ', 1), ' ', -1) = 7591 
   THEN "Europe"
END) as memberfirst

推荐阅读