Summarize the use of 'table' in Excel SQL queries

This article brings you relevant knowledge about excel, which mainly introduces the use of tables in SQL queries, including regional tables, cross-workbook tables, etc. ,I hope everyone has to help.

Related learning recommendations: excel tutorial

Today we will talk about the Excel table in the SQL statement.

1. Area into table

There are many differences between Excel worksheet and database data table. The most significant point is that the database data table can be understood as consisting of rows and columns, while The Excel worksheet is composed of cells one after another, and these cells have unique address expression methods, that is, A1 or R1C1. They can also form a range of cells with connected data, such as A2:H8.

Then the question is, if we only need to calculate part of an Excel worksheet, how should we express it in SQL?

This kind of problem is very common.

For example, many people’s Excel title row is not in the first row of the table, but in the second row...

As shown in the figure below

At this time, we hope to calculate the cell range of columns A2:F, so that it is easier for us to use field names to process data, rather than the entire Excel worksheet...

Another example, a There are two or more "tables" inside and outside the table... What does this sentence mean?

See the picture below

In the table shown in the picture, there is both a "teacher table" and a "student table"; If we only want SQL to reference and calculate the teacher table data of A2:D8...

... SQL in Excel actually supports using the cell range of the worksheet as a "table".

For the problem shown in the above picture, the SQL can be written as:

SELECT 姓名,学科 FROM [数据表$A2:D8]

The query results are as follows:

In the first case, we Knowing that the data starts in cell A2, but does not know which cell in column F ends, the SQL can be written as:

SELECT 姓名,爱好 FROM [学生表$A2:F]

In addition, if we need the SQL reference to calculate the data in the entire column D:G of the table, the SQL can be written as :

SELECT * FROM [学生表$D:G]

Summarize the above ways of expressing the Excel worksheet area, that is, the worksheet name, the dollar sign $, the cell address in the relative reference state, and finally wrapped in square brackets.

Just Jiang Zi.

Tips for this section:

[Student table $A2:F], we say that this statement can refer to the cell range from column A2 to column F where the last data exists, but This is subject to a limited precondition, which is a non-self-connected state. The so-called self-join refers to the workbook that SQL should be used to link itself. In the self-linking state, the maximum expression of A2:F is A2:F65536 rows; if the reference rows required at this time exceed 65536 rows, please use the whole table mode.

2. Cross-workbook tables

A well-known problem is that Excel functions are very tired when processing cross-workbook data, except for a few lookup reference functions (such as VLOOKUP etc.), most functions need to open the relevant workbook before they can be calculated and used.

Yes, the VLOOKUP function does not need to open the relevant workbook and can be used across workbooks. Moreover, after the VLOOKUP formula is written, even if you delete the workbook it refers to, it will not hinder it. Calculation, this is because it has cached relevant data in the workbook where the formula is located, but VLOOKUP mode does not support complex nesting of functions... Snap your fingers, if you are interested, we will chat separately another day one time.

...Ahem, back to SQL~~

...The SQL statements we shared before are all used to process the tables of the current workbook. If the data we need to process is located in other workbooks , how to express SQL?

For example, obtain all the data of the "Grade Table" in the "Student Table. Don't let me admire you.

If it is the OLE DB method (refer to Chapter 1 of this series of tutorials for this method), the SQL statement is as follows

SELECT * FROM [D:\EH小学\学生表.xlsx].[成绩表$]

The specified table string after FROM consists of two parts, the first square bracket Inside is the complete workbook name with a suffix in the storage path of the specified workbook, and inside the last square bracket is the worksheet name, and the two square brackets are connected with a period (.).

If you are using SQL statements through VBA ADO...

Knock on the bookcase in front of the warning: Children's shoes with poor VBA foundation, please skip the following content...

Compared with OLE DB method, VBA ADO method is much more flexible. It can use ADO to directly create and open a link to the specified workbook, so the SQL statement does not need to specify the complete name of the workbook, etc.

The code reference is as follows

Sub ADO_SQL()
'适用于除2003版以外的高版本Excel
Dim cnn As Object, rst As Object
Dim strPath As String, strCnn As String, strSQL As String
Dim i As Long
Set cnn = CreateObject("adodb.connection")
strPath = "D:\EH小学\学生表.xlsx" '指定工作簿
strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & strPath
cnn.Open strCnn '创建并打开到指定工作簿的链接
strSQL = "SELECT * FROM [成绩表$]" 'strSQL语句，查询成绩表的所有数据
Set rst = cnn.Execute(strSQL) '执行strSQL
Cells.ClearContents
For i = 0 To rst.Fields.Count - 1
Cells(1, i + 1) = rst.Fields(i).Name
Next
Range("a2").CopyFromRecordset rst
cnn.Close
Set cnn = Nothing
End Su

The 7th line of the above code directly specifies the complete name of the workbook that needs to be connected, and there is no need for special processing in the SQL statement.

但更多的情况是，ADO创建的链接是一个工作簿，需要获取的数据在另一个或多个工作簿，例如两个工作簿之间的数据查询统计。此时通常使用的代码如下

Sub ADO_SQL2()
'适用于除2003版以外的高版本Excel
Dim cnn As Object, rst As Object
Dim strPath As String, strCnn As String, strSQL As String
Dim i As Long
Set cnn = CreateObject("adodb.connection")
strPath = ThisWorkbook.FullName '代码所在工作簿的完整名称
strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & strPath
cnn.Open strCnn '创建到代码所在工作簿的链接
strSQL = "SELECT * FROM [Excel 12.0;DATABASE=D:\EH小学\学生表.xlsm].[成绩表$]"
Set rst = cnn.Execute(strSQL) '执行SQL
Cells.ClearContents
For i = 0 To rst.Fields.Count - 1
Cells(1, i + 1) = rst.Fields(i).Name
Next
Range("a2").CopyFromRecordset rst
cnn.Close
Set cnn = Nothing
End Sub

代码中第7行创建了当前工作簿的链接，SQL语句中又指定了另外一个工作簿的链接。SQL语句如下

SELECT * FROM [Excel 12.0;DATABASE=D:\EH小学\学生表.xlsx].[成绩表$]

FROM指定表的字符串有两部分组成。第一个中括号中，Excel 12.0是目标工作簿的版本号，第2章时我们讲过，Excel 12.0适用于除了2003以外的所有Excel版本。DATABASE指定的是数据源工作簿的路径和名称。第2个中括号内是工作表名。两个中括号之间使用英文点号相连。

看起来似乎VBA+ADO方法的SQL语句比OLE DB法更复杂？确实如此，不过前者的功能也更强大。比如，它可以通过VBA对象的属性、方法，循环和判断语句等，有条件的筛选工作簿和工作表……相比之下，OLE DB中的SQL语句就是纯手工常量模式了。当然，更重要的是，前者不但可以查数据，还可以增改删数据，后者却只限于查。

相关学习推荐：excel教程

The above is the detailed content of Summarize the use of 'table' in Excel SQL queries. For more information, please follow other related articles on the PHP Chinese website!