'FROM'涉及巨大 UNION 子句的语法错误

Question

好吧，我正在进行的一个项目（在MS ACCESS中）是计算客户为汽车公司购买的每个额外"选项"的数量。为此，我创建了以下查询，将每个选项放在一列中，然后在下一列中对每个选项的总数求和（为了可读性和匿名性进行编辑）。

SELECT a.options, Count(*)
FROM(
SELECT TBL.Des1 AS options FROM TBL UNION ALL
SELECT TBL.Des2 AS options FROM TBL UNION ALL
SELECT TBL.Des3 AS options FROM TBL UNION ALL
SELECT TBL.Des4 AS options FROM TBL UNION ALL
SELECT TBL.Des5 AS options FROM TBL UNION ALL
SELECT TBL.Des6 AS options FROM TBL UNION ALL
SELECT TBL.Des7 AS options FROM TBL UNION ALL
SELECT TBL.Des8 AS options FROM TBL UNION ALL
SELECT TBL.Des9 AS options FROM TBL UNION ALL
SELECT TBL.Des10 AS options FROM TBL UNION ALL
SELECT TBL.Des11 AS options FROM TBL UNION ALL
SELECT TBL.Des12 AS options FROM TBL UNION ALL
SELECT TBL.Des13 AS options FROM TBL) AS a
INTO TBL_OPTION_ALL
GROUP BY a.options;

我的问题是在尝试运行时出现错误"FROM子句中的语法错误"。错误提示终止后，底部的INTO语句将高亮显示。最初，我用括号分隔每个SELECT，但后来我得到了一个"JOIN子句语法错误"的错误，并发现了一个类似的问题，通过删除括号解决了这个问题。我最初也只有(...)a来创建别名，但为此我将其转换为(...) AS a，因为我不确定创建别名的方法是否适用于Access。

我有一些关于我的问题所在的理论（从最有可能到最小）

1. 当我应该使用[]时，我正在使用（），或者
   • 我的一些UNION调用缺少括号，或者
   • 我需要以完全不同的方式组织括号，并像以前一样分解UNION
2. 它无法处理这么多UNION。如果是这样的话，我该如何构建呢？我是否必须建立多个查询

Answer 1

尝试下面的代码，您使用的是表别名，而不是字段别名。

SELECT a.options, Count(*)
FROM(
SELECT TBL.Des1  AS options FROM TBL UNION ALL
SELECT TBL.Des2 AS options FROM TBL UNION ALL
SELECT TBL.Des3  AS options FROM TBL UNION ALL
SELECT TBL.Des4 AS options FROM TBL UNION ALL
SELECT TBL.Des5  AS options FROM TBL UNION ALL
SELECT TBL.Des6  AS options FROM TBL UNION ALL
SELECT TBL.Des7 AS options FROM TBL UNION ALL
SELECT TBL.Des8 AS options FROM TBL UNION ALL
SELECT TBL.Des9 AS options FROM TBL UNION ALL
SELECT TBL.Des10 AS options FROM TBL UNION ALL
SELECT TBL.Des11 AS options FROM TBL UNION ALL
SELECT TBL.Des12  AS options FROM TBL UNION ALL
SELECT TBL.Des13  AS optionsFROM TBL) AS a
INTO TBL_OPTION_ALL
GROUP BY a.options;

Answer 2

语法向后。这是

Select <your columns> 
Into <destination table> 
From <source table>

所以你应该有：

SELECT a.options, Count(*)
INTO TBL_OPTION_ALL
FROM(
SELECT TBL.Des1 AS options FROM TBL UNION ALL
SELECT TBL.Des2 AS options FROM TBL UNION ALL
SELECT TBL.Des3 AS options FROM TBL UNION ALL
SELECT TBL.Des4 AS options FROM TBL UNION ALL
SELECT TBL.Des5 AS options FROM TBL UNION ALL
SELECT TBL.Des6 AS options FROM TBL UNION ALL
SELECT TBL.Des7 AS options FROM TBL UNION ALL
SELECT TBL.Des8 AS options FROM TBL UNION ALL
SELECT TBL.Des9 AS options FROM TBL UNION ALL
SELECT TBL.Des10 AS options FROM TBL UNION ALL
SELECT TBL.Des11 AS options FROM TBL UNION ALL
SELECT TBL.Des12 AS options FROM TBL UNION ALL
SELECT TBL.Des13 AS options FROM TBL) AS a
GROUP BY a.options;

Answer 3

"AS选项"需要位于子查询中的列名之后，而不是表名之后。

更新

试试这个：

SELECT a.options, Count(*)
INTO TBL_OPTION_ALL
FROM(
SELECT TBL.Des1 AS options FROM TBL UNION ALL
SELECT TBL.Des2 AS options FROM TBL UNION ALL
SELECT TBL.Des3 AS options FROM TBL UNION ALL
SELECT TBL.Des4 AS options FROM TBL UNION ALL
SELECT TBL.Des5 AS options FROM TBL UNION ALL
SELECT TBL.Des6 AS options FROM TBL UNION ALL
SELECT TBL.Des7 AS options FROM TBL UNION ALL
SELECT TBL.Des8 AS options FROM TBL UNION ALL
SELECT TBL.Des9 AS options FROM TBL UNION ALL
SELECT TBL.Des10 AS options FROM TBL UNION ALL
SELECT TBL.Des11 AS options FROM TBL UNION ALL
SELECT TBL.Des12 AS options FROM TBL UNION ALL
SELECT TBL.Des13 AS options FROM TBL) AS a
GROUP BY a.options;