Question

sql server2005语法错误 ，下面的查询语句报错消息 102，级别 15，状态 1，第 8 行')' 附近有语法错误。

select userName,realName,gender,deptName,roleName,phone,userNumber from(
select ROW_NUMBER() OVER (ORDER BY userName asc) AS rn, userName,realName,gender,deptName,roleName,phone,userNumber
from (
select oa_user.user_name userName,oa_user.user_realname realName,oa_user.user_gender gender,
oa_dept.dept_name deptName,oa_role.role_name roleName,oa_user.user_phone phone,oa_user.user_number userNumber
from oa_user,oa_dept,oa_role
where oa_user.dept_number=oa_dept.dept_number and oa_user.role_number=oa_role.role_number and oa_user.dept_number='00000'
))where rn>0 and rn<=2

Answer 1

子表应该用别名吧

select userName,realName,gender,deptName,roleName,phone,userNumber from(select ROW_NUMBER() OVER (ORDER BY userName asc) AS rn, userName,realName,gender,deptName,roleName,phone,userNumber from (select oa_user.user_name userName,oa_user.user_realname realName,oa_user.user_gender gender,oa_dept.dept_name deptName,oa_role.role_name roleName,oa_user.user_phone phone,oa_user.user_number userNumber from oa_user,oa_dept,oa_rolewhere oa_user.dept_number=oa_dept.dept_number and oa_user.role_number=oa_role.role_number and oa_user.dept_number='00000') bbb)  aaa
where rn>0 and rn<=2