SQLite技术问题：如何获取子查询定位列的对应行值

方法1：CASE表达式（适合列数固定且较少的场景） #

如果你的数字列只有1、3、6、9这几个，可以直接用CASE匹配子查询得到的列名，返回对应列的值：

SELECT
  CASE (
    SELECT CAST(name AS TEXT)
    FROM pragma_table_info('0')
    WHERE CAST(name AS REAL) <= 9
    ORDER BY CAST(name AS REAL) DESC
    LIMIT 1
  )
    WHEN '1' THEN "1"
    WHEN '3' THEN "3"
    WHEN '6' THEN "6"
    WHEN '9' THEN "9"
  END AS target_value
FROM "0"
WHERE "column2" = 'some_sh';

方法2：动态SQL（通用场景，适配任意列数） #

SQLite 3.39.0及以上版本支持EXECUTE语句，能通过拼接SQL实现动态列查询：

WITH target_col AS (
  SELECT CAST(name AS TEXT) AS col_name
  FROM pragma_table_info('0')
  WHERE CAST(name AS REAL) <= 9
  ORDER BY CAST(name AS REAL) DESC
  LIMIT 1
)
EXECUTE IMMEDIATE 'SELECT "' || col_name || '" FROM "0" WHERE "column2" = ''some_sh'''
FROM target_col;

要是用代码实现（比如Python），可以分两步操作：先查询得到目标列名，再拼接成查询语句执行：

import sqlite3

conn = sqlite3.connect('你的数据库名.db')
cur = conn.cursor()

# 第一步：获取符合条件的最大数字列名
cur.execute("""
  SELECT CAST(name AS TEXT)
  FROM pragma_table_info('0')
  WHERE CAST(name AS REAL) <= 9
  ORDER BY CAST(name AS REAL) DESC
  LIMIT 1
""")
col_name = cur.fetchone()[0]

# 第二步：查询该列对应的值
cur.execute(f'SELECT "{col_name}" FROM "0" WHERE "column2" = ?', ('some_sh',))
result = cur.fetchone()[0]
print(result)

conn.close()

原查询问题说明 #

你原来的查询返回的是子查询得到的字符串'9'，而不是引用名为'9'的列。静态SQL在编译阶段就确定了要查询的列，没法用运行时的子查询结果动态替换列名，所以必须用上面两种方法绕开这个限制。

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