ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
数据管理利器Foxtable2022下载 Excel 2016函数公式学习大典 Office知识技巧免费学 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 Power Query数据清洗实战攻略 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 3711|回复: 6

[讨论] sql语句中 top 的灵活应用

[复制链接]

TA的精华主题

TA的得分主题

发表于 2014-7-22 11:02 | 显示全部楼层 |阅读模式
原帖在此,当时没有想到解决的办法,这几天有时间正好研究的一下,现把自己的想法与结果,发上来,大家讨论一下,也算是对自己的一个总结吧。
http://club.excelhome.net/forum.php?mod=viewthread&tid=1137754&page=1#pid7749431

表格内容精简了一下,保留必要的字段,其余的删除了。

截图00.png


表格的结构如上图所示,楼主的要求

1、先把表按“录取成绩”从高到低排序。
2、然后通过筛选户口所在省为“AAA省”;户口所在县为“AAA县”;考生类别栏为“A”类别的考生;第1志愿为“高中1校”的。(这里把省,县都清除了,如果需要的话,简单的办法是首先筛选一下,然后把筛选后的数据,复制到原表,如果不想筛选,在sql语句中也可以实现,也就是加几个where and 。。。。。。,在我上传的表格中,没有提现,如果需要可以自己添加。)
3、从高分到低分选16个在“录取学校”栏手工录取“高中1校”。即完成划线和录取,16人中最低的分即为录取分数线。
4、第1志愿所有招录学校都录取后,录取计划人数未满的再录第2志愿。

首先先来看看top语句的应用,

截图02.png


截图01.png

通过如上两张图,我想大家已经明白了top语句的用法,需要补充的是,楼主在说明中,漏了一点,录取学校是按照第一志愿进行录取的,如果第一志愿已经录够,那么不管你的分数多少,第二志愿学校是不会录取的。

举个简单的例子

成绩
第一志愿
第二志愿
99
1校
2校
98
1校
2校
97
2校
96
2校

1校录1人,2校录2人,成绩为98的,未被录取,原因是第一志愿都已经录够了,第二志愿根本就不需要看,虽然分数排名第二,但也未被录取。(自己的理解,不一定正确。)

下面是代码,我尽量解释的清楚一些。

Sub 查询()
Dim cnn, RS As Object
Dim Sql As String
Set cnn = CreateObject("adodb.connection") 建立对对象的引用,这种方法,属于前期绑定,就不用去引用控件了,好处是,你复制代码后就可以运行,缺点是,速度慢一些。
Set RS = CreateObject("adodb.Recordset")
Dim arr  建立一个数组,大小以后定
arr = Sheet1.[i2:l8] 对arr数组赋值
Dim i, j, k, M As Byte, lastrow As Integer  建立一些变量,后面要用到
Application.ScreenUpdating = False
Sheet3.Activate
Cells.Clear
[A1:C1] = Array("考号", "成绩", "录取学校") 对sheet3 表格写入标题,
[a2] = "t"   这里解释一下,如果没有这个t,当对not in的时候,会出现错误,所以这里写了一个和考号无关的字符串。
cnn.Open "Provider=Microsoft.ACE.OleDb.12.0;Extended Properties='Excel 12.0;HDR=YES'; Data Source=" & ThisWorkbook.FullName '07/10用 打开、建立的连接。
For i = 1 To 7  因为有7说学校,所以最少需要循环7次
    M = 0 初始化变量
   Sql = "select top " & arr(i, 2) & " 考号,成绩 from " & "(select * from [sheet1$a1:f]  where 第1志愿='" & arr(i, 1) _
        & "' and 考号 not IN (select 考号 from [SHEET3$A:A] )) ORDER BY 成绩 desc"   这条语句的意思是从sheet1表格中,选取第一志愿是高中1校,人数由arr(1,2)定,并且an成绩降序排列,也就是成绩高的在前面,并且,不包含sheet3表格中的考号,因为如果第一志愿已经被录取的话,再次循环的时候,第一志愿已经被录取的不需要参与循环了。
    RS.Open Sql, cnn, 1, 1  打开连接,形成记录集,
    M = M + RS.RecordCount                  RS.RecordCount是返回记录的条数。  
    If M < arr(i, 2) Then   如果返回的记录小于招生人数,那么就需要对第二、第三、第四志愿进行4次循环,直到录取够人数为止。
        lastrow = [B65536].End(xlUp).Row   返回a列不为空的最后一个行号。
        Range("A1").Offset(lastrow, 0).CopyFromRecordset RS   copy 记录集中的记录
        RS.Close                                                       关闭记录集
            For j = 2 To 4   对其余志愿进行循环。
                Sql = "select top " & arr(i, 2) - M & " 考号,成绩 from " & "(select * from [sheet1$a1:f]  where 第" & j & "志愿='" & arr(i, 1) _
                    & "' and 考号 not IN (select 考号 from [SHEET3$A:A] )) ORDER BY 成绩 desc"           arr(i, 2) - M  意思是对第二志愿循环时需要减去第一志愿的人数,比如第一志愿招了10人,共计招12人,那么第二志愿只需要2人就够了。
                RS.Open Sql, cnn, 1, 1
                lastrow = [B65536].End(xlUp).Row
                Range("A1").Offset(lastrow, 0).CopyFromRecordset RS
                M = M + RS.RecordCount
                RS.Close
                If M >= arr(i, 2) Then Exit For   如果招够了人数就退出循环,也就是说第1+第二志愿,够了,第3就不需要循环了。
            Next
        Columns("A:C").SpecialCells(xlCellTypeBlanks) = arr(i, 1)   定位空值,填充学校名称。
    Else     第一志愿就符合招生人数了,那么执行如下语句
        lastrow = [B65536].End(xlUp).Row   
        Range("A1").Offset(lastrow, 0).CopyFromRecordset RS
        Columns("A:C").SpecialCells(xlCellTypeBlanks) = arr(i, 1)
        RS.Close
    End If  结束判断,开始第二次循环,比如1校第一志愿招16人,在第一志愿已经够了,那么,开始2校招。
