SQL查询，难倒我了，谁能帮帮忙，谢了

Question

有这样的数据库表，存储每次设备发送的故障信息
EQUIP_ID,ERROR_CODE,UPDATE_TIME
001,    1,    2011-10-22 08:01:00
001,    1,    2011-10-22 08:01:00
002,    2,    2011-10-22 08:00:00
002,    2,    2011-10-22 07:50:00
002,    1,    2011-10-22 07:45:00
002,    1,    2011-10-22 07:40:00
002,    1,    2011-10-22 07:30:00我想查询出这样的结果，过滤掉重复上传的信息，保留每个设备每个故障最新（最后）上传的的一条。
EQUIP_ID,ERROR_CODE,UPDATE_TIME
001,    1,    2011-10-22 08:01:00
002,    2,    2011-10-22 08:00:00
002,    1,    2011-10-22 07:45:00

Answer 1

select m.* from tb m where not exists(select 1 from tb n where n.EQUIP_ID = m.EQUIP_ID and n.UPDATE_TIME > m.UPDATE_TIME)select m.* from tb m where m.UPDATE_TIME = (select max(UPDATE_TIME) from tb n where n.EQUIP_ID = m.EQUIP_ID)

Answer 2

也可以用row_number函数
SELECT EQUIP_ID, ERROR_CODE, UPDATE_TIME
  FROM (SELECT EQUIP_ID,
               ERROR_CODE,
               UPDATE_TIME,
               ROW_MUNBER() OVER(PARTITION BY EQUIP_ID, ERROR_CODE ORDER BY UPDATE_TIME DESC) RN
          FROM TAB1)
 WHERE RN = 1

Answer 3

select EQUIP_ID,ERROR_CODE,UPDATE_TIME
(
select EQUIP_ID,ERROR_CODE,UPDATE_TIME,
row_number()over(partition by EQUIP_ID,ERROR_CODE,UPDATE_TIME order by update_time desc) rn
from tb
)where rn=1

Answer 4

select EQUIP_ID,ERROR_CODE,UPDATE_TIME
(
select EQUIP_ID,ERROR_CODE,UPDATE_TIME,
row_number()over(partition by EQUIP_ID,ERROR_CODE order by update_time desc) rn
from tb
)where rn=1

Answer 5

用 row_number 函数比较好

Answer 6

简单分组就可以做到。SELECT equipId, errorcode, MAX(updatetime) FROM t3
GROUP BY equipid, errorcode;

Answer 7

你的需求 应该一个group by 就可以了SELECT t.equip_id,error_code,MAX(update_time) FROM t
GROUP BY t.equip_id,t.error_code
ORDER BY t.equip_id,t.error_code

Answer 8

不好意思，表数据不全，让大家误解了，直接group by不可以。
修改后如下：有这样的数据库表，存储每次设备发送的故障信息
EQUIP_ID,ERROR_CODE,UPDATE_TIME
001, 1, 2011-10-22 08:10:00
001, 1, 2011-10-22 08:02:00
002, 2, 2011-10-22 08:00:00
002, 2, 2011-10-22 07:50:00
002, 1, 2011-10-22 07:45:00
002, 1, 2011-10-22 07:40:00
002, 1, 2011-10-22 07:30:00
001, 1, 2011-10-22 07:10:00
001, 1, 2011-10-22 07:08:00我想查询出这样的结果，过滤掉重复上传的信息，保留每个设备每个故障最早上传的的一条。
EQUIP_ID,ERROR_CODE,UPDATE_TIME
001, 1, 2011-10-22 08:02:00
002, 2, 2011-10-22 07:50:00
002, 1, 2011-10-22 07:30:00
001, 1, 2011-10-22 07:08:00

Answer 9

EQUIP_ID,ERROR_CODE,UPDATE_TIME
001, 1, 2011-10-22 08:02:00
002, 2, 2011-10-22 07:50:00
002, 1, 2011-10-22 07:30:00
001, 1, 2011-10-22 07:08:00
怎么还有两条呢???不能用Group by就用row_number试试看
SELECT EQUIP_ID, ERROR_CODE, UPDATE_TIME
  FROM (SELECT EQUIP_ID,
               ERROR_CODE,
               UPDATE_TIME,
               ROW_MUNBER() OVER(PARTITION BY EQUIP_ID, ERROR_CODE ORDER BY UPDATE_TIME DESC) RN
          FROM TAB1)
 WHERE RN = 1

