日期:2014-05-18  浏览次数:20509 次

求一SQL语句的写法,.....................................


SELECT   A_x.ModelInfoNO,   z.[ModelNO],   z.[TradeName],   z.[EnscapMode],   z.[Quality],   z.[BatchNO],
  A_y.ShopWarehouseNO,
ISNULL(InStockInfo.InStock_Count,   0)   AS   InStock_Count,
ISNULL(OutStockInfo.OutStock_Count,   0)   AS   OutStock_Count,
FROM
(
SELECT   DISTINCT   ModelInfoNO     FROM   [InStockOrder   Details]
union    
SELECT   DISTINCT   ModelInfoNO   FROM   [OutStockOrder   Details]
)
A_x,
(
SELECT   DISTINCT   InShopWarehouseNO   AS   ShopWarehouseNO   FROM   [InStockOrders]
union
SELECT   DISTINCT   OutShopWarehouseNO   FROM   [OutStockOrders]
)A_y
LEFT   JOIN
(
SELECT   A.ModelInfoNO,B.InShopWarehouseNO,SUM(A.Quantity)   as   InStock_Count
FROM   [InStockOrder   Details]   A,   [InStockOrders]   B
WHERE   A.[InStockOrderNO]=B.[NO]
GROUP   BY   A.ModelInfoNO,   B.InShopWarehouseNO
)   InStockInfo
ON   InStockInfo.ModelInfoNO=A_x.ModelInfoNO     AND   InStockInfo.InShopWarehouseNO   =   A_y.ShopWarehouseNO
LEFT   JOIN
(
SELECT   A.ModelInfoNO,B.OutShopWarehouseNO,SUM(A.Quantity)   as   OutStock_Count
FROM   [OutStockOrder   Details]   A,   [OutStockOrders]   B
WHERE   A.[OutStockOrderNO]=B.[NO]
GROUP   BY   A.ModelInfoNO,   B.OutShopWarehouseNO
)   OutStockInfo
ON   OutStockInfo.ModelInfoNO   =   A_x.ModelInfoNO   AND   OutStockInfo.OutShopWarehouseNO   =   A_y.ShopWarehouseNO
LEFT   JOIN  
ShopWarehouseInfo   y1   ON   y1.[ID]   =   A_y.ShopWarehouseNO
LEFT   JOIN
ModelsInfo   z     ON A_x.ModelInfoNO=z.[NO]

ORDER   BY   InStock_Count   DESC,
OutStock_Count   DESC

大意为:根据ModelInfoNO与ShopWarehouseNO去分组统计InStock,OutStock表中的Quantity,(ModelInfoNO和ShopWarehouseNO由外面传入到里面的子查询中去,它们俩没有任何关系).

不知道为什么,如果我把主查询FROM中A_x后面 ",
(
SELECT   DISTINCT   InShopWarehouseNO   AS   ShopWarehouseNO   FROM   [InStockOrders]
union
SELECT   DISTINCT   OutShopWarehouseNO   FROM   [OutStockOrders]
)A_y
"这个去掉,里面的A_y.ShopWarehouseNO用一个常数(如2或3什么的)去代替,则能很好的运行,然而这样查询的结果就是ShopWarehouseNO为那个指定的数据了.

如果我不去掉A_y,查询分析器则报出下面的错误.
Server:   Msg   107,   Level   16,   State   2,   Line   3
The   column   prefix   'A_x '   does   not   match   with   a   table   name   or   alias   name   used   in   the   query.

为什么呀?



------解决方案--------------------
SELECT A_x.ModelInfoNO, z.[ModelNO], z.[TradeName], z.[EnscapMode], z.[Quality], z.[BatchNO],
A_y.ShopWarehouseNO,
ISNULL(InStockInfo.InStock_Count, 0) AS InStock_Count,
ISNULL(OutStockInfo.OutStock_Count, 0) AS OutStock_Count
FROM
(
SELECT DISTINCT ModelInfoNO FROM [InStockOrder Details]
union
SELECT DISTINCT ModelInfoNO FROM [OutStockOrder Details]
)A_x,
(
SELECT DISTINCT InShopWarehouseNO AS ShopWarehouseNO FROM [InStockOrders]
union
SELECT DISTINCT OutShopWarehouseNO FROM [OutStockO