mysql - 谷歌电子表格: Skip empty queries when stacking multiple queries in one sheet

Question

背景我正在构建一个电子表格查询复合体，它会自动返回所有每周事件。这些事件按地区排序，但并非所有地区每周都会举办事件。我使用分号堆叠了来自每个区域的查询，例如{=QUERY();QUERY();QUERY();QUERY()}，每个都用区域名称标记。查询复合体只需要返回具有实际事件的查询。

问题由于不一定每周都会在所有区域发生一项事件，因此我的查询复合体中最终会出现一个或多个空查询。这将导致我的查询仅返回带有其标签的查询，或者，在删除查询标签时，导致查询复合体返回错误。

示例

={

Query(Data!A:O,"Select A,B,C,E,F where D = 'Phantom Row For Titles' order by D asc, C asc label B 'City'",1);

Query(Data!A:O,"Select A,B,C,E,F where D = 'Africa' order by D asc, C asc label C 'Africa'",0);

Query(Data!A:O,"Select A,B,C,E,F where D = 'Asia' order by D asc, C asc label C 'Asia'",0);

Query(Data!A:O,"Select A,B,C,E,F where D = 'Central America' order by D asc, C asc label C 'Central America'",0);

Query(Data!A:O,"Select A,B,C,E,F where D = 'Europe' order by D asc, C asc label C 'Europe'",0);

Query(Data!A:O,"Select A,B,C,E,F where D = 'Middle East' order by D asc, C asc label C 'Middle East'",0);

Query(Data!A:O,"Select A,B,C,E,F where D = 'North America' order by D asc, C asc label C 'North America'",0);

Query(Data!A:O,"Select A,B,C,E,F where D = 'Oceania' order by D asc, C asc label C 'Oceania'",0);

Query(Data!A:O,"Select A,B,C,E,F where D = 'South America' order by D asc, C asc label C 'South America'",0)

}

这是一个工作电子表格示例，说明了我所拥有的和我需要的: https://docs.google.com/spreadsheets/d/1VnDyxOdw9aJMJpIs7zKSdG3c6fUo-QxDU82zaw-k7Kk/edit?usp=sharing

Answer 1

我认为，没有直接的方法可以做到这一点。我在这里建议一个解决方法。

请尝试:

=QUERY(

{Query(Data!A:O,"Select A,B,C,E,F where D = 'Phantom Row For Titles' order by D asc, C asc label B 'City'",1);

IFERROR({"","","Africa","","";Query(Data!A:O,"Select A,B,C,E,F where D = 'Africa' order by D asc, C asc",0)},{"x","x","x","x","x"});

IFERROR({"","","Asia","","";Query(Data!A:O,"Select A,B,C,E,F where D = 'Asia' order by D asc, C asc",0)},{"x","x","x","x","x"});

IFERROR({"","","Central America","","";Query(Data!A:O,"Select A,B,C,E,F where D = 'Central America' order by D asc, C asc",0)},{"x","x","x","x","x"});

IFERROR({"","","Europe","","";Query(Data!A:O,"Select A,B,C,E,F where D = 'Europe' order by D asc, C asc",0)},{"x","x","x","x","x"});

IFERROR({"","","Middle East","","";Query(Data!A:O,"Select A,B,C,E,F where D = 'Middle East' order by D asc, C asc",0)},{"x","x","x","x","x"});

IFERROR({"","","North America","","";Query(Data!A:O,"Select A,B,C,E,F where D = 'North America' order by D asc, C asc",0)},{"x","x","x","x","x"});

IFERROR({"","","Oceania","","";Query(Data!A:O,"Select A,B,C,E,F where D = 'Oceania' order by D asc, C asc",0)},{"x","x","x","x","x"});

IFERROR({"","","South America","","";Query(Data!A:O,"Select A,B,C,E,F where D = 'South America' order by D asc, C asc",0)},{"x","x","x","x","x"})},

"select * where Col2 <> 'x'",1)

<小时/>

我的基本逻辑是使用查询两次:

1. 检查输出
2. 如果输出超过 2 行，则返回结果。

这种方法很糟糕，因为它使公式加倍。

<小时/>

然后我想到了不同的方法:

• 当没有标签且没有结果时，查询将返回错误
• 所以我使用了 iferror 函数，并在发生错误时给出了假行 {"x","x","x","x","x"}
• 最终查询是跳过“x”行。