如何使用mysql中的case语句将3列数据合并为一个

Question

我有一张名、名、姓的桌子。我想将这3列数据添加到一个完整的名称中，就好像middlename != '‘，那么fullname = want =want，如果lastname != '’，那么fullname =want lastname。我试过如下，但没有影响行。

UPDATE studentprofiles SET fullname = CASE middlename WHEN middlename !=  '' THEN firstname +  ' ' + middlename ELSE firstname END WHERE fullname = ''

怎么做？我必须更新多行。欢迎任何帮助/建议。

Answer 1

问题的解决办法如下：

UPDATE studentprofiles 
    SET fullname = CASE
                WHEN middlename != '' AND lastname != '' THEN concat(firstname,' ',middlename,' ',lastname)
                WHEN middlename != '' AND lastname = '' THEN concat(firstname,' ',middlename)
                WHEN middlename = '' AND lastname != '' THEN concat(firstname,' ',lastname)
                ELSE concat(firstname)
END

Answer 2

您是否尝试过像这样使用AND：

CASE
WHEN middlename != '' AND lastname != '' THEN firstname + middlename + lastname
ELSE ''
END AS fullname

Answer 3

为什么不能简单地连接列数据，比如

UPDATE studentprofiles 
SET fullname = concat(firstname, middlename, lastname);

编辑:在这种情况下可以使用IFNULL()函数，如

UPDATE studentprofiles 
SET fullname = IFNULL(firstname,'') +
               IFNULL(middlename, '') +
               IFNULL(lastname, '');