Next
Sheet1.Activate
Dim brr, crr, drr, err  再定义一些变量,其实这里直接用函数也是可以的,不过既然已经用到了sql,就一直用下去吧。
Sql = "select 录取学校,COUNT(*) from [sheet3$] GROUP BY 录取学校"
brr = cnn.Execute(Sql).GetRows    把查询结果返回到数组(这个数组是水平数组),第一列是学校名称,第二列是录取人数。
Sql = "select 录取学校,MIN(成绩) from [sheet3$] GROUP BY 录取学校"   返回录取学校的最小成绩,其实这里可以说是返回第一志愿是我校的最小成绩。
crr = cnn.Execute(Sql).GetRows
cnn.Close: Set cnn = Nothing
Set RS = Nothing
For i = 1 To UBound(arr)   对sheet1录取分数、录取人数进行循环赋值。
    arr(i, 3) = Application.HLookup(arr(i, 1), brr, 2, 0)   hlookup 函数不用解释了吧。
    arr(i, 4) = Application.HLookup(arr(i, 1), crr, 2, 0)
Next
[i2].Resize(7, 4) = arr  返回数据
err = [a1].CurrentRegion
For i = 2 To UBound(err)
    err(i, 7) = Application.VLookup(err(i, 1), Sheet3.[A:C], 3, 0)  返回录取学校名称
Next
[a1].Resize(UBound(err), 7) = err
[g:g].Replace "#N/A", "未录取"   因为用到了函数,没有值的会显示na错误,替换成,未录取。
Application.ScreenUpdating = True
End Sub


Sub t1()
Dim tim As Double, i As Byte
tim = Timer
For i = 1 To 10
    Call 查询
Next
MsgBox Format(Timer - tim, "0.00秒")
End Sub

这个语句是测试速度用的,数据表中,大概1300条数据,循环10次就是13000条数据,在我的电脑大概是20秒,实际上时间应该更少一些,因为底下那些对sheet1赋值,实际使用中,只需要循环一次就可以了。

结果如下

学校
计划录取人数
实际录取人数
分数线
高中1校
16
17
735
高中2校
15
17
714.5
高中3校
15
15
663
高中4校
14
14
445.5
高中5校
16
16
341
高中6校
13
13
370.5
高中7校
12
12
622.5

可以看到1校多了一人,因为第16名,与第17名,成绩是一样的,按我的想法,都需要,除非楼主指明别的方法,如果只按成绩的话,就应该都录。
2校也一样


