SQL查询实时数据排除如果第一次发现
问题描述:
我想从我们AspenTech公司的IP 21的服务器查询数据和现在用的是下面的查询其他时间SQL查询实时数据排除如果第一次发现
SELECT s.IP_TREND_VALUE AS "Weight", s.IP_TREND_TIME AS TIMES
From "wtTotal" as s
WHERE s.IP_TREND_TIME like '__________05:59:00.%' AND s.IP_TREND_TIME between '1-JUN-17 05:59:00' and '15-JUN-17 06:00:00'
OR s.IP_TREND_TIME like '__________06:00:00.%' AND s.IP_TREND_TIME between '1-JUN-17 05:59:00' and '15-JUN-17 06:00:00'
的问题是,有些天就有一个数据点在5:59和其他人在6点的数据点。一些在两个5:59有数据,并在6,我想只有拉的每一天,从来没有一个数据点的一个或两者在5:59和6
答
with CTE as
(
SELECT s.IP_TREND_VALUE AS "Weight", s.IP_TREND_TIME AS TIMES,
row_number()
over (partition by to_char(IP_TREND_TIME, 'YYYYMMDD')
order by IP_TREND_TIME asc) as rn -- change the order by to change which value to select
From "wtTotal" as s
WHERE s.IP_TREND_TIME like '__________05:59:00.%' AND s.IP_TREND_TIME between '1-JUN-17 05:59:00' and '15-JUN-17 06:00:00'
OR s.IP_TREND_TIME like '__________06:00:00.%' AND s.IP_TREND_TIME between '1-JUN-17 05:59:00' and '15-JUN-17 06:00:00'
)
select *
from CTE
where RN = 1
你愿意得到0559或0600的值? – JohnHC