从复杂查询中获取数据到 Excel

发布于 2024-10-04 14:55:11 字数 697 浏览 0 评论 0原文

我有一个查询，我想依赖 Excel 工作表中的某些参数，但收到错误“无法以图形方式显示的查询中不允许使用参数”。我认为解决这个问题的唯一方法是使用视图，但这会带来多少额外开销？这意味着将两个表（一个包含近 70000 条记录，另一个包含超过 200000 条记录，并且两个表都有大约 40 个字段）连接到该视图中，可能只使用总共 80 个字段中的 5 个左右。我们没有测试服务器。或者，有没有一种方法可以将以下查询更改为 microsoft query 允许参数的查询？

select count(distinct a) from table1 where b=0 and c < '2010-01-01' and a in 
(select a from table2 where d between '2010-01-01' and '2010-12-31')

或作为连接：

select count(distinct table1.a) from table1 inner join table2 on (table1.a=table2.a 
and table2.d between '2010-01-01' and '2010-12-31') where table1.c < '2010-01-01'
and table1.b=0

我想用单元格值替换日期（c 和 d）。

谢谢，

恩斯特

原文

I've got a query which I want to depend on certain parameters from an excel sheet, but I get the error 'parameters are not allowed in queries that can't be displayed graphically'. The only way to get around it that I see is to use a view, but how much extra overhead would that give? It would mean joining two tables (one with near 70000 and one with over 200000 records and both having around 40 fields) into that view, probably using only 5 or so of the total 80 fields. We do not have a test server. Alternatively, is there a way to change the following query to one for which microsoft query does allow parameters?

select count(distinct a) from table1 where b=0 and c < '2010-01-01' and a in 
(select a from table2 where d between '2010-01-01' and '2010-12-31')

or as a join:

select count(distinct table1.a) from table1 inner join table2 on (table1.a=table2.a 
and table2.d between '2010-01-01' and '2010-12-31') where table1.c < '2010-01-01'
and table1.b=0

I want to replace the dates (for c and d) with cellvalues.

Thanks,

Ernst

分享到QQ

分享到微博