[sql] SQL-VS 사용

다음 두 테이블이 있습니다.

1. Lecturers (LectID, Fname, Lname, degree).
2. Lecturers_Specialization (LectID, Expertise).

가장 전문화 된 강사를 찾고 싶습니다. 이것을 시도하면 작동하지 않습니다.

SELECT
  L.LectID,
  Fname,
  Lname
FROM Lecturers L,
     Lecturers_Specialization S
WHERE L.LectID = S.LectID
AND COUNT(S.Expertise) >= ALL (SELECT
  COUNT(Expertise)
FROM Lecturers_Specialization
GROUP BY LectID);

그러나 이것을 시도하면 작동합니다.

SELECT
  L.LectID,
  Fname,
  Lname
FROM Lecturers L,
     Lecturers_Specialization S
WHERE L.LectID = S.LectID
GROUP BY L.LectID,
         Fname,
         Lname
HAVING COUNT(S.Expertise) >= ALL (SELECT
  COUNT(Expertise)
FROM Lecturers_Specialization
GROUP BY LectID); 

이유가 무엇입니까? 감사.

답변

SELECT  L.LectID, Fname, Lname
FROM Lecturers L
JOIN Lecturers_Specialization S ON L.LectID=S.LectID
GROUP BY L.LectID, Fname, Lname
HAVING COUNT(S.Expertise)>=ALL
(SELECT COUNT(Expertise) FROM Lecturers_Specialization GROUP BY LectID)

  /**
INTERNATIONAL_ORDERS - table of orders by company by location by day
companyId, country, city, total, date
**/

SELECT country, city, sum(total) totalCityOrders
FROM INTERNATIONAL_ORDERS with (nolock)
WHERE companyId = 884501253109
GROUP BY country, city
HAVING country = 'MX'
ORDER BY sum(total) DESC

select empName, sum(Bonus)
from employees
order by empName
having sum(Bonus) > 5000;