在SQL查询中使用参数与子查询 [英] Using Parameters in SQL query with sub-query

问题描述

我有一个相当复杂的SQL查询和一个嵌套的子查询。当我尝试在Microsoft Query中使用参数是说我可以使用不能用图形表示的查询中的参数。所以我需要另外一个选择。我想你可以把你的SQL查询作为一个字符串放在一个单元格，然后一个宏运行它。任何想法我该怎么做？

谢谢

-Jesse

解决方案

以下是我在Excel 2007中解决Microsoft Query的限制：

1. 在Microsoft Query中产生虚拟查询（例如， SELECT NULL AS Test ），并将其插入到工作表中。


2. 右键单击MS Query刚刚插入的表，然后单击Table->编辑外部数据属性。


3. 单击连接属性按钮，然后单击定义选项卡。 >
   
4. 在命令文本部分中，使用通常的？

5. 单击确定退出外部数据属性窗口。


6. 再次右键单击表，然后选择表 - >参数以绑定参数以通常的方式。

这个想法是绕过了MS Quer y提供，它有一些任意的限制，底层引擎没有。

这适用于许多复杂的查询，但不是全部。当我遇到MS Query拒绝完全消化的查询时，我重新构造查询（可行时）或在SQL服务器上创建一个 VIEW ，并针对该查询进行查询。 / p>

I have a fairly complicated SQL query with a nested subquery. When I try to use parameters in Microsoft Query is say I can use parameters in queries that cant be represented graphically. So I need another option. I think you can place your SQL query in a cell as a string then have a Macro run it. Any ideas how I could do this?

Thanks

-Jesse

解决方案

Here's what I do to work around the limitations of Microsoft Query in Excel 2007:

1. A produce a dummy query (SELECT NULL AS Test, for example) in Microsoft Query and insert it into the worksheet.
2. Right-click on the table that MS Query just inserted and click Table->Edit External Data Properties.
3. Click on the Connection Properties button, then click the Definition tab.
4. In the Command Text section, write out or paste in the query that you want, using the usual '?' convention for parameters, then click OK.
5. Click OK to exit the External Data Properties window.
6. Right click on the table again, and select Table->Parameters to bind the parameters in the usual way.

The idea is the bypass the GUI that MS Query provides, which has some arbitrary limitations that the underlying engine does not.

This works for many complex queries, but not all. When I encounter a query that MS Query refuses to digest at all, I either refactor the query (when feasible) or create a VIEW on the SQL server and query against that.

这篇关于在SQL查询中使用参数与子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！