再問：得到相同NO對應Q的最後一次變更的DATE

[dy15]

table :test
DATE NO Q
2005/07/14 001 5
2005/07/15 001 6
2005/07/16 001 7
2005/07/17 001 7
2005/07/18 001 7
2005/07/17 002 6
2005/07/13 003 3
2005/07/14 003 4
2005/07/15 003 4
2005/07/16 003 4
2005/07/17 003 2
2005/07/18 003 2

要得到相同NO對應Q的最後一次變更的DATE
2005/07/16 001 7
2005/07/17 002 6
2005/07/17 003 2

謝謝

[w_tsinghua]

select min(date),no , Q FROM test group by no,q

[子陌红尘]

select
min(a.DATE) as DATE,
a.NO,
a.Q
from
test a
where
not exists(select * from test where DATE>=a.DATE and NO=a.NO and Q<>a.Q)
group by
a.NO,a.Q

[沝林]

SQL> select * from (
2 select to_char(date1,'yyyy/mm/dd') "date",no,q,
3 row_number() over(partition by no order by date1 desc) rn1
4 from
5 (select t.*,row_number() over(partition by no,q order by date1) rn
6 from test t
7 ) where rn = 1
8 ) where rn1 = 1
9 /

date NO Q RN1
---------- ---------- ---------- ----------
2005/07/16 001 7 1
2005/07/17 002 6 1
2005/07/17 003 2 1

[沝林]

SQL> select * from (
2 select to_char(date1,'yyyy/mm/dd') "date",no,q,
3 row_number() over(partition by no order by date1 desc) rn1
4 from (select min(date1) date1,no ,Q FROM test group by no,q)
5 ) where rn1 = 1
6 /

date NO Q RN1
---------- ---------- ---------- ----------
2005/07/16 001 7 1
2005/07/17 002 6 1
2005/07/17 003 2 1

[bzszp]

前两天不是提过这个问题吗？
还没解决？

[小李木耳]

回复人： w_tsinghua() ( ) 信誉：105 2005-07-19 13:21:00 得分: 0


select min(date),no , Q FROM test group by no,q

==>select max(date),no , Q FROM test group by no,q