Question

oracle 10g子查询中使用rownum取到同样的值，为什么？

create table m_title(
tid number(10) primary key
);
create table m_comment(
cid number(10) primary key,
tid number(10),
constraint tid_fk foreign key (tid) references m_title(tid)
);
insert into m_title values(1);
insert into m_title values(2);
insert into m_title values(3);
insert into m_comment values(1,1);
insert into m_comment values(2,1);
insert into m_comment values(3,2);
insert into m_comment values(4,1);
insert into m_comment values(5,3);
insert into m_comment values(6,2);
commit;

--查找id最大的一条评论记录
select t.tid,
(select cid
from (select c.cid
from m_comment c
where c.tid = t.tid
order by c.cid desc)--cid倒序
where rownum = 1) as cid--倒序之后取第一条，也就是最大
from m_title t;
结果：
1 1 6
2 2 6
3 3 6

为什么取到都是一样的cid？如果改成select max(c.cid)就没有问题。

Answer 1

其实你sql嵌套太多层了 不建议你上面的语句。我刚刚把你的语句拷贝出来 好像不能执行。
你可以看看下面的
select t.tid,(select max(c.cid) from m_comment c where c.tid = t.tid) as cid
from m_title t;
或者
select t.tid,
(select tt.cid from (select row_number() over(partition by tid order by cid desc) rn,tid,cid from m_comment) tt where tt.rn=1 and tt.tid = t.tid)
from m_title t
希望可以帮到你。

Answer 2

方案1：select t.tid,max(c.cid)
from m_title t,m_comment c
where t.tid = c.tid
group by t.tid;

方案2：按你的SQL写法

select t.tid,
(select cid
from (select c.cid,tid
from m_comment c
where c.tid = tid
order by c.cid desc)--cid倒序
where tid = t.tid and rownum = 1) as cid--倒序之后取第一条，也就是最大
from m_title t;
--你得把tid传到上一层的子查询，然后与m_title的tid关联。

追问

是可以了，可是为什么把关联条件放上一层就可以呢？

Answer 3

如果没看错的话，把where rownum=1放进最里面应该可以把

Answer 4

你这样永远都会取6的，使用ROW_NUMBER() OVER(PARTITION BY t.tid order by c.cid DESC) rn