在MySQL中工作，但在Postgrehaving语句中失败

Question

下面的查询在MySQL中有效，但在Postgresql中失败，错误如下：

ERROR:  column "status" does not exist

我们如何在Postgresql中实现这一点？

select
devices.*,
CASE
WHEN devices.retired = false
AND devices.last_reported_utc_at > '2020-12-23 07:13:52'
AND SUM(device_measurements.flow) > 0 THEN 'active'
WHEN devices.retired = false
AND devices.last_reported_utc_at > '2020-12-23 07:13:52'
AND SUM(device_measurements.flow) = 0 THEN 'online'
WHEN devices.retired = false
AND devices.last_reported_utc_at <= '2020-12-23 07:13:52' THEN 'offline'
WHEN devices.retired = false
AND devices.serial_number IS NULL THEN 'inactive'
WHEN devices.retired = true THEN 'retired'
END AS status,
MAX(devices_meters.activated_at) AS activated_at,
MAX(devices_meters.created_at) AS latest
from
devices
left join devices_meters on devices_meters.device_id = devices.id
left join device_measurements on device_measurements.device_id = devices.id
group by
devices.id,
devices_meters.activated_at
having
status = "offline"
order by
devices.installed_at desc

Answer 1

有几种解决方案。

• 使用子查询
• 使用CTE(WITH(
• 使用横向连接

后者有我的偏好，以下是我将如何实现它：

select
devices.*,
lt.status,
MAX(devices_meters.activated_at) AS activated_at,
MAX(devices_meters.created_at) AS latest
from
devices
left join devices_meters on devices_meters.device_id = devices.id
left join device_measurements on device_measurements.device_id = devices.id
left join lateral (
SELECT
CASE
WHEN devices.retired = false
AND devices.last_reported_utc_at > '2020-12-23 07:13:52'
AND SUM(device_measurements.flow) > 0 THEN 'active'
WHEN devices.retired = false
AND devices.last_reported_utc_at > '2020-12-23 07:13:52'
AND SUM(device_measurements.flow) = 0 THEN 'online'
WHEN devices.retired = false
AND devices.last_reported_utc_at <= '2020-12-23 07:13:52' THEN 'offline'
WHEN devices.retired = false
AND devices.serial_number IS NULL THEN 'inactive'
WHEN devices.retired = true THEN 'retired'
END AS status
) AS lt ON TRUE
group by
devices.id,
devices_meters.activated_at
having
lt.status = "offline"
order by
devices.installed_at desc

现在，尽管这解决了在查询中使状态处处可用的问题，但GROUP BY还有另一个问题，它需要包括device.*和lt.status，但我认为这超出了这个问题的范围。