Проблема запроса MySQL с псевдонимом и агрегатными функциями

У меня есть проблемный запрос MySQL следующим образом:

SELECT camera_id, ((avg(low_price) + avg(high_price)) / 2) as avg_price
FROM camera_general, camera_products
WHERE camera_id = ir_camera_id
AND dp_post_dt IS NOT NULL
AND dp_post_dt NOT LIKE '0000%'
AND currently_manufactured = 'Yes'
AND ((no_of_sellers >= 0) OR ((TO_DAYS(CURRENT_DATE) - TO_DAYS(dp_post_dt)) < 120))
AND avg_price < 150 
AND camera_id != 1411
AND camera_id != 9
ORDER BY rand();

Это привело к ошибке «Неизвестный столбец« avg_price »в« предложении где »». Я понимаю, что это связано с тем, что псевдонимы столбцов не разрешены в предложении WHERE. (Поправьте меня, если я ошибаюсь в этом, пожалуйста.)

Итак, я изменил запрос следующим образом:

SELECT camera_id, ((avg(low_price) + avg(high_price)) / 2) as avg_price
FROM camera_general, camera_products
WHERE camera_id = ir_camera_id
AND dp_post_dt IS NOT NULL
AND dp_post_dt NOT LIKE '0000%'
AND currently_manufactured = 'Yes'
AND ((no_of_sellers >= 0) OR ((TO_DAYS(CURRENT_DATE) - TO_DAYS(dp_post_dt)) < 120))
AND ((avg(low_price) + avg(high_price)) / 2) < 150 
AND camera_id != 1411
AND camera_id != 9
ORDER BY rand();

Замена псевдонима фактическим расчетом и этим запросом привела к ошибке: «Недопустимое использование групповой функции». Я понимаю, что это связано с тем, что avg() не может произойти до тех пор, пока предложение WHERE не выполнит свою обработку.

Итак, я попробовал:

SELECT camera_id, ((avg(low_price) + avg(high_price)) / 2) as avg_price
FROM camera_general, camera_products
ORDER BY rand();
HAVING camera_id = ir_camera_id
AND dp_post_dt IS NOT NULL
AND dp_post_dt NOT LIKE '0000%'
AND currently_manufactured = 'Yes'
AND ((no_of_sellers >= 0) OR ((TO_DAYS(CURRENT_DATE) - TO_DAYS(dp_post_dt)) < 120))
AND avg_price < 150 
AND camera_id != 1411
AND camera_id != 9;

Замена WHERE на HAVING привела к появлению этой ошибки: «У вас есть ошибка в синтаксисе SQL; проверьте руководство, соответствующее версии вашего сервера MySQL, на предмет правильного синтаксиса для использования рядом с HAVING camera_id = ir_camera_id».

И в этот момент я чувствую, что стреляю в темноту, пытаясь заставить этот запрос работать. Кто-нибудь направит меня в правильном направлении, чтобы сделать этот функциональный запрос?

Спасибо!


mysql aggregate-functions mysql-error-1111
person Yazmin    schedule 03.09.2010    source источник

Ответы (2)


arrow_upward
2
arrow_downward

  1. Несмотря на то, что вы можете использовать WHERE для указания условия соединения, лучше сделать это в предложении LEFT[INNER] JOIN.
  2. Если вы хотите отфильтровать по неагрегированному полю, поместите фильтр в WHERE, если вам нужно отфильтровать по агрегированному, переместите условие в HAVING

  3. При использовании агрегатов и неагрегатов в одном запросе не забывайте GROUP BY.

    SELECT camera_id, ((avg(low_price) + avg(high_price)) / 2) as avg_price FROM camera_general
    INNER JOIN camera_products ON (camera_id = ir_camera_id)
    WHERE dp_post_dt IS NOT NULL
    AND dp_post_dt NOT LIKE '0000%'
    AND currently_manufactured = 'Yes'
    AND ((no_of_sellers >= 0) OR ((TO_DAYS(CURRENT_DATE) - TO_DAYS(dp_post_dt)) < 120))
    AND camera_id != 1411 AND camera_id != 9
    GROUP BY camera_id
    HAVING avg_price < 150
    ORDER BY rand();

person a1ex07    schedule 03.09.2010
comment
Спасибо - это сработало. Я понял (посмотрел) #2 и #3. Тем не менее, я все еще немного не понимаю, почему лучше сделать ЛЕВОЕ [ВНУТРЕННЕЕ] СОЕДИНЕНИЕ? - person Yazmin; 08.09.2010
comment
Условия соединения в WHERE — это старый синтаксис (ANSI SQL 1989). Он поддерживает только перекрестное и внутреннее соединение (для внешних соединений разные серверы имеют разные расширения, например, TSQL имеет *= И =*). Ключевое слово JOIN появилось в SQL 1992 и поддерживает внешние соединения. Использование JOIN делает ваш запрос ясным, и считается хорошим стилем использовать его при указании условия соединения. - person a1ex07; 08.09.2010
comment
Спасибо. Я ценю объяснение и помощь! - person Yazmin; 09.09.2010

arrow_upward
1
arrow_downward

Предложение ORDER должно идти после предложения HAVING. (кроме того, вы поставили точку с запятой ; после ORDER BY rand(), а затем продолжили HAVING, что на самом деле является началом другого запроса, поскольку первый закончился ;).

person aularon    schedule 03.09.2010
comment
Спасибо. Точка с запятой была путаницей из-за перестановки всего, что было раньше. Однако перемещение предложения ORDER после HAVING помогло решить одну проблему, но вынудило меня добавить все неагрегированные столбцы, используемые в предложении HAVING, в предложение GROUP BY. - person Yazmin; 08.09.2010