求一SQL查询语句！

Question

SQL> select * from t;AAA        BBB     CCC
---     -----  ----
a            aaa            0
a                              0
a                              0
a                            500
a            bbb            0
a                              0
a                              0
a                              0
a                              0
a                            255
c            ccc            0
c                              012 rows selected.
希望得到结果：
AAA        BBB     CCC
---     -----  ----
a            aaa            0
a            aaa            0
a            aaa            0
a            aaa        500
a            bbb            0
a            bbb            0
a            bbb            0
a            bbb            0
a            bbb            0
a            bbb        255
c            ccc            0
c            ccc            0

Answer 1

SELECT T.AAA,A.BBB,T.CCC
FROM T,(SELECT AAA,MAX(BBB) BBB FORM T GROUP BY AAA)A
WHERE T.AAA=A.AAA

Answer 2

或者
SELECT T.AAA,A.BBB,T.CCC 
FROM T,
(SELECT *
FROM (SELECT AAA,ROW_NUMBER() OVER(PARTITION BY AAA ORDER BY BBB) RN FROM T)
WHERE RN=1)A
WHERE T.AAA=A.AAA

Answer 3

LS的兄弟，你这两个查询都不对阿。
除了CCC正确，第一个查询把第二列都变成了aaa，第二个又全变成了bbb。

Answer 4

select t.a,case when t.b is null then lag (t.b) over(partition by a order by a)
                else t.b end as b ,t.c from a t
结果：1 a aa 0
2 a aa 0
3 a 0
4 a 500
5 a bb 0
6 a bb 0
7 a 0
8 a 0
9 a 0
10 a 255
11 c cc 0
12 c cc 0
这个还有空，你在加个视图

Answer 5

select x.a,case when x.rn>1 then lag(x.b,rn-1)over(partition by x.a order by x.a)
                else x.b end as b
,x.c
from ( select t.a,t.b,t.c, row_number()over(partition by t.a order by t.a) rn
       from  a t
       )x
结果 ：
1 a aa 0
2 a aa 0
3 a aa 0
4 a aa 500
5 a aa 0
6 a aa 0
7 a aa 0
8 a aa 0
9 a aa 0
10 a aa 255
11 c cc 0
12 c cc 0楼主对着改下，就可以用了

Answer 6

一条sql可以实现．加点分我给你写，好不好？

Answer 7

SELECT AAA,AAA||AAA||AAA AS BBB,C FROM T

Answer 8

不对的
bbb空格不是aaa

Answer 9

是不是这样，呵呵
SQL> select * from t;AAA  BBB                                      CCC
---- ---- ---------------------------------------
a    aaa                                        0
a                                               0
a                                               0
a                                             500
a    bbb                                        0
a                                               0
a                                               0
a                                               0
a                                               0
a                                             255
c    ccc                                        0
c                                               012 rows selectedSQL> 
SQL> with tmp as (select rownum rn,aaa,bbb,ccc,sum(decode(bbb,null,0,rownum)) over(order by rownum) rid from t)
  2    select a.aaa,b.bbb,a.ccc
  3     from tmp a,(select * from tmp where bbb is not null)b
  4     where a.rid = b.rid
  5     order by a.rn
  6  /AAA  BBB                                      CCC
---- ---- ---------------------------------------
a    aaa                                        0
a    aaa                                        0
a    aaa                                        0
a    aaa                                      500
a    bbb                                        0
a    bbb                                        0
a    bbb                                        0
a    bbb                                        0
a    bbb                                        0
a    bbb                                      255
c    ccc                                        0
c    ccc                                        012 rows selectedSQL> 

Answer 10

 select t1.aaa,t1.bbb,t2.ccc from
(select * from t) t1,
(select * from t) t2
 where t1.aaa(+)=t2.aaa 
 and t1.bbb is not null 
 order by t1.rowid desc,t2.rowid desc查询结果是：
1 a aaa 0  
2 a aaa 0  
3 a aaa 0  
4 a aaa 500
5 a aaa 0  
6 a aaa 0  
7 a aaa 0  
8 a aaa 0  
9 a aaa 0  
10 a aaa 255
11 a bbb 0  
12 a bbb 0  
13 a bbb 0  
14 a bbb 500
15 a bbb 0  
16 a bbb 0  
17 a bbb 0  
18 a bbb 0  
19 a bbb 0  
20 a bbb 255
21 c ccc 0  
22 c ccc 0  中间去掉应该就是了，楼主再改改。