UNION ALL查询:“字段定义太多"； [英] UNION ALL query: "Too Many Fields Defined"

问题描述

我正在尝试获得3个表的UNION，每个表都有97个字段.我尝试了以下方法:

I'm trying to get a UNION of 3 tables, each of which have 97 fields. I've tried the following:

select * from table1
union all
select * from table2
union all
select * from table3

这给我一条错误消息:

Too many fields defined.

我还尝试从第一个表中明确选择所有字段名称(为简便起见添加了省略号):

I also tried explicitly selecting all the field names from the first table (ellipses added for brevity):

select [field1],[field2]...[field97] from table1
union all
select * from table2
union all
select * from table3

当我仅将两个表合并时，效果很好:

It works fine when I only UNION two tables like this:

select * from table1
union all
select * from table2

作为该查询的结果，我最终不应包含超过97个字段；两张桌子的UNION只有97.那么为什么我要得到3张桌子的Too many fields?

I shouldn't end up with more than 97 fields as a result of this query; the two-table UNION only has 97. So why am I getting Too many fields with 3 tables?

正如下面的RichardTheKiwi所述，Access正在对UNION链中每个SELECT查询的字段计数求和，这意味着我的3个表超过了255个字段的最大值.因此，我需要这样编写查询:

As RichardTheKiwi notes below, Access is summing up the field count of each SELECT query in the UNION chain, which means that my 3 tables exceed the 255 field maximum. So instead, I need to write the query like this:

select * from table1
union all
select * from
(select * from table2
union all
select * from table3)

效果很好.

推荐答案

看来，要跟踪的字段数(限制为255)是根据UNION ALL的所有部分计算的.因此3 x 97 = 291，这是多余的.您可能会创建一个由UNION(共2个部分)组成的查询，然后再创建一个包含第3个部分的查询.

It appears that the number of fields being tracked (limit 255) is counted against ALL parts of the UNION ALL. So 3 x 97 = 291, which is in excess. You could probably create a query as a UNION all of 2 parts, then another query with that and the 3rd part.

这篇关于UNION ALL查询:“字段定义太多"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！