100778
734
高中1校
高中2校
未录取
100741
733.5
高中1校
高中1校
未录取
100852
732
高中1校
未录取
100926
731.5
高中1校
未录取
101111
731
高中1校
高中4校
未录取
101075
729.5
高中1校
未录取
101149
729.5
高中1校
高中3校
未录取

可以看到成绩考的如此之高,竟然也未被录取,原因就在于,第一志愿各个学校都已经招够了,第二志愿没有意义。

中考划线录取工作.rar (121.64 KB, 下载次数: 66)




TA的精华主题

TA的得分主题

发表于 2014-7-22 11:13 | 显示全部楼层
学习了,打开网站必有收获。

TA的精华主题

TA的得分主题

发表于 2014-7-22 12:16 | 显示全部楼层
本帖最后由 gpk 于 2014-7-22 12:17 编辑

在此正好请教版主,有一组人员,每个人员对应数量不等值,如何按每个人取这个人倒数第一和倒数第二的值
倒数第一个 好做 last函数就出来了
倒数第二个怎么求?

百思不得其解
取最后和倒数第二.rar (6.46 KB, 下载次数: 13)
  

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-7-22 13:16 | 显示全部楼层
gpk 发表于 2014-7-22 12:16
在此正好请教版主,有一组人员,每个人员对应数量不等值,如何按每个人取这个人倒数第一和倒数第二的值
倒 ...


我不是版主。

你这个应该是top无关,

top一般来说后面都有排序的,再说你这个没有一个序号字段,用top不太好办,而一般的数据库,自增字段一般都是默认有的,

Sub a()
Dim cnn, RS As Object
Dim Sql As String
Set cnn = CreateObject("adodb.connection")
Set RS = CreateObject("adodb.Recordset")
Application.ScreenUpdating = False
Sheet1.Activate
cnn.Open "Provider=Microsoft.ACE.OleDb.12.0;Extended Properties='Excel 12.0;HDR=YES'; Data Source=" & ThisWorkbook.FullName '07/10用 打开、建立的连接。
Dim i As Byte
For i = 3 To 6
    Sql = "select 值 from [sheet1$a:b] where 姓名='" & Cells(i, "E") & "'"
    RS.Open Sql, cnn, 1, 1
    RS.movelast
    Cells(i, "F") = RS.Fields(0)
    If RS.RecordCount > 1 Then
        RS.moveprevious
        Cells(i, "G") = RS.Fields(0)
     End If
    RS.Close
        
Next
cnn.Close
Set RS = Nothing
Set cnn = Nothing
End Sub

评分

参与人数 1鲜花 +2 收起 理由
gpk + 2 感谢帮助

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-7-22 15:11 | 显示全部楼层
魂断蓝桥 发表于 2014-7-22 13:16
我不是版主。

你这个应该是top无关,

谢谢蓝大及时回复,我慢慢消化下

TA的精华主题

TA的得分主题

发表于 2014-11-4 17:03 | 显示全部楼层
你好,excel高手,请教下,如何在多重合并的数据透视表里更改筛选项?,如下,如何将除”计提费用“以外的项目设置成报表筛选项,谢谢啦.                                                                                                               
                                                                                                               
时间        (全部)                                                                                                       
                                                                                                               
求和项:值        列标签                                                                                                       
部门        币种        费用所属公司(合同所属公司)        国家        合同号        计提费用(USD)        计提费用(原币金额)        结算方式        渠道名称        收款公司        所属游戏        是否结算        结算时间        备注        总计
互联网部         -            -            -            -            12,000.00          12,000.00          -                            -                                    24,000.00
无线技术         -            -            -            -            12,000.00          12,000.00          -                            -                                    24,000.00
(空白)                                                                                                               
总计         -            -            -            -            24,000.00          24,000.00          -                            -                                    48,000.00

TA的精华主题

TA的得分主题

发表于 2017-7-18 18:22 | 显示全部楼层
魂断蓝桥 发表于 2014-7-22 13:16
我不是版主。

你这个应该是top无关,

请教老师,我们地区的中考分数线比较复杂,要有多个条件要求,不知能否通过分数线,来确定录取的情况吗?感激不尽!

中考考生录取结果汇总.rar

17.04 KB, 下载次数: 5

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,每天学会一个新技能

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2021-10-26 10:50 , Processed in 0.120727 second(s), 17 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

快速回复 返回顶部 返回列表