我有以下查询在Postgres 9.1中运行良好：
SELECT users.id,GREATEST( COALESCE(MAX(messages.created_at),'2012-07-25 16:05:41.870117'),COALESCE(MAX(phone_calls.created_at),'2012-07-25 16:05:41.870117') ) AS latest_interaction FROM users LEFT JOIN messages ON users.id = messages.user_id LEFT JOIN phone_calls ON users.id = phone_calls.user_id GROUP BY users.id ORDER BY latest_interaction DESC LIMIT 5;
但我想要做的是这样的事情：
SELECT users.id,'2012-07-25 16:05:41.870117')
) AS latest_interaction
FROM users LEFT JOIN messages ON users.id = messages.user_id
LEFT JOIN phone_calls ON users.id = phone_calls.user_id
GROUP BY users.id
ORDER BY
CASE WHEN(
latest_interaction > '2012-09-05 16:05:41.870117')
THEN 0
WHEN(latest_interaction > '2012-09-04 16:05:41.870117')
THEN 2
WHEN(latest_interaction > '2012-09-04 16:05:41.870117')
THEN 3
ELSE 4
END
LIMIT 5;
我收到以下错误：
错误：列“latest_interaction”不存在
似乎我不能使用带有CASE语句的order by子句中的聚合latest_interaction的别名.
这有什么变通方法吗？
解决方法
尝试将其包装为子查询：
SELECT *
FROM
(
SELECT users.id,GREATEST(
COALESCE(MAX(messages.created_at),'2012-07-25 16:05:41.870117')
) AS latest_interaction
FROM users LEFT JOIN messages ON users.id = messages.user_id
LEFT JOIN phone_calls ON users.id = phone_calls.user_id
GROUP BY users.id
) Sub
ORDER BY
CASE WHEN(
latest_interaction > '2012-09-05 16:05:41.870117')
THEN 0
WHEN(latest_interaction > '2012-09-04 16:05:41.870117')
THEN 2
WHEN(latest_interaction > '2012-09-04 16:05:41.870117')
THEN 3
ELSE 4
END
LIMIT 5;
