Sub getFirstSQL() Dim conn As Object Dim rs As Object Dim sql As String Dim row As Long, firstCol As Integer '创建 ADO 连接 Set conn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") '使用 OLEDB 连接 Excel 文件 conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";" conn.Open 'SQL 查询每个学科的最高分 sql = "SELECT '语文' AS 学科, [准考证号], [姓名], [班级], [语文] AS 分数 FROM [成绩$] WHERE [语文] = (SELECT MAX([语文]) FROM [成绩$]) " & _ "UNION " & _ "SELECT '数学' AS 学科, [准考证号], [姓名], [班级], [数学] AS 分数 FROM [成绩$] WHERE [数学] = (SELECT MAX([数学]) FROM [成绩$]) " & _ "UNION " & _ "SELECT '英语' AS 学科, [准考证号], [姓名], [班级], [英语] AS 分数 FROM [成绩$] WHERE [英语] = (SELECT MAX([英语]) FROM [成绩$]) " & _ "UNION " & _ "SELECT '物理' AS 学科, [准考证号], [姓名], [班级], [物理] AS 分数 FROM [成绩$] WHERE [物理] = (SELECT MAX([物理]) FROM [成绩$]) " & _ "UNION " & _ "SELECT '化学' AS 学科, [准考证号], [姓名], [班级], [化学] AS 分数 FROM [成绩$] WHERE [化学] = (SELECT MAX([化学]) FROM [成绩$]) " & _ "UNION " & _ "SELECT '政治' AS 学科, [准考证号], [姓名], [班级], [政治] AS 分数 FROM [成绩$] WHERE [政治] = (SELECT MAX([政治]) FROM [成绩$]) " & _ "UNION " & _ "SELECT '历史' AS 学科, [准考证号], [姓名], [班级], [历史] AS 分数 FROM [成绩$] WHERE [历史] = (SELECT MAX([历史]) FROM [成绩$]) " & _ "UNION " & _ "SELECT '生物' AS 学科, [准考证号], [姓名], [班级], [生物] AS 分数 FROM [成绩$] WHERE [生物] = (SELECT MAX([生物]) FROM [成绩$]) " & _ "UNION " & _ "SELECT '地理' AS 学科, [准考证号], [姓名], [班级], [地理] AS 分数 FROM [成绩$] WHERE [地理] = (SELECT MAX([地理]) FROM [成绩$]) " & _ "UNION " & _ "SELECT '体育' AS 学科, [准考证号], [姓名], [班级], [体育] AS 分数 FROM [成绩$] WHERE [体育] = (SELECT MAX([体育]) FROM [成绩$]) " & _ "order by 学科 DESC"
