我有以下MySQL代码:
查询工作SELECT name, AVG(q1) AS avg
FROM respondents
LEFT JOIN results_new ON respondents.login_id = results_new.company
WHERE respondents.brand = 'ABC'
AND results_new.sdate = 'MAY2014'
GROUP BY name
ORDER BY avg
DESC
以上工作正常,它返回35行,并正确显示名称和平均值。
非工作查询
SELECT name, AVG(q1) AS avg
FROM respondents
LEFT JOIN results_new ON respondents.login_id = results_new.company
WHERE respondents.brand = 'ABC'
AND results_new.sdate = 'NOV2014'
GROUP BY name
ORDER BY avg
DESC
以上返回zero
结果-这是正确的,因为表中没有' NOV2014'的数据。
然而,我期望发生的是查询返回35行,每个名称旁边都有name
和NULL
。显然,我认为我的JOIN
是不正确的,但我不能找出我错在哪里。
欢迎大家指教
where
子句将left join
转换为inner join
,因为您对连接表中的数据进行了过滤。将此条件放在连接的on
子句中。
SELECT name, AVG(q1) AS avg
FROM respondents
LEFT JOIN results_new ON respondents.login_id = results_new.company
AND results_new.sdate = 'NOV2014'
WHERE respondents.brand = 'ABC'
GROUP BY hotel_name
ORDER BY avg DESC