SQL中如何利用现有列值生成动态列字段

不需要用CASE语句，你要的是固定格式的字符串拼接，直接用对应数据库的字符串拼接函数即可。以下是主流数据库的实现代码：

MySQL/MariaDB #

使用CONCAT()函数拼接字段：

Select 
   mt.First_name,
   mt.Last_name as OLD_Last_name,
   ot.Last_name as New_Last_name,
   ot.Date as Update_Date,
   CONCAT(mt.First_name, ' ', ot.Last_name, ', nee ', mt.Last_name, ' changed their name on ', ot.Date) as Name_Change_Description
from maintable as mt
JOIN othertable as ot on mt.id=ot.id

SQL Server #

可以用CONCAT()函数（兼容2012及以上版本），或者直接用+运算符：

Select 
   mt.First_name,
   mt.Last_name as OLD_Last_name,
   ot.Last_name as New_Last_name,
   ot.Date as Update_Date,
   CONCAT(mt.First_name, ' ', ot.Last_name, ', nee ', mt.Last_name, ' changed their name on ', ot.Date) as Name_Change_Description
   -- 或者用+：mt.First_name + ' ' + ot.Last_name + ', nee ' + mt.Last_name + ' changed their name on ' + CONVERT(varchar, ot.Date)
from maintable as mt
JOIN othertable as ot on mt.id=ot.id

注意：用+时如果日期类型不是字符串，需要先转换为字符类型，比如用CONVERT()或者CAST()

PostgreSQL #

用||运算符或者CONCAT()函数：

Select 
   mt.First_name,
   mt.Last_name as OLD_Last_name,
   ot.Last_name as New_Last_name,
   ot.Date as Update_Date,
   mt.First_name || ' ' || ot.Last_name || ', nee ' || mt.Last_name || ' changed their name on ' || ot.Date as Name_Change_Description
   -- 或者用CONCAT：CONCAT(mt.First_name, ' ', ot.Last_name, ', nee ', mt.Last_name, ' changed their name on ', ot.Date)
from maintable as mt
JOIN othertable as ot on mt.id=ot.id

Oracle #

用||运算符或者CONCAT()函数（CONCAT()只支持两个参数，多参数需要嵌套）：

Select 
   mt.First_name,
   mt.Last_name as OLD_Last_name,
   ot.Last_name as New_Last_name,
   ot.Date as Update_Date,
   mt.First_name || ' ' || ot.Last_name || ', nee ' || mt.Last_name || ' changed their name on ' || TO_CHAR(ot.Date, 'YYYY-MM-DD') as Name_Change_Description
from maintable as mt
JOIN othertable as ot on mt.id=ot.id

注意：Oracle中日期转字符串建议用TO_CHAR()指定格式，避免默认格式不符合需求

关键说明 #

你之前用CASE语句出错，是因为CASE用于条件分支判断，而这里是固定格式的字符串拼接，完全不需要条件判断，直接把字段和固定文本按顺序拼接即可。

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