悬赏，一个SQL语句如何拼写。（肯定接贴）

[zhaoyongzhu]

表结构为：
SQL> select * from test1;

ID ZYZ1 P1 DOCTIME
---------- ---------- ---------- ----------
1 dfdf 2
1 2 01-5ÔÂ -02
1 2 20-8ÔÂ -02
1 df0 2


求id和p1的第二大值,即将id和p1安desc排序后的第二条纪录为第二大值。
在上面的数据中求出的第二大值应该为2

[bzszp]

select decode(sign(max(id)-max(pl)),1,max(pl),max(id)) from test1;
这样试试。

[bzszp]

SQL> select * from a5;

BBB CCC
--------- ---------
1 2
1 3
4 3
2 4
4 5

SQL> select decode(sign(max(bbb)-max(ccc)),1,max(ccc),max(bbb)) second from a5;

SECOND
---------
4

[yuxuan]

同意楼上的

[zhaoyongzhu]

to:bzszp(SongZip)
max(id)在id和p1所有的值中有可能只是第8大或。。。。。，所以你的SQL肯定不行。
不过非常感谢。

[bzszp]

select aaa from (
select aaa from (
select id aaa from test1
union all
select pl aaa from test1)order by aaa desc) where rownum<3
minus
select aaa from (
select id from (
select pl aaa from test1
union all
select ccc aaa from test1)order by aaa desc) where rownum<2;
这样一般没问题

[bzszp]

select aaa from (
select aaa from (
上面有点笔误
select id aaa from test1
union all
select pl aaa from test1)order by aaa desc) where rownum<3
minus
select aaa from (
select aaa from (
select pl aaa from test1
union all
select ccc aaa from test1)order by aaa desc) where rownum<2;

[hdkkk]

select ID,ZYZ1,P1,DOCTIME,rank() over(order by id,p1) as rk from test1 where rk=2;

[zhaoyongzhu]

非常感谢给位的参与。
问题虽然还没有解决，但找到了问题的根源了。

给分。

[javafish]

楼主自己好像都不知道要干什么？大家都在猜测着了，‘union all’？什么类型？怎么连接，哪个在前？都不知道！大家回答了这么多，你怎得把问题说清楚三

[bzszp]

SQL> select * from a5;

BBB CCC
--------- ---------
1 2
1 3
4 3
2 4
4 5

SQL> select aaa from (
2 select aaa from (
3 select bbb aaa from a5
4 union all
5 select ccc aaa from a5)order by aaa desc) where rownum<3
6 minus
7 select aaa from (
8 select aaa from (
9 select bbb aaa from a5
10 union all
11 select ccc aaa from a5)order by aaa desc) where rownum<2;

AAA
---------
4

[javafish]

select id from (
select id from
(select id id from test1 union select pl id from test1 ) order by id desc)
where rownum <= 2;

[bzszp]

select * from (select * from test1 order by pl desc) where rownum=2;
这个意思么？

[zhaoyongzhu]

where rownum=2;肯定报错呀！！！！

[zhaoyongzhu]

sorry,用where rownum=2不饱错。但却得不到我想要的结果。

[bzszp]

select ID,ZYZ1,P1, DOCTIME from (select rownum id,ID,ZYZ1,P1, DOCTIME from test1 order by pl desc) where id=2;
这样呢？

[duckcn]

select * from (select * from test1 order by id, pl desc) where rownum=2;

[zcs_1]

select * from
(select * from test1 order by id desc,p1 desc) where rownum <= 2
minus
select * from
(select * from test1 order by id desc,p1 desc) where rownum <= 1

[zhaoyongzhu]

我是求id和p1的第二大值.
即将id和p1的值union all成一个字段后安desc排序后的第二条纪录为第二大值。

[zhaoyongzhu]

sorry是我没说清，希望大家继续努力。多谢了。
参与者肯定有分

[javafish]

如果你的id和pl是一一对应的话还可以理解，要不就不懂了
select * from (select id，pl from test1 group by id,pl order by id, pl desc) where rownum <= 2;