select * from (Select A.*,B.OverCredit,ROW_NUMBER() OVER(Order By empno) as row FROM (select convert(bit,0) as Checked,EnEmpName,HrEmployee.EmpID,HrEmployee.EmpNO, HrEmployee.EmpName, HrEmployee.DeptID,Balance/100 as Balance, HrEmployee.PkMemType,HrEmployee.EmpType, HrEmployee.UserID,(CASE HrEmployee.EmpType when 0 then '外来人员'when 1 then '干部' when 2 then '管理' when 3 then '一线' END) AS EmpTypeName, (CASE HrEmployee.Sex WHEN 1 THEN '男' WHEN 2 THEN '女' END) AS Sex, HrEmployee.CertifNo, HrEmployee.MobilePhone,(CASE HrEmployee.Leave WHEN 1 THEN '是' WHEN 0 THEN '否' END) AS Leave, HrEmployee.CardNo, HrEmployee.Birthday,EmpSNO, HrEmployee.Duty, HrEmployee.LeaveDate,HrDept.DeptNo, HrDept.DeptName, HrEmployee.Reason, HrEmployee.GuideID, HrEmployee.IsPatrol, HrEmployee.RegTime, HrEmployee.RegReason, HrEmployee.GuideName,(CASE HrEmployee.CmYcState WHEN 1 THEN '是' ELSE '' END) AS CmYcState FROM HrDept,HrEmployee where HrEmployee.DeptID = HrDept.DeptID and HrEmployee.EmpType>=1 and HrEmployee.EmpType<=99 and HrEmployee.DeptID in (1)) AS A left join (SELECT CmOrder.EmpID,SUM(OrderMoney)as OverCredit FROM CmOrder where OrdeDateTime >= '2014-5-1 00:00:00' and OrdeDateTime <= '2014-5-31 23:59:59' group by CmOrder.EmpID) AS B ON A.EmpID = B.EmpID ) a where row
between 1 and 30
这个sql语句在sql2005上执行没问题，但是在sql2000上执行会报错，提示EmpID不明确 。 为什么？ 求指点！！！！
------解决方案--------------------
帮你格式化了一下，没有环境不好跟踪，你逐层抽出来单独执行看看哪个地方出问题
SELECT *
FROM ( SELECT A.* ,
B.OverCredit ,
ROW_NUMBER() OVER ( ORDER BY empno ) AS row
FROM ( SELECT CONVERT(BIT, 0) AS Checked ,
EnEmpName ,
HrEmployee.EmpID ,
HrEmployee.EmpNO ,
HrEmployee.EmpName ,
HrEmployee.DeptID ,
Balance / 100 AS Balance ,
HrEmployee.PkMemType ,
HrEmployee.EmpType ,
HrEmployee.UserID ,
( CASE HrEmployee.EmpType
WHEN 0 THEN '外来人员'
WHEN 1 THEN '干部'
WHEN 2 THEN '管理'
WHEN 3 THEN '一线'