执行下面SQL 会收到如题的错误.这是为什么呢? 注意红色部分,它属于子查询,再关注出错信息.问题出在order by......
select location from (Select * From LOCATION Where WAREHOUSE = N'W3_SHANGHAI' And PUT_ZONE = N'AA' And LOCATION_TYPE = N'STO' Order By LOCATION Asc) as t1 where not exists(select 1 from inventory where(qty>0 or qty_expected>0) and inventory.location = t1.location and inventory.warehouse = t1.warehouse) order by t1.location
OK,就是这样>>>>>因为作为子查询,如果有order ...,规定必须有TOP,所以红色部分改成 Select top 100 percent * From LOCATION Where WAREHOUSE = N'W3_SHANGHAI' And PUT_ZONE = N'AA' And LOCATION_TYPE = N'STO' Order By LOCATION Asc 就漂亮地运行啦!
我们可以举一反三如:
select top 10 id from table ...order by...
select top 100 percent id from table ... order by ...
也许有问.SELECT TOP 100 PERCENT这是虾米东西啊. 继续说明:
--返回符合条件的100%的记录,即所有符合条件的记录
SELECT TOP 100 PERCENT *
--返回符合条件的100条记录,即只返回符合条件的100条记录
SELECT TOP 100 *
甚至我们可以这样写 SELECT TOP 30 PERCENT from table ... order by...desc/asc
本文来自博客园,作者:del88,转载请注明原文链接:https://www.cnblogs.com/del88/archive/2012/08/17/2644645.html