具有可变列名的MySQL选择查询

Question

我正在寻找一种最简单的解决方案来根据另一个查询的结果查询一个表。

Table 1:
id    rslt  
-----------
1      r1  
2      r2   
3      r3 

Table 2:
  r1  r2  r3  
 ------------------
  a1  b1  c1
  a2  b2  c2
  a2  b3  c3

query: `SELECT rslt FROM table1 WHERE id = 1` will return r1

我希望能够动态地使用它从 table2 中查询数据，即

SELECT r1 FROM table2

我试过:

select (SELECT rslt from table1 where id = 1) from table2

返回 'r1' 而不是 r1 列值 (a1, a2, a3) 中的值

提前致谢

Answer 1

最简单的解决方案是使用两个单独的查询。

我们使用第一个查询的结果为第二个查询动态生成 SQL 文本。

mysql> SET @colname := '' ; 
mysql> SELECT t.rslt FROM table1 t WHERE t.id = 1 ORDER BY t.rslt LIMIT 1 INTO @colname ;

mysql> SET @sql := CONCAT('SELECT `',@colname,'` FROM table2 ORDER BY 1') ;
mysql> PREPARE stmt FROM @sql ; 
mysql> EXECUTE stmt ;

mysql> DEALLOCATE PREPARE stmt ;

注意包括 @colname 作为 SQL 文本的一部分，动态准备的 SQL，是一个潜在的 SQL 注入(inject)漏洞。

---

如果要求是在单个 SQL 语句的上下文中完成类似的操作，则该语句需要预测从 table1 查询返回的可能值，并包括显式引用到 table2 中的可能列。例如，像这样:

  SELECT CASE ( SELECT t.rslt FROM table1 t WHERE t.id = 1 LIMIT 1 )
           WHEN 'r1' THEN r.r1 
           WHEN 'r2' THEN r.r2 
           WHEN 'r3' THEN r.r3 
           ELSE NULL
         END AS c2
    FROM table2 r
   ORDER BY ...

这不一定是编写查询的最有效方式，但它演示了这种模式。

---

在SQL语句中，必须明确指定标识符(表名、列名、函数名)；这些不能在运行时动态派生。这是因为 SQL 语句的处理方式......解析 SQL 文本的语法，然后解析语义(有效引用和特权)，评估可用访问路径的相对成本，选择执行计划，然后执行该计划。

也就是说，使用此 SQL 观察到的行为是我们所期望的:

 SELECT (SELECT rslt FROM table1 WHERE id = 1) FROM table2

准备好 SQL 文本，在执行时，对于 table2 中的每一行，执行 SELECT 列表中的子查询。如果子查询返回标量值，则该标量值作为外部查询的列返回。子查询返回的值是一个值，它不会(也不能)作为列名求值。