Answer 10

EQUIP_ID,ERROR_CODE,UPDATE_TIME
001, 1, 2011-10-22 08:10:00
001, 1, 2011-10-22 08:02:00
002, 2, 2011-10-22 08:00:00
002, 2, 2011-10-22 07:50:00
002, 1, 2011-10-22 07:45:00
002, 1, 2011-10-22 07:40:00
002, 1, 2011-10-22 07:30:00
001, 1, 2011-10-22 07:10:00
001, 1, 2011-10-22 07:08:00按照你的需求：每个设备每个故障最早上传的的一条。
上面的4条记录不是同一个设备同一个故障吗？你要得结果是怎么变成下面的？
EQUIP_ID,ERROR_CODE,UPDATE_TIME
001, 1, 2011-10-22 08:02:00
002, 2, 2011-10-22 07:50:00
002, 1, 2011-10-22 07:30:00
001, 1, 2011-10-22 07:08:00

Answer 11

你这是要统计连续出现故障的最新一条吗？
比如先出现2次故障1（A，B），再出现3次故障2（C，D，E），然后再出现2次故障1（F，G），
你时间要统计的是B，E，G，不知道这样理解是否正确！

Answer 12

--是否这样的
----------------------------->
SQL> 
SQL> with t as (
  2  select  '001' EQUIP_ID,1 ERROR_CODE,'2011-10-22 08:01:00' UPDATE_TIME from dual
  3  union all
  4  select  '001' EQUIP_ID,1 ERROR_CODE,'2011-10-22 08:01:00' UPDATE_TIME from dual
  5  union all
  6  select  '002' EQUIP_ID,2 ERROR_CODE,'2011-10-22 08:00:00' UPDATE_TIME from dual
  7  union all
  8  select  '002' EQUIP_ID,2 ERROR_CODE,'2011-10-22 07:50:00' UPDATE_TIME from dual
  9  union all
 10  select  '002' EQUIP_ID,1 ERROR_CODE,'2011-10-22 07:45:00' UPDATE_TIME from dual
 11  union all
 12  select  '002' EQUIP_ID,1 ERROR_CODE,'2011-10-22 07:40:00' UPDATE_TIME from dual
 13  union all
 14  select  '002' EQUIP_ID,1 ERROR_CODE,'2011-10-22 07:30:00' UPDATE_TIME from dual
 15  union all
 16  select  '001' EQUIP_ID,1 ERROR_CODE,'2011-10-22 07:10:00' UPDATE_TIME from dual
 17  union all
 18  select  '001' EQUIP_ID,1 ERROR_CODE,'2011-10-22 07:08:00' UPDATE_TIME from dual)
 19  SELECT EQUIP_ID, ERROR_CODE, MIN(UPDATE_TIME) FROM t
 20  GROUP BY EQUIP_ID, ERROR_CODE;
 
EQUIP_ID ERROR_CODE MIN(UPDATE_TIME)
-------- ---------- -------------------
002               2 2011-10-22 07:50:00
002               1 2011-10-22 07:30:00
001               1 2011-10-22 07:08:00
 
SQL> 

Answer 13

select distinct EQUIP_ID,ERROR_CODE,(case when lag(EQUIP_ID,1,'000') over(order by UPDATE_TIME) =EQUIP_ID and lag(ERROR_CODE,1,'000') over(order by UPDATE_TIME) =ERROR_CODE then lag(UPDATE_TIME,1,'000') over(order by UPDATE_TIME) else UPDATE_TIME end) col1 from tab1 

Answer 14

select distinct EQUIP_ID,ERROR_CODE,
(case when lag(EQUIP_ID,1,'000') over(order by UPDATE_TIME) =EQUIP_ID 
and lag(ERROR_CODE,1,'0') over(order by UPDATE_TIME) =ERROR_CODE then lag(UPDATE_TIME,1,UPDATE_TIME) over(order by UPDATE_TIME) 
else UPDATE_TIME end) col1 
from tab1

Answer 15

select distinct EQUIP_ID,ERROR_CODE,
(case when lag(EQUIP_ID,1,'000') over(order by UPDATE_TIME) =EQUIP_ID 
and lag(ERROR_CODE,1,'0') over(order by UPDATE_TIME) =ERROR_CODE then FIRST_VALUE(UPDATE_TIME,1,UPDATE_TIME) over(PARTITION BY EQUIP_ID,ERROR_CODE order by UPDATE_TIME) 
else UPDATE_TIME end) col1 
from tab1

