技巧集锦020：自定义查询表单，带ORDER BY 时报错

问题截图如下：

咨询智友人员，称在SQL中，SELECT 中的字段不能重名，但经检查SELEC 中的字段没有重复的。

后台观察SQL语句，发现有后台分页语句：

 select identity(int,1,1) as r___n,* INTO  #__temptable 
 FROM (
		SELECT a.Dates,a.OnTime,a.BillNo,a.BillCode,c.ORGID,c.ORGNAME AS businessname,d.DEPARTMENT,e.STAFFNAME AS SaleManName
			  ,g.BusinessCode
			  --,g.BusinessName AS BusinessName1
			  ,f.STAFFNAME AS CaozyName
			  ,CASE WHEN a.kk_kplx ='1' THEN '器械' ELSE '非器械' END kk_kplx
			  ,CASE WHEN a.kk_ddlx ='3' THEN '领用' WHEN a.kk_ddlx ='4' THEN '归还' ELSE '领用' END AS kk_ddlx
			  ,a.kk_islc
			  ,b.billSn,b.BillSort,h.GoodsId,h.GoodsCode,h.GoodsName,h.GoodsSpec,i.kk_ylqxbah,h.Manufacturer,i.kk_Meas,b.unit
			  ,j.WHName,k.BatchCode,k.SterilCode,k.ProduceDate,k.ValDate
			  ,b.Num,b.Price,b.Taxprice,b.Amount,b.Rate,b.Tax,b.TaxAmount,b.Cost,b.CostAmt,b.Profit
			  ,i.kk_ylqxsch,i.InstRegNo,i.kk_qxzcrq,i.InstRegValDate,i.kk_qxfzjg
			  ,a.Remark
		FROM SALEOUTMT AS a
			 INNER JOIN SALEOUTDT AS b ON a.EntId = b.EntId AND a.BillNo = b.BillNo
			 INNER JOIN ORGDOC AS c ON a.EntId = c.ENTID AND a.ClientId = c.ORGID
			 INNER JOIN DEPTDOC AS d ON a.EntId = d.ENTID AND a.DeptId = d.DEPTID and  d.entid= 'E2SVHJM7RVQ'  
			 INNER JOIN STAFFDOC AS e ON a.EntId = e.ENTID AND a.SaleManId = e.STAFFID  and  e.entid= 'E2SVHJM7RVQ'   
			 INNER JOIN STAFFDOC AS f ON a.EntId = f.ENTID AND a.caozy = f.STAFFID  and  f.entid= 'E2SVHJM7RVQ'   
			 LEFT  JOIN BUSINESSDOC AS g ON a.EntId =g.EntId AND a.KK_BusinessId = g.BusinessId
			 INNER JOIN GOODSDOC AS h ON b.EntId = h.EntId AND b.GoodsId = h.GoodsId and  h.entid= 'E2SVHJM7RVQ'  
			 INNER JOIN GOODSATTR AS i ON h.EntId = i.EntId AND h.GoodsId = i.GoodsId
			 INNER JOIN STOREHOUSE AS j ON b.EntId = j.EntId AND b.WHId = j.WHId
			 INNER JOIN BATCHCODE AS k ON b.EntId = k.EntId AND b.GoodsId =k.GoodsId AND b.AngleId = k.AngleId    
		WHERE a.RuleId ='0pa1ysk18roo3ay8' and a.entid =  'E2SVHJM7RVQ' 
			and a.Dates >=  '2019-11-01'  and a.Dates <=  '2019-11-08' 
			and a.orgid='O2SVHJM7TKU'  and a.billcode like '%'
	   ORDER by a.BillCode
) a 


SELECT * from #__temptable where r___n <= 500 and r___n > 0 


DROP table #__temptable

执行上面语句报错

原因找到了

解决办法：

1.删除ORDER by a.BillCode 排序，就正常，但没有排序又不行；

2.在SELECT 增加TOP 100 PERCENT，再执行没报错 

top 100 percent是指返回符合条件的100%的记录，即所有符合条件的记录

更换SQL宏以后运行正常