我有一个包含 3 个联合和一个 order by 子句的查询。

因此,如果 s1、s2、s3 和 s4 是包含选择查询的字符串变量,而 oc 是包含 order by 子句的字符串变量,则完整查询为:

s1 & " union " & s2 & " union " & s3 & " union " & s4 & oc

每个选择都具有完全相同的格式,只是不同的参数,并且每个都包含许多连接。

此查询失败并显示错误



但是,以下所有变化都不会导致错误
s1 & " union " & s2 & " union " & s3 & " union " & s4

s1 & oc

s2 & oc

s3 & oc

s4 & oc

导致错误的查询在 .mdb 格式的同一数据库上运行正常。

postgresql 服务器是 postgres 9.0
该程序是带有 ADO 2.6 的 vb6,连接字符串是



编辑
每个单独选择的结构如下:
 select Charges.CHARGESCALENAMEF1, MATERIALST12.MATERIALNAMEF1
   , workspecificationst11.*
   , clientdetail.clientidf9
   , eventTbl.*
 from Charges
 right join (MATERIALST12
            right join  (workspecificationst11
                         right join (clientdetail
                                      right join  eventTbl
                                      on clientdetail.clientidf9 = eventTbl.clientidf2
                                    ) on workspecificationst11.serialnumf5 = eventTbl.workf5
                         ) ON MATERIALST12.SERIALNUMF3 = eventTbl.MATERIALF8
           ) ON Charges.SERIALNUMF2 = eventTbl.CHARGESCALEF7
 where eventTbl.clientidf2 = 15249
 and  eventTbl.workf5 = 40
 and  eventTbl.workstatusf14 = 2
 and eventTbl.section = 1
 and workspecificationst11.showinaccountsf9 = true
  ;

最佳答案

如果我理解正确,完整的查询最终会看起来像下面这样,尽管我已经将它简化为两个 SELECT 语句,它们之间有一个 UNION,最后是一个 ORDER BY 子句。根据您上面的评论,您发现:

当查询包含 table alias 时,eventTbl 子句中的 ORDER BY(在您的情况下为 UNION )不起作用。

我怀疑这是因为每个 select 查询都有一个 eventTbl 的本地表别名,但总的来说,该查询在整个查询的范围内没有表别名。此外,ORDER BY 应用于整个结果集,而不是单个 SELECT 语句。

SELECT Charges.CHARGESCALENAMEF1, MATERIALST12.MATERIALNAMEF1
   , workspecificationst11.*
   , clientdetail.clientidf9
   , eventTbl.*
 from Charges
 right join (MATERIALST12
            right join  (workspecificationst11
                         right join (clientdetail
                                      right join  eventTbl
                                      on clientdetail.clientidf9 = eventTbl.clientidf2
                                    ) on workspecificationst11.serialnumf5 = eventTbl.workf5
                         ) ON MATERIALST12.SERIALNUMF3 = eventTbl.MATERIALF8
           ) ON Charges.SERIALNUMF2 = eventTbl.CHARGESCALEF7
 where eventTbl.clientidf2 = 15249
 and  eventTbl.workf5 = 40
 and  eventTbl.workstatusf14 = 2
 and eventTbl.section = 1
 and workspecificationst11.showinaccountsf9 = true

UNION

SELECT Charges.CHARGESCALENAMEF1, MATERIALST12.MATERIALNAMEF1
   , workspecificationst11.*
   , clientdetail.clientidf9
   , eventTbl.*
 from Charges
 right join (MATERIALST12
            right join  (workspecificationst11
                         right join (clientdetail
                                      right join  eventTbl
                                      on clientdetail.clientidf9 = eventTbl.clientidf2
                                    ) on workspecificationst11.serialnumf5 = eventTbl.workf5
                         ) ON MATERIALST12.SERIALNUMF3 = eventTbl.MATERIALF8
           ) ON Charges.SERIALNUMF2 = eventTbl.CHARGESCALEF7
 where eventTbl.clientidf2 = 15249
 and  eventTbl.workf5 = 40
 and  eventTbl.workstatusf14 = 2
 and eventTbl.section = 1
 and workspecificationst11.showinaccountsf9 = true
ORDER BY eventbl.datef1, eventtbl.eventidf15;

ORDER BY 中删除表别名是有效的,因为作为单个查询,上面返回一个 datef1 列和一个 eventiff15 列,但没有定义整个结果集的表别名。

解决方案
ORDER BY 子句中删除表别名:
ORDER BY datef1, eventidf15;

如果您执行以下操作,我想您可以使表别名工作:
SELECT * FROM
( Original full query goes here without the order by) AS eventTbl
ORDER BY eventbl.datef1, eventtbl.eventidf15;

但这有点过分了。

关于postgresql - 使用 'missing from-clause entry' 和 'union' 查询时出错 'order by',我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/24107125/

10-11 22:36
查看更多