我在select子句中使用了一个别名(
AVG(u.rating) as avg_rating
)我想在where子句中加上这个
avg_rating > 3
Unknown column 'u3__1' in 'where clause'
.
有人知道我怎样才能让where子句看到别名吗?此别名在
orderBy
where
.
上面是一个示例,但这里是真正呈现的SQL,没有那么简单。我的问题实际上是一个别名的值与一堆条件。我遇到问题的别名是生成日期,它有重复出现,并且不必填充到每个日期字段。因此,我发布了一个简单的SQL查询,给出了相同的问题。
SELECT t.type_id as type_alias, t.* FROM theme as t WHERE t.id > 1 AND type_alias = 3
如果你这么感兴趣,这里有一个真正的问题:
SELECT t.id AS t__id, t.created_by AS
t__created_by, t.type_id AS
t__type_id, t.url_slug AS t__url_slug,
t.name AS t__name, t.description AS
t__description, t.summary AS
t__summary, t.start_month AS
t__start_month, t.start_day AS
t__start_day, t.start_year AS
t__start_year, t.duration_unit AS
t__duration_unit, t.duration_length AS
t__duration_length, t.is_active AS
t__is_active, t.is_public AS
t__is_public, t.needs_moderation AS
t__needs_moderation, t.recurrence AS
t__recurrence, t.tag_string AS
t__tag_string, t.date_created AS
t__date_created, t.date_updated AS
t__date_updated, AVG(t2.rating) AS
t2__0, IF(t.recurrence = "none",
STR_TO_DATE(CONCAT(t.start_month,
t.start_day, t.start_year), "%m%d%Y"),
(IF(STR_TO_DATE(CONCAT(t.start_month,
t.start_day, YEAR(NOW())), "%m%d%Y") >
NOW(),
STR_TO_DATE(CONCAT(t.start_month,
t.start_day, YEAR(NOW())), "%m%d%Y"),
STR_TO_DATE(CONCAT(t.start_month,
t.start_day, (YEAR(NOW())+1)),
"%m%d%Y")))) AS t__1, (COUNT(u.id) +
COUNT(e.id)) AS u__2 FROM theme t LEFT
JOIN theme_rating t2 ON t.id =
t2.theme_id LEFT JOIN
user_saves_themes u ON t.id =
u.theme_id LEFT JOIN event e ON
((e.is_active = 1 AND e.theme_id =
t.id)) WHERE t.id IN ('3', '2', '1')
AND (IF(t.recurrence = "none",
STR_TO_DATE(CONCAT(t.start_month,
t.start_day, t.start_year), "%m%d%Y"),
(IF(STR_TO_DATE(CONCAT(t.start_month,
t.start_day, YEAR(NOW())), "%m%d%Y") >
NOW(),
STR_TO_DATE(CONCAT(t.start_month,
t.start_day, YEAR(NOW())), "%m%d%Y"),
STR_TO_DATE(CONCAT(t.start_month,
t.start_day, (YEAR(NOW())+1)),
"%m%d%Y")))) >=
FROM_UNIXTIME(1278001295) AND
t.is_public = ? AND t.is_active = ?)
GROUP BY t.id ORDER BY t__1
