sql - PostgreSQL 错误：“WHERE”处或附近的语法错误

Question

我必须在 Postgres 中创建一个简单的视图，但是出了点问题。

我将在下面粘贴我的 SQL 查询：

CREATE OR REPLACE VIEW domotica."sensori-stanze-unita"
 AS
SELECT
sens.id_sens,sens.nome,sens.id_stanza,stanz.id,stanz.id_immobile,immo.id
FROM domotica.sensori AS sens
INNER JOIN domotica.stanze AS stanz
INNER JOIN domotica.immobili AS immo
ON stanz.id_immobile = immo.id 
WHERE sens.id_stanza = stanz.id;

显示的错误如下

ERROR: syntax error at or near "WHERE"
LINE 9: WHERE sens.id_stanza = stanz.id;
^

标签： sqlpostgresqlinner-joinwhere-clause

Answer 1

一个INNER JOIN requiresON子句，通常包含连接条件。在这里，您可以将条件从WHERE子句移动到ON第一个连接的子句：

CREATE OR REPLACE VIEW domotica."sensori-stanze-unita" AS
SELECT se.id_sens, se.nome, se.id_stanza, st.id AS st_id, st.id_immobile, im.id as im_id
FROM domotica.sensori AS se
INNER JOIN domotica.stanze AS st ON se.id_stanza = st.id
INNER JOIN domotica.immobili AS im ON st.id_immobile = im.id