SQLite如何获取每行首个非空列的数据及索引？有无更优方案？

嘿，你的思路方向是对的——用coalesce结合列索引来实现需求确实可行，但其实还有更清晰、易维护的替代方案，甚至不同数据库还有专属的高效写法，下面给你拆解一下：

1. 最通用的CASE表达式写法（全数据库兼容） #

如果追求兼容性和可读性，CASE表达式绝对是比嵌套coalesce更直观的选择，尤其是列数较多的时候，逻辑一目了然，后期改起来也不容易出错：

SELECT
  CASE
    WHEN col1 IS NOT NULL THEN col1
    WHEN col2 IS NOT NULL THEN col2
    WHEN col3 IS NOT NULL THEN col3
    -- 按顺序继续添加后续列即可
  END AS first_non_null_value,
  CASE
    WHEN col1 IS NOT NULL THEN 1
    WHEN col2 IS NOT NULL THEN 2
    WHEN col3 IS NOT NULL THEN 3
    -- 对应上面的列索引
  END AS column_index
FROM your_table;

这种写法的好处是每个列的判断和索引映射都清清楚楚，哪怕是新手看也能立刻明白逻辑，后续调整列顺序或者增减列的时候，直接对应修改就行，不会像拼接式coalesce那样容易搞混。

2. 优化后的coalesce写法（简洁且清晰） #

如果你还是偏好coalesce的简洁性，完全不用把索引和数据拼接在一起再拆分——分开获取反而更高效，还能避免数据类型转换的额外开销：

SELECT
  COALESCE(col1, col2, col3, ...) AS first_non_null_value,
  COALESCE(
    CASE WHEN col1 IS NOT NULL THEN 1 END,
    CASE WHEN col2 IS NOT NULL THEN 2 END,
    CASE WHEN col3 IS NOT NULL THEN 3 END,
    -- 对应列的索引判断
  ) AS column_index
FROM your_table;

这里coalesce分别处理值和索引，逻辑清晰，性能也比拼接后拆分要好。

3. 数据库专属的高效方案（适合列多/结构易变的场景） #

不同数据库有一些内置函数能让这个需求实现得更优雅，比如：

• PostgreSQL：可以用row_to_json配合json_each_text动态遍历列，不用硬编码列名，表结构变了也不用改SQL：

  SELECT
    value AS first_non_null_value,
    (key::int) AS column_index
  FROM your_table,
       LATERAL (
         SELECT key, value
         FROM json_each_text(row_to_json(your_table))
         WHERE value IS NOT NULL
         ORDER BY key::int
         LIMIT 1
       ) AS non_null_cols;
  

  注意这里假设你的列名是数字格式（比如col1、col2），如果列名是其他格式，调整ORDER BY的逻辑就行。

• MySQL：可以用FIELD函数配合COALESCE，前提是列顺序要严格对应：

  SELECT
    COALESCE(col1, col2, col3) AS first_non_null_value,
    FIELD(COALESCE(col1, col2, col3), col1, col2, col3) AS column_index
  FROM your_table;
  

  FIELD会返回第一个匹配值的位置，刚好对应列索引，用起来非常简洁。

总结一下 #

• 要兼容所有数据库+可读性拉满：选CASE表达式；
• 要简洁且保留coalesce风格：用分开获取值和索引的版本；
• 列多或者表结构易变，且用特定数据库：选对应数据库的动态遍历方案。

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