2审批人2 3审批人3员工1员工2员工3员工4员工5员工6员工7员工8员工9
我不知道该如何将其放入查询中。我需要有IdApprover员工下的员工数量,但这只在一个表上。我想要审批人的姓名,其中对应于IdApprover=IdEmployee
SELECT COUNT(IdApprover), IdApprover, EmployeeName
FROM tblEmployee
GROUP BY IdApprover
ORDER BY COUNT(IdApprover) DESC
上面提到的所有列都在同一张表上。我想要
Count(IdApprover) IdApprover EmployeeName
3 45 John Doe
2 26 James Bond
其中,员工姓名是审批人的姓名,审批人也是员工。
我想你的tblEmployee表看起来有点像这个
DECLARE @tblEmployee TABLE
(
IdApprover int not null,
IdEmployee int not null,
EmployeeName nvarchar(50) not null
)
INSERT INTO @tblEmployee VALUES (1,1,'Approver 1');
INSERT INTO @tblEmployee VALUES (2,2,'Approver 2');
INSERT INTO @tblEmployee VALUES (3,3,'Approver 3');
INSERT INTO @tblEmployee VALUES (1,4,'Employee 1');
INSERT INTO @tblEmployee VALUES (1,5,'Employee 2');
INSERT INTO @tblEmployee VALUES (1,6,'Employee 3');
INSERT INTO @tblEmployee VALUES (2,4,'Employee 4');
INSERT INTO @tblEmployee VALUES (2,4,'Employee 5');
INSERT INTO @tblEmployee VALUES (3,4,'Employee 6');
INSERT INTO @tblEmployee VALUES (3,4,'Employee 7');
INSERT INTO @tblEmployee VALUES (3,4,'Employee 8');
INSERT INTO @tblEmployee VALUES (3,4,'Employee 9');
这就是记录的样子,其中前3条记录是由IdApprover=IdEmployee指定的审批者,并且员工链接到不同的员工(他们的审批者(。
IdApprover审批人1