如何查看SQL Server中COALESCE函数返回结果的来源字段？

解决方法 #

你可以搭配CASE条件判断语句实现需求，和原有COALESCE的取值优先级完全一致，直接返回两列结果，分别对应取值内容和来源字段名，SQL写法如下：

SELECT 
  -- 取值结果列，和原有逻辑完全一致
  COALESCE(FirstName, Surname, DateOfBirth, id, Postcode) AS result_value,
  -- 来源字段名列，按优先级匹配第一个非空字段的名称
  CASE
    WHEN FirstName IS NOT NULL THEN 'FirstName'
    WHEN Surname IS NOT NULL THEN 'Surname'
    WHEN DateOfBirth IS NOT NULL THEN 'DateOfBirth'
    WHEN id IS NOT NULL THEN 'id'
    WHEN Postcode IS NOT NULL THEN 'Postcode'
  END AS source_field
FROM table1

注意事项 #

如果出现字段类型不兼容的报错，是因为COALESCE要求所有入参数据类型统一，你可以把所有字段统一转为字符串类型即可，修改后写法：

SELECT 
  COALESCE(
    CAST(FirstName AS VARCHAR(100)),
    CAST(Surname AS VARCHAR(100)),
    CAST(DateOfBirth AS VARCHAR(100)),
    CAST(id AS VARCHAR(100)),
    CAST(Postcode AS VARCHAR(100))
  ) AS result_value,
  CASE
    WHEN FirstName IS NOT NULL THEN 'FirstName'
    WHEN Surname IS NOT NULL THEN 'Surname'
    WHEN DateOfBirth IS NOT NULL THEN 'DateOfBirth'
    WHEN id IS NOT NULL THEN 'id'
    WHEN Postcode IS NOT NULL THEN 'Postcode'
  END AS source_field
FROM table1

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