String sql = "select top(2) * from flobject where dirid in " + "(select dirid from dir where dirpath like '" + dirPath + "%' and dirid "
+ "not in (select dirid from dir where dirpath like (select top(1) dirpath from"
+ "( select top(2)* from dir where dirid in (select dirid from user_dir_role " + "where dirid in (select dirid from dir where dirpath like '"
+ dirPath + "%'))" + "order by dirpath desc)as a) + '%' ))or dirid in (select dirid from dir " + "where dirpath like '" + dirPath
+ "%' and dirid not in (select dirid from dir " + "where dirpath like (select top(1) dirpath from(select top(2) * from dir where dirid in "
+ "(select dirid from group_dir_role where dirid in (select dirid from dir " + "where dirpath like '" + dirPath
+ "%'))order by dirpath desc)as a) + '%' )) and (phase = 1 or phase = 3)"
太长了
分开还看的明白
搅在一起就晕了
+ "not in (select dirid from dir where dirpath like (select top(1) dirpath from"
+ "( select top(2)* from dir where dirid in (select dirid from user_dir_role " + "where dirid in (select dirid from dir where dirpath like '"
+ dirPath + "%'))" + "order by dirpath desc)as a) + '%' ))or dirid in (select dirid from dir " + "where dirpath like '" + dirPath
+ "%' and dirid not in (select dirid from dir " + "where dirpath like (select top(1) dirpath from(select top(2) * from dir where dirid in "
+ "(select dirid from group_dir_role where dirid in (select dirid from dir " + "where dirpath like '" + dirPath
+ "%'))order by dirpath desc)as a) + '%' )) and (phase = 1 or phase = 3)"
太长了
分开还看的明白
搅在一起就晕了
很多方法可以实现 row_number()
小弟万分感激
以前只用SQL server
突然用这个很多细节就不明白了
from dir d
where d.dirid in
(select dirid
from user_dir_role
where dirid in
(select dirid from dir where dirpath like 'dirPath%'))
and rownum<=2
order by d.dirpath desc
from (select * from t1 order by name desc)
where rownum<=10要先order by 后 where rownum<=10这样写
select *
from flobject f
where dirid in
(select dirid
from dir
where dirpath like 'dirPath%'
and dirid not in
(select dirid
from dir
where dirpath like
(select max(dirpath) /*如果dirpath不为NUMBER型的话,要用to_number转换*/
from (select *
from dir
where dirid in
(select dirid
from user_dir_role
where dirid in
(select dirid
from dir
where dirpath like 'dirPath%'))
and rownum<=2
order by dirpath desc) as a) '%'))
or dirid in
(select dirid
from dir
where dirpath like 'dirPath%'
and dirid not in
(select dirid
from dir
where dirpath like
(select max(dirpath)
from (select *
from dir
where dirid in
(select dirid
from group_dir_role
where dirid in (select dirid
from dir
where dirpath like
'dirPath%'))
and rownum<=2
order by dirpath desc) as a) '%'))
and (phase = 1 or phase = 3)
and rownum<=2
order by f.[排序列]
/code