Answer 16

感谢superhsj及各位的回复，
学习了一下oracle中的分析函数的用法
不能按EQUIP_ID,ERROR_CODE排序，只能以EQUIP_ID,UPDATE_TIME为序，然后针对EQUIP_ID,ERROR_CODE的每一段取最早的时间，下面代码将每一段不是最早时间的行赋值null，然后通过嵌套查询过滤掉为null的就可以了。
自己修改一下superhsj的SQL，最终结果如下：select *
  from (select EQUIP_ID,
               ERROR_CODE,
               (case
                 when lag(EQUIP_ID, 1) over(order by EQUIP_ID, UPDATE_TIME) = EQUIP_ID and 
                      lag(ERROR_CODE, 1) over(order by EQUIP_ID, UPDATE_TIME) = ERROR_CODE 
                then
                  null
                else
                  UPDATE_TIME
               end) col1
          from tab1) t
 where col1 is not null
 order by col1

Answer 17

针对以上问题再扩展一下：
假定故障0是正常状态，其他的都是故障状态，我要查出的表要增加一个故障恢复时间，就是收到故障后，最近的一次0状态记录的时间。源数据表如下：
EQUIP_ID,ERROR_CODE,UPDATE_TIME
001,    0,    2011-10-22 08:12:00
001,    0,    2011-10-22 08:11:00
001,    1,    2011-10-22 08:10:00
001,    1,    2011-10-22 08:02:00
001,    0,    2011-10-22 07:55:00
001,    2,    2011-10-22 07:50:00
001,    2,    2011-10-22 07:40:00
001,    1,    2011-10-22 07:10:00
001,    1,    2011-10-22 07:08:00查询的结果要求如下：
EQUIP_ID, ERROR_CODE, UPDATE_TIME, RECOVER_TIME
001,    1,    2011-10-22 08:02:00,    2011-10-22 08:11:00
001,    2,    2011-10-22 07:40:00,    2011-10-22 07:55:00
001,    1,    2011-10-22 07:08:00,    2011-10-22 07:55:00后两条的故障的恢复时间相同，实际情况是这样，设备发生了故障1，还没修呢又发生了故障2，之后这两个故障一起被修好了。

Answer 18

with t as (
select '001' EQUIP_ID, 0 ERROR_CODE, to_date('2011-10-22 08:12:00','yyyy-mm-dd hh24:mi:ss') UPDATE_TIME from dual union all
select '001', 0, to_date('2011-10-22 08:11:00','yyyy-mm-dd hh24:mi:ss') from dual union all
select '001', 1, to_date('2011-10-22 08:10:00','yyyy-mm-dd hh24:mi:ss') from dual union all
select '001', 1, to_date('2011-10-22 08:02:00','yyyy-mm-dd hh24:mi:ss') from dual union all
select '001', 0, to_date('2011-10-22 07:55:00','yyyy-mm-dd hh24:mi:ss') from dual union all
select '001', 2, to_date('2011-10-22 07:50:00','yyyy-mm-dd hh24:mi:ss') from dual union all
select '001', 2, to_date('2011-10-22 07:40:00','yyyy-mm-dd hh24:mi:ss') from dual union all
select '001', 1, to_date('2011-10-22 07:10:00','yyyy-mm-dd hh24:mi:ss') from dual union all
select '001', 1, to_date('2011-10-22 07:08:00','yyyy-mm-dd hh24:mi:ss') from dual
)
select * 
from
  (select B.equip_id,B.error_code,B.update_time,first_value(update_time) over(partition by B.g order by B.error_code) recover_time
  from
    (select equip_id,g,error_code,min(update_time) update_time
    from 
      (select t.*,sum(decode(error_code,0,1,0)) over(order by equip_id,update_time desc) g from t) A
    group by equip_id,g,error_code
    ) B
  )
where error_code <> 0
order by update_time;
EQUIP_ID ERROR_CODE UPDATE_TIME RECOVER_TIME
-------- ---------- ----------- ------------
001               1 2011-10-22  2011-10-22 7
001               2 2011-10-22  2011-10-22 7
001               1 2011-10-22  2011-10-22 8