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); 

Какова причина? Благодарю.

Предложение WHERE вводит условие для отдельных строк ; Предложение HAVING вводит условие об агрегировании , то есть результаты выбора, где один результат, такой как подсчет, средний, мин, макс или сумма, был получен из нескольких строк. В запросе содержится запрос на второй тип условия (т.е. условие на агрегацию), следовательно, HAVING работает правильно.

Как правило, используйте WHERE перед GROUP BY и HAVING после GROUP BY . Это довольно примитивное правило, но оно полезно в более чем 90% случаев.

Пока вы на нем, вы можете повторно написать свой запрос, используя ANSI-версию соединения:

 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) 

Это устранило бы WHERE которое использовалось в качестве условия соединения тета .

HAVING работает на агрегатах. Поскольку COUNT является агрегированной функцией, вы не можете использовать ее в предложении WHERE .

Вот некоторые чтения из MSDN по совокупным функциям.

Вы не можете использовать предложение where с агрегатными функциями, потому что, если вы извлекаете записи по условию, оно переходит в запись таблицы по записи, а затем извлекает запись по условию, которое мы даем. Так что в это время мы не можем найти предложение. Хотя предложение clue работает на resultSet, который мы наконец получаем после запуска запроса.

Пример запроса:

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

Это будет хранить результирующий набор во временной памяти, тогда предложение будет выполнять свою работу. Поэтому мы можем легко использовать агрегатные функции здесь.

  1. Предложение WHERE может использоваться с операторами SELECT, INSERT и UPDATE, тогда как HAVING может использоваться только с оператором SELECT.

  2. WHERE фильтрует строки перед агрегацией (GROUP BY), тогда как группы фильтров HAVING после агрегации выполняются.

  3. Функция агрегации не может использоваться в предложении WHERE, если она не находится в подзапросе, содержащемся в предложении HAVING, тогда как агрегированные функции могут использоваться в предложении HAVING.

Источник

Сначала мы должны знать порядок выполнения Клаусов, т. Е. FROM> WHERE> GROUP BY> HAVING> DISTINCT> SELECT> ORDER BY. Поскольку WHERE clause выполняется после GROUP BY, записи не могут быть отфильтрованы путем применения WHERE к применяемым записям GROUP BY .

«HAVING – это то же самое, что и предложение WHERE, но применяется к сгруппированным записям».

сначала предложение WHERE извлекает записи на основе условия, тогда предложение GROUP BY соответствующим образом группирует их, а затем предложение HAVING извлекает записи группы на основании условия наличия.

Не видел пример как в одном запросе. Поэтому этот пример может помочь.

  /** 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 

Это сначала фильтрует таблицу companyId, затем группирует ее (по стране и городу) и дополнительно отфильтровывает ее до только городских агрегатов Мексики. Компания companyId не нужна в агрегации, но мы могли использовать WHERE для фильтрации только тех строк, которые мы хотели, прежде чем использовать GROUP BY.

1. Мы можем использовать агрегатную функцию с предложением HAVING не по предложению WHERE, например min, max, avg.

2. Предложение WHERE исключает запись кортежа в соответствии с предложением HAVING исключает целую группу из коллекции группы

В основном HAVING используется, когда у вас есть группы данных, а WHERE используется, когда у вас есть данные в строках.