SQL-关于“按组排列的前1名”的帮助

我不知道你的模式以及你做,但我猜你需要关联您的分组 DepartmentID DepartmentPhoneID .

select x.*
from DepartmentPhone x
where 
  x.DepartmentID = 2
  and x.PhoneID = (select max(y.PhoneID)
                   from departmentphone y 
                     join Phone on y.PhoneID = Phone.PhoneID and Phone.PhoneType = 4
                   where x.DepartmentID = y.DepartmentID);

这里有两个可供选择的查询,它们应该在不使用相关子查询的情况下获得相同的结果。第一个使用派生表:

select *
from DepartmentPhone x
join (select d.DepartmentID, max(d.PhoneID) as maxPhoneID
      from DpartmentPhone d join Phone p using (PhoneID)
      where p.PhoneType = 4
      group by d.DepartmentID) y
  using (DepartmentID);

第二种选择完全不使用子查询,而是使用自联接:

select d1.*
from DepartmentPhone d1
join Phone p1 on d1.PhoneID = p1.PhoneID and p1.PhoneType = 4
left outer join (DepartmentPhone d2 join Phone p2 
    on d2.PhoneID = p2.PhoneID and p2.PhoneType = 4)
  on d1.DepartmentID = d2.DepartmentID and d1.PhoneID < d2.PhoneID
where d2.DepartmentID is NULL;

我希望在那里 是 ROW_NUMBER

;WITH DepartmentPhone_CTE AS
(
    SELECT p.*, 
        ROW_NUMBER() OVER
            (PARTITION BY dp.DepartmentID ORDER BY dp.PhoneID) AS RowNum
    FROM DepartmentPhone dp
    INNER JOIN Phone p
        ON p.PhoneID = dp.PhoneID
    WHERE p.PhoneType = 4
)
SELECT dp.*
FROM DepartmentPhone_CTE
WHERE RowNum = 1