查询结果怎么去除重复的数据请各位大哥大姐帮忙看看

Question

select * from
        (select
        distinct a.ELEVID,
        a.alarmflag,
        a.IDENT,
        REPLACE(a.REGCODE,'-','') as REGCODE ,
        a.LOCATION,
        a.MFRNAME,
        a.USEUNITNAME,
        a.USEUNITADR,
        a.MAINNAME,
        a.DTC,
        a.elevcode,
        a.state,
        case nvl(isnum,0) when 0 then '0' else '1' end as isFlag,
        b.accidentid,
        a.ELIGIBILITYNO,
        a.areacode
        from ELEV_ELEVATORINFO a
        left join (
        select  ELEVATORID,count(ACCIDENTID) as isnum,accidentid,sydwmc,address,max(createtime)
        from ELEV_ACCIDENT
        where ACCIDENTSTATUS in('0','1','2','3')
        and isdel=0
        group by ELEVATORID,accidentid,sydwmc,address,createtime        ) b on b.ELEVATORID=a.ELEVID
        left join (
             select mainid,mainname from elev_maintenanceunit
        ) c on c.mainid=a.mainid
        where (((a.isdel=0)))) k
where 1=1 
and
(regcode = '31104401001987010034')
oracle

Answer 1

试试这个行不行，
select a.ELEVID,a.alarmflag,a.ident,a.regcode
  from (select distinct a.ELEVID,
                        a.alarmflag,
                        a.IDENT,
                        REPLACE(a.REGCODE, '-', '') as REGCODE,
                        a.LOCATION,
                        a.MFRNAME,
                        a.USEUNITNAME,
                        a.USEUNITADR,
                        a.MAINNAME,
                        a.DTC,
                        a.elevcode,
                        a.state,
                        case nvl(isnum, 0)
                          when 0 then
                           '0'
                          else
                           '1'
                        end as isFlag,
                        b.accidentid,
                        a.ELIGIBILITYNO,
                        a.areacode
          from ELEV_ELEVATORINFO a
          left join (select ELEVATORID,
                           count(ACCIDENTID) as isnum,
                           accidentid,
                           sydwmc,
                           address,
                           max(createtime)
                      from ELEV_ACCIDENT
                     where ACCIDENTSTATUS in ('0', '1', '2', '3')
                       and isdel = 0
                     group by ELEVATORID,
                              accidentid,
                              sydwmc,
                              address,
                              createtime) b
            on b.ELEVATORID = a.ELEVID
          left join (select mainid, mainname from elev_maintenanceunit) c
            on c.mainid = a.mainid
         where a.isdel = 0        
        ) k
 where 1 = 1
   and regcode = '31104401001987010034' and rownum<=1

Answer 2

你可以使用rowid，将rowid大的全部删除就可以了

Answer 3

我是这样想的：
  在最外一层对所有的字段进行group by ,在having 条件中取count(*)=1的。
这样肯定能把重复的去掉。

Answer 4

select *
  from table1 a
 where a.rowid in (select max(b.rowid)
                     from table2 b
                    where a.id = b.id
                      and a.name = b.name
                      )如果查询这个表有条件写在（）里边的where后边，（）的条件可以随需求改变。