手上没有oracle,可以先说说思路。拿“固定值”来举例: 我的想法是两个表,一个是开始时间,一个是结束时间,然后做关联。 确定开始和结束时间可以用Row_Number()函数。select SITECODE, ITEMCODE, ITEMNAME, TIMECODE STARTTIME, AVGVALUE, EXPTYPE from( select SITECODE, ITEMCODE, ITEMNAME, TIMECODE, AVGVALUE, EXPTYPE, Row_Number() over (partition by SITECODE, ITEMCODE,AVGVALUE order by TIMECODE) thekey from ATEST where EXPTYPE='固定值' ) where thekey=1结束时间的话对TIMECODE做逆排序就是了:select SITECODE, ITEMCODE, TIMECODE ENDTIME, AVGVALUE from( select SITECODE, ITEMCODE, TIMECODE, AVGVALUE, Row_Number() over (partition by SITECODE, ITEMCODE,AVGVALUE order by TIMECODE desc) thekey from ATEST where EXPTYPE='固定值' ) where thekey=1然后两个表按主键关联就行了。 超限制也是一样的做法,就是partition by的时候用EXPTYPE列而不是AVGVALUE列,最后的AVGVALUE列用||连接字符串就是了。 最后固定值和超限制用CASE WHEN区别也行,分别计算完了在UNION也行。
select SITECODE, ITEMCODE, ITEMNAME,min(TIMECODE) 开始时间,max(TIMECODE) 结束时间, replace(wm_concat(AVGVALUE),',','、') AVGVALUE, EXPTYPE from ATEST where EXPTYPE='超限值' group by SITECODE, ITEMCODE, ITEMNAME,to_char(to_date(TIMECODE,'yyyy-mm-dd hh24:mi:ss'),'hh24')-rownum,EXPTYPE union all select SITECODE, ITEMCODE, ITEMNAME, min(TIMECODE) 开始时间,max(TIMECODE) 结束时间, AVGVALUE, EXPTYPE from ATEST where EXPTYPE<>'超限值' or EXPTYPE is null group by SITECODE, ITEMCODE, ITEMNAME,to_char(to_date(TIMECODE,'yyyy-mm-dd hh24:mi:ss'),'hh24')-rownum,EXPTYPE,AVGVALUE order by SITECODE
select f.sitecode , f.ITEMCODE,f.itemname ,f.STARTTIME 开始时间 , f.ENDTIME 结束时间,f.avgvalue ,f.exptype from ( select a.sitecode ,a.ITEMCODE,a.itemname , a.STARTTIME as STARTTIME , b.ENDTIME ENDTIME,a.avgvalue , a.exptype from (select SITECODE, ITEMCODE, ITEMNAME, TIMECODE STARTTIME, AVGVALUE, EXPTYPE from( select SITECODE, ITEMCODE, ITEMNAME, TIMECODE, AVGVALUE, EXPTYPE, Row_Number() over (partition by SITECODE, ITEMCODE,AVGVALUE order by to_char(to_date(TIMECODE,'yyyy-mm-dd hh24:mi:ss'),'hh24') asc) thekey from ATEST where EXPTYPE='固定值' ) where thekey=1) a ,(select SITECODE, ITEMCODE, ITEMNAME, TIMECODE ENDTIME, AVGVALUE, EXPTYPE from( select SITECODE, ITEMCODE, ITEMNAME, TIMECODE, AVGVALUE, EXPTYPE, Row_Number() over (partition by SITECODE, ITEMCODE,AVGVALUE order by to_char(to_date(TIMECODE,'yyyy-mm-dd hh24:mi:ss'),'hh24')desc ) thekey from ATEST where EXPTYPE='固定值' ) where thekey=1) b where a.SITECODE= b.SITECODE and a.ITEMCODE = b.ITEMCODE and a.AVGVALUE=b.AVGVALUE union allselect c.sitecode,c. ITEMCODE ,c.itemname , c.STARTTIME STARTTIME , d.ENDTIME ENDTIME,c.avgvalue ||'、'||d.avgvalue, c.exptype from ( select SITECODE, ITEMCODE, ITEMNAME, TIMECODE STARTTIME, AVGVALUE, EXPTYPE from( select SITECODE, ITEMCODE, ITEMNAME, TIMECODE, AVGVALUE, EXPTYPE, Row_Number() over (partition by SITECODE, ITEMCODE order by to_char(to_date(TIMECODE,'yyyy-mm-dd hh24:mi:ss'),'hh24') asc) thekey from ATEST where EXPTYPE='超限值' ) where thekey=1) c ,(select SITECODE, ITEMCODE, ITEMNAME, TIMECODE endTIME, AVGVALUE, EXPTYPE from( select SITECODE, ITEMCODE, ITEMNAME, TIMECODE, AVGVALUE, EXPTYPE, Row_Number() over (partition by SITECODE, ITEMCODE order by to_char(to_date(TIMECODE,'yyyy-mm-dd hh24:mi:ss'),'hh24') desc) thekey from ATEST where EXPTYPE='超限值' ) where thekey=1) d where c.SITECODE= d.SITECODE and c.ITEMCODE = d.ITEMCODE union all select sitecode,ITEMCODE,itemname , TIMECODE STARTTIME ,TIMECODE ENDTIME,avgvalue ,exptype from ATEST where exptype is null order by sitecode,ITEMCODE ) f order by f.sitecode,f.ITEMCODE ,f.STARTTIME
我的想法是两个表,一个是开始时间,一个是结束时间,然后做关联。
确定开始和结束时间可以用Row_Number()函数。select SITECODE, ITEMCODE, ITEMNAME, TIMECODE STARTTIME, AVGVALUE, EXPTYPE
from(
select SITECODE, ITEMCODE, ITEMNAME, TIMECODE, AVGVALUE, EXPTYPE,
Row_Number() over (partition by SITECODE, ITEMCODE,AVGVALUE order by TIMECODE) thekey
from ATEST
where EXPTYPE='固定值'
) where thekey=1结束时间的话对TIMECODE做逆排序就是了:select SITECODE, ITEMCODE, TIMECODE ENDTIME, AVGVALUE
from(
select SITECODE, ITEMCODE, TIMECODE, AVGVALUE,
Row_Number() over (partition by SITECODE, ITEMCODE,AVGVALUE order by TIMECODE desc) thekey
from ATEST
where EXPTYPE='固定值'
) where thekey=1然后两个表按主键关联就行了。
超限制也是一样的做法,就是partition by的时候用EXPTYPE列而不是AVGVALUE列,最后的AVGVALUE列用||连接字符串就是了。
最后固定值和超限制用CASE WHEN区别也行,分别计算完了在UNION也行。
select SITECODE, ITEMCODE, ITEMNAME,min(TIMECODE) 开始时间,max(TIMECODE) 结束时间,
replace(wm_concat(AVGVALUE),',','、') AVGVALUE, EXPTYPE
from ATEST
where EXPTYPE='超限值'
group by SITECODE, ITEMCODE, ITEMNAME,to_char(to_date(TIMECODE,'yyyy-mm-dd hh24:mi:ss'),'hh24')-rownum,EXPTYPE
union all
select SITECODE, ITEMCODE, ITEMNAME,
min(TIMECODE) 开始时间,max(TIMECODE) 结束时间,
AVGVALUE, EXPTYPE
from ATEST
where EXPTYPE<>'超限值' or EXPTYPE is null
group by SITECODE, ITEMCODE, ITEMNAME,to_char(to_date(TIMECODE,'yyyy-mm-dd hh24:mi:ss'),'hh24')-rownum,EXPTYPE,AVGVALUE
order by SITECODE
(
select a.sitecode ,a.ITEMCODE,a.itemname , a.STARTTIME as STARTTIME , b.ENDTIME ENDTIME,a.avgvalue , a.exptype
from
(select SITECODE, ITEMCODE, ITEMNAME, TIMECODE STARTTIME, AVGVALUE, EXPTYPE
from(
select SITECODE, ITEMCODE, ITEMNAME, TIMECODE, AVGVALUE, EXPTYPE,
Row_Number() over (partition by SITECODE, ITEMCODE,AVGVALUE order by to_char(to_date(TIMECODE,'yyyy-mm-dd hh24:mi:ss'),'hh24') asc) thekey
from ATEST
where EXPTYPE='固定值'
) where thekey=1) a ,(select SITECODE, ITEMCODE, ITEMNAME, TIMECODE ENDTIME, AVGVALUE, EXPTYPE
from(
select SITECODE, ITEMCODE, ITEMNAME, TIMECODE, AVGVALUE, EXPTYPE,
Row_Number() over (partition by SITECODE, ITEMCODE,AVGVALUE order by to_char(to_date(TIMECODE,'yyyy-mm-dd hh24:mi:ss'),'hh24')desc ) thekey
from ATEST
where EXPTYPE='固定值'
) where thekey=1) b
where a.SITECODE= b.SITECODE and a.ITEMCODE = b.ITEMCODE and a.AVGVALUE=b.AVGVALUE
union allselect c.sitecode,c. ITEMCODE ,c.itemname , c.STARTTIME STARTTIME , d.ENDTIME ENDTIME,c.avgvalue ||'、'||d.avgvalue, c.exptype from
(
select SITECODE, ITEMCODE, ITEMNAME, TIMECODE STARTTIME, AVGVALUE, EXPTYPE
from(
select SITECODE, ITEMCODE, ITEMNAME, TIMECODE, AVGVALUE, EXPTYPE,
Row_Number() over (partition by SITECODE, ITEMCODE order by to_char(to_date(TIMECODE,'yyyy-mm-dd hh24:mi:ss'),'hh24') asc) thekey
from ATEST
where EXPTYPE='超限值'
) where thekey=1) c ,(select SITECODE, ITEMCODE, ITEMNAME, TIMECODE endTIME, AVGVALUE, EXPTYPE
from(
select SITECODE, ITEMCODE, ITEMNAME, TIMECODE, AVGVALUE, EXPTYPE,
Row_Number() over (partition by SITECODE, ITEMCODE order by to_char(to_date(TIMECODE,'yyyy-mm-dd hh24:mi:ss'),'hh24') desc) thekey
from ATEST
where EXPTYPE='超限值'
) where thekey=1) d
where c.SITECODE= d.SITECODE and c.ITEMCODE = d.ITEMCODE
union all
select sitecode,ITEMCODE,itemname , TIMECODE STARTTIME ,TIMECODE ENDTIME,avgvalue ,exptype from
ATEST where exptype is null
order by sitecode,ITEMCODE
) f
order by f.sitecode,f.ITEMCODE ,f.STARTTIME