SQL中先排序再筛选与先筛选再排序
一、基础知识
Select 字段1,字段2,…… from 数据表 where 条件1:从数据表中将符合条件1的记录中的相关字段1、字段2筛选出来;
Order by 字段A asc (desc) 按字段A对记录进行升序排序,asc升序排列,desc降序排列
二、问题描述
ID | 产品名称 | 数量(个) | 单价(元/个) | 金额(元) |
1001 | A | 1 | 10 | 10 |
1002 | B | 2 | 10 | 20 |
1003 | C | 3 | 10 | 30 |
1004 | D | 4 | 10 | 40 |
1005 | E | 5 | 10 | 50 |
1006 | F | 6 | 10 | 60 |
1007 | G | 7 | 10 | 70 |
1008 | H | 8 | 10 | 80 |
1009 | I | 9 | 10 | 90 |
1010 | J | 10 | 10 | 100 |
要求:从上表1中筛选中前5个记录,并按ID降序排列。
如果按下面代码执行:
Sub Excel筛选结果排序()
Dim conn As Object
Dim mysql As String
Dim rst As Object
Set conn = VBA.CreateObject("adodb.connection")
mysql = "provider=microsoft.ace.oledb.12.0;extended properties=excel 12.0;data source=E:\学习\vba\数据库\72集\CangKu.xlsx"
conn.Open mysql
Set rst = VBA.CreateObject("adodb.recordset")
mysql = "select top 5 * from [sheet2$] order by ID desc"
rst.Open mysql, conn, 3, 1
Range("A2").Resize(rst.RecordCount, 3) = Application.WorksheetFunction.Transpose(rst.getrows(-1, 1))
rst.Close
conn.Close
Set rst = Nothing
Set conn = Nothing
End Sub
结果是
ID | 产品名称 | 数量(个) | 单价(元/个) | 金额(元) |
1010 | J | 10 | 10 | 100 |
1009 | I | 9 | 10 | 90 |
1008 | H | 8 | 10 | 80 |
1007 | G | 7 | 10 | 70 |
1006 | F | 6 | 10 | 60 |
由此,可见上述代码是先对表中的记录按ID进行降序排列,然后再筛选出前5个。那么要从表中筛选中前5个记录,并按ID降序排列,该如何操作呢?
三、分析思路
由上述可知,Order by 是对from 后的表进行排序的,所以可以如下修改代码:
Sub Excel筛选结果排序()
Dim conn As Object
Dim mysql As String
Dim rst As Object
Set conn = VBA.CreateObject("adodb.connection")
mysql = "provider=microsoft.ace.oledb.12.0;extended properties=excel 12.0;data source=E:\学习\vba\数据库\72集\CangKu.xlsx"
conn.Open mysql
Set rst = VBA.CreateObject("adodb.recordset")
mysql = "select * from (select top 5 * from [sheet2$]) order by ID desc"
rst.Open mysql, conn, 3, 1
Range("A2").Resize(rst.RecordCount, 3) = Application.WorksheetFunction.Transpose(rst.getrows(-1, 1))
rst.Close
conn.Close
Set rst = Nothing
Set conn = Nothing
End Sub
实测结果如下:
ID | 产品名称 | 数量(个) | 单价(元/个) | 金额(元) |
1005 | E | 5 | 10 | 50 |
1004 | D | 4 | 10 | 40 |
1003 | C | 3 | 10 | 30 |
1002 | B | 2 | 10 | 20 |
1001 | A | 1 | 10 | 10 |