优化MS ACCESS查询中的一组嵌套SELECT

Question

这是一个运行速度非常慢的MS Access查询。Results表是主表，P、B、S和C表是子表。列组合Index和ResultID在所有这些表中都是唯一的。例如，表C中只能有一行，其中Index是"1"，ResultID是2。我不使用JOIN，因为MS Access只支持内部联接，而我需要的是外部联接，因为我想显示表Results中的所有记录，即使在任何其他表中都没有相应的记录。

SELECT 
    [Results].[Id],
    [Results].[Name],
    [Results].[OfferPrice],
    [Results].[RegPrice],
    [Results].[ImageFileName],
    [Results].[Image],
    [Results].[URL],
    (SELECT P.P FROM P WHERE P.ResultID = Results.Id AND P.Index = "0") as P0,
    (SELECT P.P FROM P WHERE P.ResultID = Results.Id AND P.Index = "1") as P1,
    (SELECT P.P FROM P WHERE P.ResultID = Results.Id AND P.Index = "2") as P2,
    (SELECT P.P FROM P WHERE P.ResultID = Results.Id AND P.Index = "3") as P3,
    (SELECT P.P FROM P WHERE P.ResultID = Results.Id AND P.Index = "4") as P4,
    (SELECT P.P FROM P WHERE P.ResultID = Results.Id AND P.Index = "5") as P5,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "0") as B0,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "1") as B1,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "2") as B2,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "3") as B3,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "4") as B4,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "5") as B5,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "6") as B6,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "7") as B7,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "8") as B8,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "9") as B9,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "10") as B10,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "11") as B11,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "12") as B12,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "13") as B13,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "14") as B14,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "15") as B15,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "16") as B16,
    (SELECT S.S FROM S WHERE S.ResultID = Results.Id AND S.Index = "1") as S1,
    (SELECT S.S FROM S WHERE S.ResultID = Results.Id AND S.Index = "2") as S2,
    (SELECT S.S FROM S WHERE S.ResultID = Results.Id AND S.Index = "3") as S3,
    (SELECT S.S FROM S WHERE S.ResultID = Results.Id AND S.Index = "4") as S4,
    (SELECT S.S FROM S WHERE S.ResultID = Results.Id AND S.Index = "5") as S5,
    (SELECT S.S FROM S WHERE S.ResultID = Results.Id AND S.Index = "6") as S6,
    (SELECT S.S FROM S WHERE S.ResultID = Results.Id AND S.Index = "7") as S7,
    (SELECT S.S FROM S WHERE S.ResultID = Results.Id AND S.Index = "8") as S8,
    (SELECT S.S FROM S WHERE S.ResultID = Results.Id AND S.Index = "9") as S9,
    (SELECT S.S FROM S WHERE S.ResultID = Results.Id AND S.Index = "10") as S10,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "0") as C0,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "1") as C1,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "2") as C2,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "3") as C3,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "4") as C4,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "5") as C5,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "6") as C6,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "7") as C7,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "8") as C8,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "9") as C9,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "10") as C10
FROM 
    Results

Answer 1

我不确定你从哪里得到了以下内容：

我不使用JOIN，因为MS Access只支持内部联接，而我需要的是外部联接

MS Access支持OUTER JOIN语法。

您可以在每个表之间用LEFT JOIN替换关联的子查询。您的代码将类似于此：

SELECT 
    [Results].[Id],
    [Results].[Name],
    [Results].[OfferPrice],
    [Results].[RegPrice],
    [Results].[ImageFileName],
    [Results].[Image],
    [Results].[URL],
    max(IIF(P.Index = "0", P.P, null)) as P0,
    max(IIF(P.Index = "1", P.P, null)) as P1,
    max(IIF(P.Index = "2", P.P, null)) as P2,
    max(IIF(P.Index = "3", P.P, null)) as P3,
    max(IIF(P.Index = "4", P.P, null)) as P4,
    max(IIF(P.Index = "5", P.P, null)) as P5,
    max(IIF(B.Index = "0", B.B, null)) as B0,
    max(IIF(B.Index = "1", B.B, null)) as B1,
    max(IIF(B.Index = "8", B.B, null)) as B8,
    max(IIF(S.Index = "2", S.S, null)) as S2,
    max(IIF(C.Index = "1", C.C, null)) as C1
FROM Results
LEFT JOIN P
    on P.ResultID = Results.Id
LEFT JOIN B
    on B.ResultID = Results.Id
LEFT JOIN S
    on S.ResultID = Results.Id
LEFT JOIN C
    on C.ResultID = Results.Id
GROUP BY [Results].[Id],
    [Results].[Name],
    [Results].[OfferPrice],
    [Results].[RegPrice],
    [Results].[ImageFileName],
    [Results].[Image],
    [Results].[URL]

您还应该能够使用TRANSFORM函数透视数据，代码将类似于此：

TRANSFORM Max(Src.Value)
select 
    [Results].[Id],
    [Results].[Name],
    [Results].[OfferPrice],
    [Results].[RegPrice],
    [Results].[ImageFileName],
    [Results].[Image],
    [Results].[URL]
from results as R
left join
(
    select ResultID, P as Value, "P"&Index as col
    from p
    union all
    select ResultID, B as Value, "B"&Index as col
    from B
    union all
    select ResultID, C as Value, "C"&Index as col
    from C
    union all
    select ResultID, S as Value, "S"&Index as col
    from S
) as Src
    on R.id = Src.ResultID
GROUP BY [Results].[Id], [Results].[Name],
    [Results].[OfferPrice],[Results].[RegPrice],
    [Results].[ImageFileName], [Results].[Image],
    [Results].[URL]
PIVOT Src.col

Answer 2

如果显式连接，查询将运行得更快。这是模板：

SELECT 
    [Results].[Id],
    [Results].[Name],
    [Results].[OfferPrice],
    [Results].[RegPrice],
    [Results].[ImageFileName],
    [Results].[Image],
    [Results].[URL],
    max(iif(p.index = "0", p.p, "")) as p0,
    . . .
FROM results left outer join
     p
     on results.id = p.resultid left outer join
     . . .
group by [Results].[Id],
    [Results].[Name],
    [Results].[OfferPrice],
    [Results].[RegPrice],
    [Results].[ImageFileName],
    [Results].[Image],
    [Results].[URL]

其想法是使用left outer join引入表，并在正确的条件下连接它们，并使用聚合将它们缩减为每个结果一行。SELECT中的逻辑根据您设置的逻辑选择值。

Answer 3

这应该运行得很快。看起来您正试图对一个表和多个表的并集之间的连接进行透视。下面的unionized表将替换您的所有联接。事实上，这是有枢轴的，取代了进行任何相关子查询的需要。

TRANSFORM Max(unionized.DataColumn) AS MaxOfDataColumn
SELECT Results.ResName, Results.OfferPrice, Results.RegPrice, Results.Image, Results.URL
FROM (
    Select B.ID, B.B as DataColumn, "B"&B.B as tableName from B
    union all select C.ID, C.C, "C"&C.C from C
    union all select P.ID, P.P, "P"&P.P from P
    union all select S.ID, S.S, "S"&S.S from S)  AS unionized 
RIGHT JOIN Results ON unionized.ID = Results.ID
GROUP BY Results.ResName, Results.OfferPrice, Results.RegPrice, Results.Image, Results.URL
PIVOT unionized.tableName;

您需要稍微清理一下，因为我假设您使用的是示例列，但希望您能理解。