0

## [Oracle] 無法得到正確的查詢結果....幫幫忙!!!!

https://imgur.com/87Z1z2V

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss' ;
SELECT NODE_NAME, ENV_DATE, ENV_CONS
FROM station s, riskdata r
WHERE s.node_id = r.node_id
AND r.sen_num in
(SELECT sen_num FROM station
WHERE node_range ='小港機場' OR NODE_RANGE='新北市三重區')
AND r.sen_num='一氧化碳分析儀'
and r.ENV_CONS IN
(SELECT ENV_CONS FROM riskdata
WHERE rownum <=3);

PS:小港機場的NODE_ID為S001，SEN_NUM為T02；

### 1 個回答

0
rogeryao
iT邦大師 2 級 ‧ 2019-05-30 00:55:00

``````SELECT TEMPTABLE.NODE_RANGE,TEMPTABLE.ROW_NUM,TEMPTABLE.NODE_NAME,TEMPTABLE.ENV_DATE,TEMPTABLE.ENV_CONS
FROM (
SELECT
ROW_NUMBER() OVER(ORDER BY RISKDATA.ENV_CONS DESC) ROW_NUM,
STATION.NODE_RANGE,STATION.NODE_NAME,RISKDATA.ENV_CONS,RISKDATA.ENV_DATE
FROM RISKDATA
LEFT JOIN SENSOR ON SENSOR.NODE_ID=RISKDATA.NODE_ID AND SENSOR.SEN_NUM=RISKDATA.SEN_NUM
LEFT JOIN STATION ON STATION.NODE_ID=SENSOR.NODE_ID
WHERE 1=1
AND SENSOR.SEN_NAME='一氧化碳分析儀'
AND ((STATION.NODE_RANGE LIKE '%小港機場%') OR (STATION.NODE_RANGE LIKE '%三重%'))
) "TEMPTABLE"
WHERE 1=1
AND TEMPTABLE.ROW_NUM <= 3
ORDER BY TEMPTABLE.NODE_RANGE,TEMPTABLE.ROW_NUM,TEMPTABLE.NODE_NAME ;
``````

miyokoya iT邦新手 5 級 ‧ 2019-05-30 13:36:29 檢舉