求一SQL语句的写法.

westevil

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.

为什么呀?

wgfxman

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

FROM 前面多了个，
其他应该没什么错

westevil

FROM 前面多了个，
其他应该没什么错

==============

不好意思，是我提问的时候为了简单起见就去掉了一些显示字段，那个逗号不小心就忘了。

westevil

声明一下哈：FROM前面那个逗号，是笔误，而不是这个错误的原因。

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.

hiqwolfzzh

lz的写法是这样：

……

from A,B left join C on C.###=A.### ……

也许on后边只能是关联的两表(B,C)之间的字段作为条件。
你现在把A也拉了进来，不知是否有问题。

westevil

为什么呀？

waxiaoning

系统没有你聪明

mydriver

...
        (SELECT DISTINCT ModelInfoNO  FROM [InStockOrder Details]
        union  
        SELECT DISTINCT ModelInfoNO FROM [OutStockOrder Details]) A_x
INNER JOIN
        (SELECT DISTINCT InShopWarehouseNO AS ShopWarehouseNO FROM [InStockOrders]
        union
        SELECT DISTINCT OutShopWarehouseNO FROM [OutStockOrders]) A_y
...

westevil

mydriver
=========
不行呀，还是报错呢?

westevil

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
        INNER JOIN
        (
                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

现在却报:
Server: Msg 156, Level 15, State 1, Line 39
Incorrect syntax near the keyword 'ORDER'.

我把后面的ORDER BY字句去掉，则又报：
Server: Msg 170, Level 15, State 1, Line 38
Line 38: Incorrect syntax near 'NO'.
(指ModelsInfo z  ON A_x.ModelInfoNO=z.[NO]这一行)

各位帮帮忙呀，怎么办呀！