SELECT DISTINCT p.ContractID,ch.CompanyID,e.EmployeeID,e.FirstName,e.MiddleInitial,e.LastName,e.SSN,c.FederalTaxID, CONVERT(VARCHAR(10),BeginLastDate.BeginDate,101) AS BeginDate,CONVERT(VARCHAR(10),BeginLastDate.LastDate,101) AS LastDate,CONVERT(VARCHAR(10),ch.EndDate,101) as EndDate FROM CprWeekly cw INNER JOIN CprHeader ch ON cw.CprHeaderID = ch.CprHeaderID ...ORDER BY e.FirstName,e.LastName,ch.EndDate这个地方ch.如果不去掉,在我机器上是对的,在server是出错。错误消息: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
CONVERT(VARCHAR(10),BeginLastDate.BeginDate,101) AS BeginDate,CONVERT(VARCHAR(10),BeginLastDate.LastDate,101) AS LastDate,CONVERT(VARCHAR(10),ch.EndDate,101) as EndDate
FROM CprWeekly cw
INNER JOIN CprHeader ch ON cw.CprHeaderID = ch.CprHeaderID
...ORDER BY e.FirstName,e.LastName,ch.EndDate这个地方ch.如果不去掉,在我机器上是对的,在server是出错。错误消息:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
服务器是windows 2003,我是windows xp.
首先同时select distinct语句和order by语句的时候要求:order by子句出现的字段必须在select子句中出现
而在你的sql语句中,你的sql语句并没有ch.EndDate字段,而是将它重命名为:EndDate
如果你将ch.去除就不会出现这个问题。
个人建议对于这种情况order by子句不要使用『表名.字段名』的格式,直接用字段名称就可以
(如果重命名了就用已重命名的名称)