查询太复杂

小编典典

查询太复杂

sql

我正在尝试将一些计算从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

阅读 214

2021-05-23

共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

2021-05-23