Excel:使用单元格值作为SQL查询的参数

I'm using MS Excel to get data from a MySQL database through ODBC.
I successfully get data using an SQL query. But now I want that query to be parameterized.
So I wonder If it is possible to use a cell value (a spreadsheet cell) as a parameter for such a query.
For example, for this query:

我使用MS Excel通过ODBC从MySQL数据库获取数据。我成功地使用SQL查询获取数据。但是现在我想要那个查询被参数化。因此，我想知道是否可能使用单元格值(电子表格单元格)作为此类查询的参数。例如，对于这个查询:

select name from user where id=1

I'd like to get the id value from, say, cell D4 in the spreadsheet.

我想从电子表格中的单元格D4中获取id值。

Is that the proper approach to parameterize a query? and how can I do it?

这是对查询进行参数化的合适方法吗?我怎么做呢?

Thanks.

谢谢。

4 个解决方案

#1

I had the same problem as you, Noboby can understand me, But I solved it in this way.

我和你有同样的问题，没有人能理解我，但我是这样解决的。

SELECT NAME, TELEFONE, DATA
FROM   [sheet1$a1:q633]
WHERE  NAME IN (SELECT * FROM  [sheet2$a1:a2])

you need insert a parameter in other sheet, the SQL will consider that information like as database, then you can select the information and compare them into parameter you like.

您需要在其他表中插入一个参数，SQL将把该信息视为数据库，然后您可以选择该信息并将其与您喜欢的参数进行比较。

#2

If you are using microsoft query, you can add "?" to your query...

如果您正在使用microsoft query，您可以在查询中添加“?”

select name from user where id= ?

从用户id= ?

that will popup a small window asking for the cell/data/etc when you go back to excel.

当你回到excel时，会弹出一个小窗口询问单元格/数据/等等。

In the popup window, you can also select "always use this cell as a parameter" eliminating the need to define that cell every time you refresh your data. This is the easiest option.

在弹出窗口中，您还可以选择“始终使用这个单元格作为参数”，以避免在每次刷新数据时需要定义该单元格。这是最简单的选择。

#3

queryString = "SELECT name FROM user WHERE id=" & Worksheets("Sheet1").Range("D4").Value

#4

The SQL is somewhat like the syntax of MS SQL.

SQL有点像MS SQL的语法。

SELECT * FROM [table$] WHERE *;

It is important that the table name is ended with a $ sign and the whole thing is put into brackets. As conditions you can use any value, but so far Excel didn't allow me to use what I call "SQL Apostrophes" (´), so a column title in one word is recommended.

表名以$符号结尾，整个内容放在括号中，这一点很重要。条件您可以使用任何价值,但到目前为止,Excel不允许我使用我称之为“SQL撇号”(´),所以建议一个词一个列标题。

If you have users listed in a table called "Users", and the id is in a column titled "id" and the name in a column titled "Name", your query will look like this:

如果在一个名为“users”的表中列出了用户，并且id在一个名为“id”的列中，而在一个名为“name”的列中列出了用户名，那么您的查询将如下:

SELECT Name FROM [Users$] WHERE id = 1;

Hope this helps.

希望这个有帮助。

#1