求SQL 语句, 有点难度

[no_com]

求SQL 语句
——————————————————————
如下表：
ＮＯ 回収日１ 回収日２ 回収日３ 回収日４ 回収日５
1 20019 20030910
2 20028 20031231 20030011 20030917 20030918 20030919
3 40001 20030910
4 40001 20030918 20030910 20030910
5 20019
6 20021 20030920 20030921 20030922 20030923 20030924
7 2002014
8 3000502
9 3000210 20030910
10 3609004 20030910
11 3609004 20030918 20030910 20030910
——————————————————
想出如下结果，就是从回収日１到5中时间的取出来。
ＮＯ 回収日１
1 20019 20030910
2 20028 20031231
3 40001 20030910
4 40001 20030918
5 20019
6 20021 20030924
7 2002014
8 3000502
9 3000210 20030910
10 3609004 20030910
11 3609004 20030918

[no_com]

补充：就是从回収日１到5中时间的最大的取出来。

[victorycyz]

select no,max(回收日1) from
(
select No,回收日1 from tablename
union
select No,回收日2 from tablename
union
select No,回收日3 from tablename
union
select No,回收日4 from tablename
union
select No,回收日5 from tablename
) T0
group by no

[yoki]

select ＮＯ,max(rd) as 回収日
from(
select ＮＯ,回収日1 as rd from table1
union all
select ＮＯ,回収日2 as rd from table1
union all
select ＮＯ,回収日3 as rd from table1
union all
select ＮＯ,回収日4 as rd from table1
union all
select ＮＯ,回収日5 as rd from table1
)t
group by ＮＯ

[mycls]

中海，小马哥，我验证了，正确，还有其他方法吗？

[victorycyz]

其他方法没有这样有效率。比如用游标。