Jave SQL executeQuery为有效的sql语句抛出错误

Question

我使用java java.sql.*从SQLite DB进行查询。我发现了一个严重的问题，我把sqlString写成：

SELECT n.Name as Name, 
       c.Value as Value0, 
       d.Value as Value1 
FROM  (Table1 c inner join Table2 n on c.NameID = n.ID),
      Table3 d 
WHERE c.RunID = 1 
      and d.RunID = 2 
      and c.NameID = d.NameID

语句stmt.executeQuery(sqlQuery)抛出以下异常：

java.sql.SQLException: no such column: n.Name
at org.sqlite.DB.throwex(DB.java:288)
at org.sqlite.NativeDB.prepare(Native Method)
at org.sqlite.DB.prepare(DB.java:114)
at org.sqlite.Stmt.executeQuery(Stmt.java:89).............

Name已经是Table2表的一部分。同样的语句在SQLite命令提示符下运行正常。但是当我去掉左方括号并尝试从java执行时，就没有问题了。你知道为什么会这样吗？

Answer 1

尝试这样的操作，使用sub resultset

SELECT rs1.Name AS NAME, 
       rs1.Value0 AS Value0, 
       d.Value AS Value1 
FROM  (SELECT n.Name AS NAME, 
       c.Value AS Value0, 
       d.Value AS Value1 
       FROM Table1 c INNER JOIN Table2 n ON c.NameID = n.ID) rs1,
      Table3 d 
WHERE c.RunID = 1 
      AND d.RunID = 2 
      AND c.NameID = d.NameID

Answer 2

无法在外部引用内部查询中使用的变量。例如，您在内部sql中使用了变量n。但你是在外面引用它。变量n超出了它的作用域。

Answer 3

朋友们，谢谢你的快速回复。我能够解决这个问题。我正在分享这个，所以如果any1其他人得到这个问题，可以这样做：

SELECT ABC.Name as Name, ABC.Value as Value0, d.Value as Value1
  FROM
  (select n.Name as Name, c.Value as Value0 from Table1 c
   inner join Table2 n on c.NameID = n.ID) AS ABC,
Table3 d ......;

致以敬意，Tor