您没有说明您将使用什么标准来选择每个部门要查看的员工。我的示例显示了工资最高的员工。您应该能够轻松地更改此标准以选择任何员工。

select
    *
from
(
    select
        e.*
        ,d.DeptReference
        ,ROW_NUMBER() OVER (partition by e.Deptid order by e.salary desc) as Row
    from Employee as e
    inner join Department as d
        on e.Deptid = d.Deptid
)as xx
where xx.Row <= xx.DeptReference;

第一步是获取每个部门的n行（其中n为DeptReference），您可以通过交叉连接到数字表来实现这一点，我假设您没有数字表，每次都可以动态创建一个，但如果您有，那么您可以使用它：

SELECT  *
FROM    Department d
        CROSS JOIN
        (   SELECT  Number = ROW_NUMBER() OVER(ORDER BY o.object_id) 
            FROM    sys.all_objects o
        ) n
WHERE   n.Number <= d.DeptReference;

或者

SELECT  *
FROM    Department d
        CROSS JOIN Numbers n
WHERE   n.Number <= d.DeptReference;

所以这会给予

Deptid DeptName       DeptReference Number
1      HR                1          1
2      Engineering       2          1
2      Engineering       2          2

因此，工程是重复的。然后，您需要使用ROW_NUMBER将您的员工按部门排序，我不知道您的顺序应该是什么，所以将只使用EmpID：

SELECT  e.DeptID, 
        e.EmpID,
        e.EmpName,
        RowNumber = ROW_NUMBER() OVER(PARTITION BY e.DeptID ORDER BY e.EmpID)
FROM    Employee e;

因此，对于DeptID = 4，将给予：

Empid   Empname Deptid  RowNUmber
3       gita    4       1
4       rohit   4       2
8       samta   4       3
10      venu    4       4

然后，只需将为department创建的Number列连接到为employee创建的RowNumber列。

• N.B.我无法确定是否要限制显示的雇员（即，如果DeptReference为2，并且有3个雇员，则只显示2个），如果要显示所有雇员，则只需取消注解已注解掉的子句 *

SELECT  d.DeptID,
        d.DeptName,
        e.EmpID,
        e.EmpName
FROM    Department d
        CROSS JOIN
        (   SELECT  Number = ROW_NUMBER() OVER(ORDER BY o.object_id) 
            FROM    sys.all_objects o
        ) n
        LEFT JOIN
        (   SELECT  e.DeptID, 
                    e.EmpID,
                    e.EmpName,
                    RowNumber = ROW_NUMBER() OVER(PARTITION BY e.DeptID ORDER BY e.EmpID)
            FROM    Employee e
        ) e
            ON e.DeptID = d.DeptID
            AND e.RowNumber = n.Number
WHERE   n.Number <= d.DeptReference
--OR        e.EmpID IS NOT NULL
ORDER BY DeptID, EmpID;

由于示例数据中没有Sales部门的雇员，并且DeptReference为2，因此这将为Sales生成以下结果：

DeptID  DeptName    EmpID   EmpName
6       sales       (null)  (null)
6       sales       (null)  (null)

SQL Fiddle示例

如果查询应忽略每个部门超过n计数的额外雇员，则尝试以下操作：

with DeptEmps (deptId, empSequence) As
   (Select deptId, DeptReference
    From Departments d 
    union all
    Select deptId, empSequence - 1 
    From DeptEmps 
    Where empSequence > 1)

 Select d.DeptId, DeptName, 
    EmpId, EmpName, Salary
 From Departments d
    join DeptEmps de 
       on de.deptId = d.DeptId
   Left Join Employees e
       on e.DeptId = d.DeptId
          And de.empSequence =
            (Select Count(*)
             From Employees
             Where DeptId = e.DeptId
                and EmpId <= e.EmpId)
  Order By d.Deptname

如果查询应该 * 包括 * 超出每个部门的n个计数的额外雇员，那么它会变得有点复杂：

with DeptEmps (deptId, empSequence) As
  (Select deptId, 
      Case When d.DeptReference > 
          (Select Count(*) From Employees 
           Where DeptId = d.DeptId) Then d.DeptReference
   Else  (Select Count(*) From Employees 
              Where DeptId = d.DeptId) End empSequence  
   From Depts d 
      union all
   Select deptId, empSequence - 1 
   From DeptEmps 
   Where empSequence > 1)
Select  d.DeptId, DeptName, 
   EmpId, EmpName, Salary
From Depts d
   join DeptEmps de 
      on de.deptId = d.DeptId
   left Join Employees e
     on e.DeptId = de.DeptId
        And (Select Count(*)
             From Employees
             Where DeptId = e.DeptId
                and EmpId <= e.EmpId)
         = de.empSequence          
Order By d.Deptname