时间戳

首页 > 解决方案 > VBA 过程中的 SQL 子句帮助

sql - VBA 过程中的 SQL 子句帮助

问题描述

我有一个包含一个员工表（大约 80k）行的电子表格，我正在尝试查找专家员工（Skill_Prof = 5），他们是该分支机构中唯一具有该特定 Skill_Title 的人。到目前为止，我的代码是：

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT Branch, Skill_Title, Employee FROM [Employee$] WHERE Skill_Prof = 5"

rs.Open strSQL, cn

我的问题是试图确定我是否需要使用 COUNT、SUM 或其他函数来查找该分支机构中只有 1 名具有该 Skill_Title 的专家员工的情况。

有谁知道我怎么能做到这一点？

标签： sqlexcelvba

解决方案

与NOT EXISTS：

SELECT e.Branch, e.Skill_Title, e.Employee 
FROM [Employee$] AS e
WHERE e.Skill_Prof = 5
AND NOT EXISTS (
  SELECT 1 FROM [Employee$]
  WHERE Employee <> e.Employee AND Branch = e.Branch AND Skill_Title = e.Skill_Title AND Skill_Prof = e.Skill_Prof
)

或加入：

SELECT e.Branch, e.Skill_Title, e.Employee 
FROM [Employee$] AS e INNER JOIN (
    SELECT Branch, Skill_Title 
    FROM [Employee$] 
    GROUP BY Branch, Skill_Title
    HAVING SUM(IIF(Skill_Prof = 5, 1, 0)) = 1
) AS g ON g.Branch = e.Branch AND g.Skill_Title = e.Skill_Title
WHERE e.Skill_Prof = 5

推荐阅读