求一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 ONA_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 [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 ONA_x.ModelInfoNO=z.[NO]
ORDER BY InStock_Count DESC,
OutStock_Count DESC
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
...
[解决办法]
指定join 一定要有关系存在并描述
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
有关系描述吗？？
[解决办法]
from a join c on a.column=c.column
join b on b.coumn=c.column
[解决办法]
两个分别是什么关系，联合，还是分别跟后面的有关系？
你的意图是什么？
打架2V1还是1V1V1
[解决办法]
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,
(
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,
(
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,
ShopWarehouseInfo y1,
ModelsInfo z,
WHERE InStockInfo.ModelInfoNO=A_x.ModelInfoNO AND InStockInfo.InShopWarehouseNO = A_y.ShopWarehouseNO AND
OutStockInfo.ModelInfoNO = A_x.ModelInfoNO AND OutStockInfo.OutShopWarehouseNO = A_y.ShopWarehouseNO AND
y1.[ID] = A_y.ShopWarehouseNO AND A_x.ModelInfoNO=z.[NO]
