100分求一sql语句

[imho888]

表 A

time vale1 value2
11:00 2 3
11:30 4 5
11:00 3 3
11:30 4 3


sql语句，得到如下结果


time vale1 value2
11:00 2 3
11:30 4 5

其实就是去掉time字段重复的记录，显示所有字段值，重复记录取哪一条都可以。


sqlserver 的DISTINCT用法是可以做到的，但是orcale里的DISTINCT用法却做不到。


各位帮帮忙，谢谢。

[zhjj1234]

select * from A T1
WHERE T1.ROWID=(SELECT MIN(ROWID) FROM A T2 WHERE TIME=T1.TIME GROUP BY TIME HAVING COUNT(TIME)>1)

[horizonlyhw]

with t as(
select '11:00' time1, '2' value1,'3' value2 from dual
union all
select '11:30' time1, '4' value1,'5' value2 from dual
union all
select '11:00' time1, '3' value1,'3' value2 from dual
union all
select '11:30' time1, '4' value1,'3' value2 from dual

)
select tt.time1,tt.value1,tt.value2
from (
select t.time1,t.value1,t.value2,row_number() over(partition by t.time1 order by t.value1) rn
from t) tt
where tt.rn = 1

[槑党--一缕风]

[Quote=引用 2 楼 的回复:]
SQL code


with t as(
select '11:00' time1, '2' value1,'3' value2 from dual
union all
select '11:30' time1, '4' value1,'5' value2 from dual
union all
select '11:00' time1, '3' value1,'3' value……
[/Quote]

这一个可以我测试了~~~

[coolkisses]

借用滤重的语句也可以

select * from 
   from table
  where rowid in
     ( select max(rowid) from table
             group by time)

[xuyongjun5124020]

select * from temp where rowid in (select max(rowid) from temp group by time)

[大明湖畔_帅锅]

为啥上面都写的这么麻烦呢？ 这样可以么

select time ,
       max(vale1), 
       max(value2)
from A
group by time

[zayx88]

用row_number()(partition by 字段 order by 字段 ) 是比较好的方法！

[Kobayashi]

select time ,min(vale1) ,max(vale2) from table group by time