MSSQL INNER JOIN WHERE子句
以下SQL查询查找与某些需求匹配的订单，然后使用发票行(_btblInvoiceLines)创建一个新订单(InvNum)。在此阶段，它只创建了基本信息。
DECLARE @gid varchar(50) SELECT @gid = newid()
INSERT INTO InvNum (DocType,DocState,AccountID,ucIDSOrdTempInvID)
SELECT 4,1,AccountID,@gid
FROM (SELECT DISTINCT AccountID
FROM InvNum
WHERE ubIDSOrdConsolOrder = '1'
AND DocState = '1'
AND DocType <> '5') A
INSERT INTO _btblInvoiceLines (iInvoiceID, iStockCodeID, fQuantity)
SELECT AutoIndex, iStockCodeID, qty
FROM (SELECT AutoIndex, iStockCodeID, qty
FROM (SELECT AutoIndex,AccountId,ucIDSOrdTempInvID
FROM InvNum
WHERE ucIDSOrdTempInvID = @gid) T1
INNER JOIN (SELECT B.iStockCodeID,A.AccountID,sum(B.fQuantity) AS qty
FROM (SELECT *
FROM InvNum
WHERE ubIDSOrdConsolOrder = '1'
AND DocState = '1'
AND DocType <> '5') A
INNER JOIN (SELECT *
FROM _btblInvoiceLines) B ON A.AutoIndex = B.iInvoiceID
GROUP BY iStockCodeID, AccountID) T2 ON T1.AccountId = T2.AccountID) T3在此之后，所有的InvNum列都被更新，这样就可以很好地工作。我的问题是以下内容，它使用匹配的旧订单的_btblInvoiceLines更新新订单和新行的WHERE ubIDSOrdConsolOrder = '1' AND DocState = '1' AND DocType <> '5'：
这个FROM子句是不正确的：
SELECT D1.*
FROM (SELECT A.*
FROM _btblInvoiceLines A
INNER JOIN (SELECT max(idInvoiceLines) as idInvoiceLines, iStockCodeID
FROM _btblInvoiceLines
GROUP BY iStockCodeID) B ON A.idInvoiceLines = B.idInvoiceLines) D1
INNER JOIN (SELECT *
FROM _btblInvoiceLines I1
INNER JOIN (SELECT accountid,autoindex,ucIDSOrdTempInvID
FROM InvNum
WHERE ucIDSOrdTempInvID = @gid) I2 ON I1.iInvoiceID = I2.AutoIndex) D2 ON D1.iStockCodeID = D2.iStockCodeID
WHERE _btblInvoiceLines.idInvoiceLines = D2.idInvoiceLines我不知道如何制作D1。*从添加到新订单D2中的最后一个原始_btblInvoiceLines中提取信息。我认为我能做到这一点的唯一方法是使用下面的SELECT DISTINCT AccountID,ubIDSOrdConsolOrder FROM InvNum WHERE ubIDSOrdConsolOrder = '1' AND DocState = '1' AND DocType <> '5'，但我不知道如何正确地放入它。抱歉，如果这是一个简单的问题，但我已经寻找并试图解决这个问题2天了，现在没有任何运气。
谢谢。
回答 1
Stack Overflow用户
发布于 2014-09-30 09:58:12
我把INNER JOIN的顺序弄错了。我执行了以下操作，现在可以正常工作了：
SELECT D1.*
FROM (SELECT DISTINCT AccountID, AutoIndex FROM InvNum WHERE ubIDSOrdConsolOrder = '1' AND DocState = '1' AND DocType <> '5') A1
INNER JOIN (SELECT * FROM _btblInvoiceLines) D1 ON A1.AutoIndex = D1.iInvoiceID----
INNER JOIN (SELECT max(idInvoiceLines) as idInvoiceLines, iStockCodeID FROM _btblInvoiceLines GROUP BY iStockCodeID) I1 ON D1.iStockCodeID = I1.iStockCodeID
INNER JOIN (SELECT * FROM _btblInvoiceLines I1 INNER JOIN (select accountid,autoindex,ucIDSOrdTempInvID FROM InvNum where ucIDSOrdTempInvID = @gid) I2 ON I1.iInvoiceID=I2.AutoIndex) D2 ---- @gid
ON D1.iStockCodeID=D2.iStockCodeID
WHERE _btblInvoiceLines.idInvoiceLines = D2.idInvoiceLineshttps://stackoverflow.com/questions/26028226
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号