时间戳

首页 > 解决方案 > Microsoft Access 联合查询导致“查询太复杂”错误

sql - Microsoft Access 联合查询导致“查询太复杂”错误

问题描述

我有八个表，其中包含有关对整个公司的应用程序进行更改的信息。我只想查询和组合 200 多个应用程序子集的行。为此，我通过应用程序代码（AAA、BBB 等）查询每个表，并希望合并结果。当我使用超过 5 个应用程序代码时，我收到错误“查询复杂”。

我知道有一种更有效的方法可以做到这一点，但以下是我用我有限的知识所能做的最好的方法。

SELECT * FROM [2019-07-A July 5 to 7]   WHERE [Application code] LIKE 'AAA' UNION ALL 
SELECT * FROM [2019-07-B July 12 to 14] WHERE [Application code] LIKE 'AAA' UNION ALL 
SELECT * FROM [2019-07-C July 19 to 21] WHERE [Application code] LIKE 'AAA' UNION ALL 
SELECT * FROM [2019-07-D July 26 to 28] WHERE [Application code] LIKE 'AAA' UNION ALL 
SELECT * FROM [2019-08-A Aug 2 to 4]    WHERE [Application code] LIKE 'AAA' UNION ALL 
SELECT * FROM [2019-08-B Aug 9 to 11]   WHERE [Application code] LIKE 'AAA' UNION ALL 
SELECT * FROM [2019-08-C Aug 16 to 18]  WHERE [Application code] LIKE 'AAA' UNION ALL 
SELECT * FROM [2019-08-D Aug 23 to 25 Proposed] WHERE [Application code] LIKE 'AAA' UNION ALL 
SELECT * FROM [2019-07-A July 5 to 7]   WHERE [Application code] LIKE 'BBB' UNION ALL 
SELECT * FROM [2019-07-B July 12 to 14] WHERE [Application code] LIKE 'BBB' UNION ALL 
SELECT * FROM [2019-07-C July 19 to 21] WHERE [Application code] LIKE 'BBB' UNION ALL 
SELECT * FROM [2019-07-D July 26 to 28] WHERE [Application code] LIKE 'BBB' UNION ALL 
SELECT * FROM [2019-08-A Aug 2 to 4]    WHERE [Application code] LIKE 'BBB' UNION ALL 
SELECT * FROM [2019-08-B Aug 9 to 11]   WHERE [Application code] LIKE 'BBB' UNION ALL 
SELECT * FROM [2019-08-C Aug 16 to 18]  WHERE [Application code] LIKE 'BBB' UNION ALL
SELECT * FROM [2019-08-D Aug 23 to 25 Proposed] WHERE [Application code] LIKE 'BBB';

我希望能够一次查询所有 200 多个应用程序代码，并且比每个代码输入相同的字符串 8 次更有效。

标签： sqlms-access

解决方案

一种更简单的方法可能是在应用选择标准之前先union处理表数据，这样您只需在一个地方指定标准，例如：

select * from
(
    select * from [2019-07-A July 5 to 7]   union all 
    select * from [2019-07-B July 12 to 14] union all 
    select * from [2019-07-C July 19 to 21] union all 
    select * from [2019-07-D July 26 to 28] union all 
    select * from [2019-08-A Aug 2 to 4]    union all 
    select * from [2019-08-B Aug 9 to 11]   union all 
    select * from [2019-08-C Aug 16 to 18]  union all 
    select * from [2019-08-D Aug 23 to 25 Proposed]
) as t
where
    t.[Application code] like 'AAA' or
    t.[Application code] like 'BBB'

如果您没有like在语句的条件中使用通配符运算符，您也可以使用该in运算符并提供应用程序代码列表：

select * from
(
    select * from [2019-07-A July 5 to 7]   union all 
    select * from [2019-07-B July 12 to 14] union all 
    select * from [2019-07-C July 19 to 21] union all 
    select * from [2019-07-D July 26 to 28] union all 
    select * from [2019-08-A Aug 2 to 4]    union all 
    select * from [2019-08-B Aug 9 to 11]   union all 
    select * from [2019-08-C Aug 16 to 18]  union all 
    select * from [2019-08-D Aug 23 to 25 Proposed]
) as t
where
    t.[Application code] in ('AAA', 'BBB')

或者，您可以创建一个包含您希望返回的所有应用程序代码的表（在以下示例中，我调用ApplicationCodes了包含一个名为 的单个字段的此类表Code），然后使用简单的连接来隐式应用过滤，例如：

select * from
(
    select * from [2019-07-A July 5 to 7]   union all 
    select * from [2019-07-B July 12 to 14] union all 
    select * from [2019-07-C July 19 to 21] union all 
    select * from [2019-07-D July 26 to 28] union all 
    select * from [2019-08-A Aug 2 to 4]    union all 
    select * from [2019-08-B Aug 9 to 11]   union all 
    select * from [2019-08-C Aug 16 to 18]  union all 
    select * from [2019-08-D Aug 23 to 25 Proposed]
) as t 
inner join ApplicationCodes on t.[Application code] = ApplicationCodes.Code

推荐阅读