Find the writing of a SQL statement.

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

The main idea is: According to ModelInfoNO and ShopWarehouseNO, group statistics on the InStock and OutStock tables, (ModelInfoNO and ShopWarehouseNO are passed into the subquery from the outside, they have nothing to do with each other).

Don't know why, if I put A_x in the main query FROM ",
(
SELECT DISTINCT InShopWarehouseNO AS ShopWarehouseNO FROM [InStockOrders]
union
SELECT DISTINCT OutShopWarehouseNO FROM [OutStockOrders]
) A_y
"This is removed. The A_y.ShopWarehouseNO in it is replaced by a constant (such as 2 or 3), and it will work well. However, the result of this query is ShopWarehouseNO for the specified data.

If I don't remove A_y, the query analyzer reports the following error.
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.

why?

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

There is one more from FROM,
There should be nothing wrong

westevil

There is one more from FROM,
There should be nothing wrong

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

Sorry, some of the display fields were removed for simplicity when I asked questions, and the comma was forgotten if I accidentally.

westevil

Let me make a statement: the comma in front of FROM is a clerical error, not the cause of this error.

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 is written like this:

...

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

Perhaps behind can only be the field between the associated two tables (B, C) as a condition.
You have also pulled in A now, I wonder if there is a problem.

westevil

why?

waxiaoning

The system is not as smart as you

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
=========
No, still report an error?

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

Now it reports:
Server: Msg 156, Level 15, State 1, Line 39
Incorrect syntax near the keyword'ORDER'.

I removed the following ORDER BY words, and then reported:
Server: Msg 170, Level 15, State 1, Line 38
Line 38: Incorrect syntax near'NO'.
(Refers to the line ModelsInfo z ON A_x.ModelInfoNO=z.[NO])

Please help, how to do it!