数据按日期排正序,若有连续的col2,则取最小日期及col2显示
否则,显示日期和col2
表数据为:
2000/01/02 A
2000/04/01 A
2003/01/01 B
2004/02/03 B
2005/02/02 C
2006/01/01 A
SQL语句的结果为:
2000/01/02 A
2003/01/01 B
2005/02/02 C
2006/01/01 A
【Solved By sonic】
SQL> SELECT * FROM ZTEST;
A B
--------------- ----------
2000-1-2 A
2000-4-1 A
2003-1-1 B
2004-2-3 B
2005-2-2 C
2006-1-1 A
6 rows selected
SQL> SELECT A, B
2 FROM (SELECT T.*,
3 LAG(T.A) OVER(ORDER BY T.A) PA,
4 LAG(T.B) OVER(ORDER BY T.A) PB
5 FROM ZTEST T) WHERE (PA <> A AND PB <> B) OR PA IS NULL
6 /
A B
--------------- ----------
2000-1-2 A
2003-1-1 B
2005-2-2 C
2006-1-1 A
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29867/viewspace-813570/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29867/viewspace-813570/