select id,uname from sysusers where id in
(select sysuser_id from
(select distinct sysuser_id from docomman where docomexam_id in(select id from docomexam where sysuserid = 4 order by id desc ))
where rownum <=10 );提示是第三行order by前面缺失右括号
(select sysuser_id from
(select distinct sysuser_id from docomman where docomexam_id in(select id from docomexam where sysuserid = 4 order by id desc ))
where rownum <=10 );提示是第三行order by前面缺失右括号
SELECT ID, uname
FROM sysusers
WHERE ID IN (SELECT sysuser_id
FROM (SELECT DISTINCT sysuser_id
FROM docomman
WHERE docomexam_id IN (SELECT ID
FROM docomexam
WHERE sysuserid = 4))
WHERE ROWNUM <= 10);你那里面ORDER BY根本没意义
你把
select id from docomexam where sysuserid = 4 order by id desc
中的order by id desc去掉后,在执行一下该语句试试。
SQL> select name
2 from a
3 where id in (select id from b order by id);
where id in (select id from b order by id)
*
第 3 行出现错误:
ORA-00907: 缺失右括号
SQL> select name
2 from a
3 where id in (select id from b);NAME
----------------------------------------
wp
wh
表是这样的:
文件表: 文件与审核人对应表: 审核人表:
文件id 用户id 文件id 审核人id 审核人id 审核人名
id sysuserid id docomexam_id sysuser_id id uname
1 2 1 2 123 123 晨晨
2 3 2 3 123 124 兰兰
3 4 3 2 124 125 咪咪
4 3 4 5 123 126 嘟嘟
5 3 5 3 124
6 4 6 2 125
7 2 7 2 126
8 3 8 3 125
9 4 9 1 124
10 4 10 3 126要求就是通过用户id查询出最近用过的10为审核人的id和姓名,因为每份文件可能用到很多个审核人,每两份文件用到是审核人可能有相同的,我想就是先把用户id对应的所有文件排序列出来,再查对应的前10为审核人。
select c.id,c.uname,
row_number()over(order by a.id desc)rn
from docomexam a,docomman b,sysusers c
where a.id=b.docomexam_id
and b.sysuser_id=c.id
and a.sysuserid=4)
where rn<=10
select c.id,c.uname,
count(distinct b.sysuser_id)over(order by a.id desc)c
from docomexam a,docomman b,sysusers c
where a.id=b.docomexam_id
and b.sysuser_id=c.id
and a.sysuserid=4
)
where c<=10这样试试
http://topic.csdn.net/u/20091119/20/a577f762-70ec-41dc-811c-44fca06f7971.html?59912