sql - SQL错误-字段可能引用多个表

Question

我看不到为什么我收到错误

“指定的字段qb2.WellworkOIL可以引用SQL语句的FROM子句中列出的多个表”

根据以下查询

SELECT 
 qb1.CompanyName, 
 qb1.AssetName, 
 qb1.Year, 
 IIf(qb1.PrimaryProductionType='Oil', 
 qb2.WellworkOIL, 
 IIF(qb1.PrimaryProductionType='Gas', 
 qb3.WellworkGAS, 
 qb4.WellworkOilGas)) AS Selected
FROM 
 (
 (
 PEBaseQueryConditions AS qb1 
 INNER JOIN PE_Field_WellworkOIL AS qb2 
 ON (qb1.Year=qb2.Year) AND (qb1.AssetName=qb2.AssetName)
 ) 
 INNER JOIN PE_Field_WellworkGAS AS qb3 
 ON (qb1.Year=qb3.Year) AND (qb1.AssetName=qb3.AssetName)
 ) 
 INNER JOIN PE_Field_WellworkOILGAS AS qb4 
 ON (qb1.Year=qb4.Year) AND (qb1.AssetName=qb4.AssetName)
GROUP BY 
 qb1.CompanyName, 
 qb1.AssetName, 
 qb1.Year, 
 qb1.PrimaryProductionType,
 qb2.WellworkOIL, 
 qb3.WellworkGAS, 
 qb4.WellworkOilGas;

Answer 1

我认为摆脱GROUP BY子句可以消除此问题。您没有使用聚合函数，因此不需要分组依据。

SELECT 
    qb1.CompanyName, 
    qb1.AssetName, 
    qb1.Year, 
    IIf(qb1.PrimaryProductionType='Oil', 
        qb2.WellworkOIL, 
        IIF(qb1.PrimaryProductionType='Gas', 
            qb3.WellworkGAS, 
            qb4.WellworkOilGas)) AS Selected
FROM    PEBaseQueryConditions AS qb1 
            INNER JOIN PE_Field_WellworkOIL AS qb2 
                 ON (qb1.Year=qb2.Year) AND (qb1.AssetName=qb2.AssetName)
            INNER JOIN PE_Field_WellworkGAS AS qb3 
                 ON (qb1.Year=qb3.Year) AND (qb1.AssetName=qb3.AssetName)
            INNER JOIN PE_Field_WellworkOILGAS AS qb4 
                 ON (qb1.Year=qb4.Year) AND (qb1.AssetName=qb4.AssetName)