http://forum.csdn.net/SList/Oracle_Management
如上链接里的帖子列表页,如果表设计是这样的:
a表(帖子表)
a_id a_title a_content a_postman a_posttimeb表(回复表)
b_id a_id b_content b_postman b_posttime需要得到的结果是:
帖子标题 发帖人 发帖时间 回帖人 回帖时间 回复次数
如上链接里的帖子列表页,如果表设计是这样的:
a表(帖子表)
a_id a_title a_content a_postman a_posttimeb表(回复表)
b_id a_id b_content b_postman b_posttime需要得到的结果是:
帖子标题 发帖人 发帖时间 回帖人 回帖时间 回复次数
, a.a_postman
, a.a_posttime
, decode ( ( SELECT count (*)
FROM b
WHERE b.a_id = a.a_id
)
, 0, ''
, ( SELECT b.b_postman
FROM b
WHERE b.a_id = a.a_id
AND b.b_posttime = ( SELECT max(b.b_posttime) FROM b WHERE b.a_id = a.a_id )
)
) last_replier
, decode ( ( SELECT count (*)
FROM b
WHERE b.a_id = a.a_id
)
, 0, ''
, ( SELECT b.b_posttime
FROM b
WHERE b.a_id = a.a_id
AND b.b_posttime = ( SELECT max(b.b_posttime) FROM b WHERE b.a_id = a.a_id )
)
) last_reply_time
, ( SELECT count (*)
FROM b
WHERE b.a_id = a.a_id
) reply_time
FROM a;
b_postman ,b_posttime ,
count(1) over (partition by a.a_id,b.b_postman)
from a,b
where a.a_id = b.a_id
没有办法啊,你的业务决定了你的sql的复杂程度啊!如果用oracle提供的函数的话,也不一定能提高效率的。
试试,没侧过
select *
from(
select a_content ,a_postman, a_posttime ,
b_postman ,b_posttime ,
sum(decode( b.a_id,null,0,1)) over(partition by a.a_id ),
row_number() over (partition by a.a_id order by b_posttime desc) n
from a,b
where a.a_id = b.a_id(+)
)
where n=1