SQL语句如下:
select distinct *
from (select row_number() over(order by currentSite desc) as rownumber,
v.*
from (select emp.emp_id empId,
pcb.proj_desc currentSite,
pcd.proj_desc headerCountSite
from dsasdba.ssls_security_master sm,
dsasdba.ssls_security_user_role ur,
dsasdba.ssls_security_role_team_edit rte,
dsasdba.ssls_security_team_table team,
employee_all_details emp
left join proj_code pcb on emp.emp_branch = pcb.proj_code
left join proj_code pcd on emp.emp_dept = pcd.proj_code
where 1 = 1
and upper(sm.username) = upper('ADMINISTRATOR')
and sm.pkey = ur.user_id
and ur.role_id = rte.role_id
and team.team_id = rte.team_id
and team.team_code = emp.emp_team) v) t
where rownumber between 1 and 10
這條語句在ORACLE數據褲中執行沒有問題,修改order by 後面的排序也同樣可以正確排序
但我的語句是用PrepareStatement的參數注入的,一共注入了4個參數: orderByClause、username、pageBegin、pageEnd
oderByClause對應的就是 這個拼接的字符串,
經過這樣注入查詢的結果就是,這個currentSite desc無法作為SQL的一部分,被當做字符串變量插入到SQL中,
查詢結果一直按默認排序來顯示
但幾次都沒發現問題所在,直到我乾脆把這個字符串連同SQL一塊拼接,搜索結果才正確排序
2018.11.6 11:15