表数据如下: a b c d1 A B 1 2 A A null 3 A A null 4 C D 2 5 C C NULL 6 E Z 3 7 E E NULL 8 E E NULL 9 X Y 4 10 X X NULL 所要的结果为:a b c d1 A B 1 2 A A 1 3 A A 1 4 C D 2 5 C C 2 6 E Z 3 7 E E 3 8 E E 3 9 X Y 4 10 X X 4求各为高手了,谢谢!!
SELECT TA.A,TA.B,TA.C,TB.D FROM TABLE TA INNER JOIN (SELECT B,MAX(D) D FROM TABLE GROUP BY B) TB ON TA.B=TB.B;--------------------- A B C D --------- ---- ---- ---------- 1 A B 1 2 A A 1 3 A A 1 4 C D 2 5 C C 2 6 E Z 3 7 E E 3 8 E E 3 9 X Y 4 10 X X 4
select a,decode(lag,'',d,lag) from (select a,d,lag(d)over(order by a)lag from a)
INNER JOIN (SELECT B,MAX(D) D FROM TABLE GROUP BY B) TB ON
TA.B=TB.B;---------------------
A B C D
--------- ---- ---- ----------
1 A B 1
2 A A 1
3 A A 1
4 C D 2
5 C C 2
6 E Z 3
7 E E 3
8 E E 3
9 X Y 4
10 X X 4
(select a,d,lag(d)over(order by a)lag from a)