问题截图如下:
咨询智友人员,称在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宏以后运行正常