在oracle中编辑请求(添加正确的连接)

Question

我有一个查询，它运行良好，但我想添加另一列！

SELECT 
 typ.name as Silo_name,
 tr.DEVID,
 tr.name as dev_name
 FROM 
   HANGINGTHREAD_SILO ev 
   LEFT JOIN SILO typ  
       ON ev.ID_SILO = typ.id 
   LEFT JOIN IOT_DEVICES  tr
       ON ev.DEVICES_ID = tr.id 

但是我想添加另一个列的最高温度，这个列的数据在表TEMPR_SILO中。下面是一个查询，用于查找每个tr.DEVID所需的数据。

select MAX(to_char(TEMP,'99.99')) from TEMPR_SILO where ID_TRANS in (select max(ID_TRANS) from TEMPR_SILO)   and NAME in (select NAME from SILO_SENSOR where DEVICES_ID = tr.DEVID)

我试过这样做，但我犯了错误

"ORA-20999:未能解析SQL查询！

ORA-06550:第17行，第3列: ORA-00933: SQL命令未正确结束“

SELECT 
 typ.name as Silo_name,
 tr.DEVID,
 tr.name as dev_name
 ,max(ts.TEMP)  AS "Lowest salary"
 FROM 
   HANGINGTHREAD_SILO ev 
   LEFT JOIN SILO typ  
       ON ev.ID_SILO = typ.id 
   LEFT JOIN IOT_DEVICES  tr
       ON ev.DEVICES_ID = tr.id 
   LEFT JOIN  TEMPR_SILO  ts
     ON ts.name  in (select NAME from SILO_SENSOR where DEVICES_ID = tr.DEVID)
     and  ts.ID_TRANS in(select max(ID_TRANS) from TEMPR_SILO) 
GROUP BY  
 ev.id,
 typ.name as Silo_name,
 tr.DEVID,
 tr.name as dev_name ;

Answer 1

这是我的选择，它起作用了)

for MAX
SELECT 
 typ.name as Silo_name,
 tr.DEVID,
 ts.temp as max_temp,
 tr.name as dev_name
 FROM 
   HANGINGTHREAD_SILO ev 
   LEFT JOIN SILO typ  
       ON ev.ID_SILO = typ.id 
   LEFT JOIN IOT_DEVICES  tr
       ON ev.DEVICES_ID = tr.id 
   LEFT JOIN  SILO_SENSOR er   
   ON er.DEVICES_ID =  tr.DEVID
   LEFT JOIN  TEMPR_SILO  ts
    ON ts.NAME = er.name
   LEFT JOIN  TEMPR_SILO  ns
    ON  ns.NAME = er.name 
where ts.temp in (select max(TEMP) from TEMPR_SILO
                    where ID_TRANS in (select max(ID_TRANS) from TEMPR_SILO)
                    and NAME in (select NAME from SILO_SENSOR where DEVICES_ID = tr.DEVID))

group by
 typ.name ,
 tr.DEVID,
 ts.temp, 
 tr.name

​

Answer 2

我不知道你所面临的问题是什么。

如果您在最后一个问题中遇到了问题，那么我必须说，我在以下查询中强调了多个问题：

SELECT
    TYP.NAME   AS SILO_NAME,
    TR.DEVID,
    TR.NAME    AS DEV_NAME,
    MAX(TS.TEMP) AS MAX_TEMP --"Lowest salary" -- just changed the name, not necessary in your case
FROM
    HANGINGTHREAD_SILO EV
    LEFT JOIN SILO TYP ON EV.ID_SILO = TYP.ID
    LEFT JOIN IOT_DEVICES TR ON EV.DEVICES_ID = TR.ID
    LEFT JOIN SILO_SENSOR SS ON SS.DEVICES_ID = TR.DEVID -- added this left join as JOIN of TS with it is not correct in your query
    LEFT JOIN TEMPR_SILO TS ON TS.NAME = SS.NAME -- added this join condition
                               AND TS.ID_TRANS IN (
        SELECT
            MAX(ID_TRANS)
        FROM
            TEMPR_SILO
    )
GROUP BY
    EV.ID,
    TYP.NAME, --as Silo_name, -- "as" is not allowed here
    TR.DEVID,
    TR.NAME; -- as dev_name -- "as" is not allowed here