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

[Juchiyufei]

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.

为什么呀?

[Juchiyufei]

声明一下哈：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.

[Juchiyufei]

为什么呀？

[zheninchangjiang]

系统没有你聪明

[bugchen888]

...
(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
...

[rookie_one]

lz的写法是这样：

……

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

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

[zheninchangjiang]

from a join c on a.column=c.column
join b on b.coumn=c.column

[Juchiyufei]

如有其它的方法，当然可以不用这种方法，诚然这种方法效率是有点低。

问题是我要根据ModelsInfoNO和ShopWarehouseNO去对InStockOrders和OutStockOrders分组统计Quantity.

[zheninchangjiang]

指定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
有关系描述吗？？

[Juchiyufei]

bugchen888(臭虫)
=========
不行呀，还是报错呢?

[Juchiyufei]

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]这一行)

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

[skeeterLa]

不好意思，我想请问一下，这样的查询能用到数量据很大的数据库上吗？ 比如是在5百万条记录的数据表中做查询，是不是速度会很慢呢？

[Juchiyufei]

我就是不知道怎样在FROM后面连接两个集，并且FROM后的两个集之间是没有任何关系的，我只是要根据这两个集去做后面的查询。


有其它的方法来实现我这个想要的功能吗？

[Juchiyufei]

不行呀，

我的from后面有两个呀

如
select ... from a,b
left join c on a.col = c.col and b.col = c.col2
left join d on a.col = d.col and b.col = d.col2

left join e on e.col = b.col
left join f on f.col = a.col

order by a.col

[Juchiyufei]

from后面的两个根本就没有任何关系，是相对独立的，只是后面的left join on要用到它们做查询的条件，你可以看一下上面的全部代码。

[zheninchangjiang]

from a join c on a.column=c.column
join b on b.coumn=c.column

[Juchiyufei]

不行呀，楼上的大哥，我按照你的那样改，查出来的数据不对。

再不你帮我完全的写出来一下。

[zheninchangjiang]

两个分别是什么关系，联合，还是分别跟后面的有关系？
你的意图是什么？
打架2V1还是1V1V1

[Juchiyufei]

楼上强人呀。

[jsyxo]

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]

[Juchiyufei]

^_^