sql - 查询过于复杂

Question

我试图将一些计算从 Excel 移到我的 Access 数据库中，但是当我使用 5 个以上的输入值时，我遇到了“查询太复杂”错误。我应该拆分查询还是有更有效的方法来解决这个问题？任何帮助，将不胜感激!这是代码:

SELECT qb1.CompanyName, qb1.Assetname, qb1.Year,
      ((qb1.DatapointValue*1000000)+qb2.DatapointValue+
        qb3.DatapointValue+qb4.DatapointValue+qb5.DatapointValue+
        qb6.DatapointValue) AS MPPOilRevised

FROM ((((((PEBaseQuery AS qb1 
INNER JOIN PEBaseQuery AS qb2 ON qb1.AssetName=qb2.AssetName) 
INNER JOIN PEBaseQuery AS qb3 ON qb1.AssetName=qb3.AssetName) 
INNER JOIN PEBaseQuery AS qb4 ON qb1.AssetName=qb4.AssetName) 
INNER JOIN PEBaseQuery AS qb5 ON qb1.AssetName=qb5.AssetName) 
INNER JOIN PEBaseQuery AS qb6 ON qb1.AssetName=qb6.AssetName))

WHERE qb1.DatapointID=2003 And qb2.DatapointID=2032 
      And qb3.DatapointID=2034 And qb4.DatapointID=2042 
      And qb5.DatapointID=2036 And qb6.DatapointID=2030;

CompanyName, Year, AssetName, DatapointID, DatapointName,   DatapointValue 
CompanyA,    2011, CAAsset1,  2005,        OIL,             170883.740972926 
CompanyA,    2011, CAAsset1,  2032,        Wellwork,        615913 
CompanyA,    2011, CAAsset1,  2034,        Annual shutdown, 0 
CompanyA,    2011, CAAsset1,  2042,        Export,          0 
CompanyA,    2011, CAAsset1,  2036,        Plant,           958387 
CompanyA,    2011, CAAsset1,  2030,        Reservoir,       2394231 

Answer 1

看起来您需要一个聚合查询，而不是这个复杂的查询。例如。

select companyName, assetName, year, 
   Sum(DatapointValue) as MPPOilRevised
from PEBaseQuery 
where DatapointID in (2032, 2034, 2042, 2036)
group by companyName, assetName, year

唯一的问题是第一个数据点乘以 1000000。你可以试试 IIF为了那个原因:

select companyName, assetName, year, 
   Sum(IIf(DatapointID=2003,DatapointValue*1000000,DatapointValue)) as MPPOilRevised
from PEBaseQuery 
where DatapointID in (2032, 2034, 2042, 2036)
group by companyName, assetName, year

也请尝试这样的“疯狂”查询，使用针对此特定 DatapointID 的子查询，而不使用 IIF :

select companyName, assetName, year, SUM(DatapointValue) 
        + (select SUM(DatapointValue * 1000000) from  PEBaseQuery q2 
           where q2.companyName = q1.companyName 
              and q2.assetName= q1.assetName
              and q2.year= q1.year
              and q2.DatapointID = 2003
           group by companyName, assetName, year)
    from PEBaseQuery q1
    where DatapointID in (2032, 2034, 2042, 2036)
    group by companyName, assetName, year

更新“生产最大潜力”。请尝试以下操作:

select b.companyName, b.assetName, IIf(b.calculationResult > mp.calculationResult,b.calculationResult,mp.calculationResult)  as MPPOilRevised
from 
    (select companyName, assetName, year, Sum(IIf(DatapointID=2003,DatapointValue*1000000,DatapointValue)) as calculationResult
        from PEBaseQuery 
        where DatapointID in (2032, 2034, 2042, 2036) 
        group by companyName, assetName, year) b --Base
     left join 
     (select companyName, assetName, year, 
        Sum(DatapointValue) as calculationResult
        from PEBaseQuery 
        where DatapointID = 2218
        group by companyName, assetName, year) mp -- Max Potential
    on b.companyName= mp.companyName
        and b.assetName = mp.assetName
        and b.year = mp.year

使用减法逻辑的计算示例。 更新 最后疯狂的SQL。另请注意，对于此类事情，我会使用应用程序逻辑或存储过程:

    select b.companyName, b.assetName, IIf(b.calculationResult > mp.calculationResult,b.calculationResult,mp.calculationResult)  as MPPOilRevised
from 
    (select companyName, assetName, year, SUM(DatapointValue) 
        + (select SUM(DatapointValue * 1000000) from  PEBaseQuery q2 
           where q2.companyName = q1.companyName 
              and q2.assetName= q1.assetName
              and q2.year= q1.year
              and q2.DatapointID = 2003
           group by companyName, assetName, year)
        - (select SUM(DatapointValue) from  PEBaseQuery q2 
           where q2.companyName = q1.companyName 
              and q2.assetName= q1.assetName
              and q2.year= q1.year
              and q2.DatapointID = 2029 
           group by companyName, assetName, year)
    from PEBaseQuery q1
    where DatapointID in (2032, 2034, 2042, 2036)
    group by companyName, assetName, year) b --Base
     left join 
     (select companyName, assetName, year, 
        Sum(DatapointValue) as calculationResult
        from PEBaseQuery 
        where DatapointID = 2218
        group by companyName, assetName, year) mp -- Max Potential
    on b.companyName= mp.companyName
        and b.assetName = mp.assetName
        and b.year = mp.year