SQL Server :选择联接
SQL Server :选择联接
提问于 2019-03-15 17:21:20
表结构：
#IN
(
REFNUM NVARCHAR(5),
QUANTITY INT,
BIRTH DATE,
WH NVARCHAR(10),
BIN NVARCHAR(10),
WEEK INT,
DAY INT
)
#OUT
(
REFNUM NVARCHAR(5),
QUANTITY INT,
BIRTH DATE,
WH NVARCHAR(10),
BIN NVARCHAR(10),
WEEK INT,
DAY INT
)
#TRANSFER
(
REFNUM NVARCHAR(5),
QUANTITY INT,
BIRTH DATE,
WH NVARCHAR(10),
BIN NVARCHAR(10),
OUTWH NVARCHAR(10),
INBIN NVARCHAR(10),
WEEK INT,
DAY INT
)
(Insert test data)
Insert into #IN (REFNUM, QUANTITY, BIRTH, WH, BIN) VALUES ('0001', 100, '2019.01.29', 'A01', 'A01')
Insert into #IN (REFNUM, QUANTITY, BIRTH, WH, BIN) VALUES ('0002', 100, '2019.01.29', 'A01', 'A02')
Insert into #IN (REFNUM, QUANTITY, BIRTH, WH, BIN) VALUES ('0003', 100, '2019.01.29', 'A01', 'A03')
Insert into #IN (REFNUM, QUANTITY, BIRTH, WH, BIN) VALUES ('0004', 100, '2019.01.29', 'A01', 'A04')
Insert into #OUT (REFNUM, QUANTITY, BIRTH, WH, BIN) VALUES ('0001', 10, '2019.01.29', 'A01', 'A01')
Insert into #OUT (REFNUM, QUANTITY, BIRTH, WH, BIN) VALUES ('0001', 10, '2019.01.29', 'A01', 'A01')
Insert into #OUT (REFNUM, QUANTITY, BIRTH, WH, BIN) VALUES ('0002', 10, '2019.01.29', 'A01', 'A02')
Insert into #OUT (REFNUM, QUANTITY, BIRTH, WH, BIN) VALUES ('0002', 10, '2019.01.29', 'A01', 'A02')
Insert into #TRANSFER (REFNUM, QUANTITY, BIRTH, WH, BIN, INWH, INBIN) VALUES ('0001', 10, '2019.01.29', 'A01', 'A01', 'A01', 'A03')
Insert into #TRANSFER (REFNUM, QUANTITY, BIRTH, WH, BIN, INWH, INBIN) VALUES ('0001', 10, '2019.01.29', 'A01', 'A01', 'A01', 'A03')
Insert into #TRANSFER (REFNUM, QUANTITY, BIRTH, WH, BIN, INWH, INBIN) VALUES ('0001', 10, '2019.01.29', 'A01', 'A02', 'A01', 'A04')
Insert into #TRANSFER (REFNUM, QUANTITY, BIRTH, WH, BIN, INWH, INBIN) VALUES ('0001', 10, '2019.01.29', 'A01', 'A02', 'A01', 'A04')我想得到的结果是：
REFNUM QUANTITY WH BIN
0001 80 A01 A01
0001 120 A01 A03
0002 80 A01 A02
0002 120 A01 A04 到目前为止，我得到的查询如下：
SELECT
I.REFNUM,
I.WH,
I.BIN,
O.WH,
O.BIN,
T.WH,
T.BIN,
T.REFNUM,
(SELECT QUANTITY FROM #IN I) - ISNULL((SELECT SUM(QUANTITY) FROM #OUT O),0) - ISNULL((SELECT SUM(QUANTITY) FROM #TRANSFER T), 0) AS RESULT
FROM #IN I
RIGHT OUTER JOIN #OUT O
ON I.REFNUM = O.REFNUM
RIGHT OUTER JOIN #TRANSFER T
ON O.REFNUM = T.REFNUM
GROUP BY I.REFNUM, O.REFNUM, T.REFNUM, I.WH, I.BIN, O.WH, O.BIN, T.WH,
T.BIN
ORDER BY I.REFNUM我得到了一个错误：“子查询返回了一个以上的值。当子查询跟在=，!=，<，<=，>，>=之后或者当子查询被用作表达式时，这是不允许的。”
我的查询应该如何通过连接这3个表来获得我想要的结果？(我正在尝试创建一个过程来获得结果。因此，如果创建临时表更容易，我也可以这样做)
回答 1
Stack Overflow用户
发布于 2019-03-15 20:51:16
由于您的#out &#转移表具有重复值...你可以试试这样的东西
WITH CTE_OUT
AS (
SELECT REFNUM
,SUM(QUANTITY) AS QUANTITY --.... OTHERCOLUMNS
FROM #OUT
GROUP BY REFNUM
)
,CTE_TRANSFER
AS (
SELECT REFNUM
,SUM(QUANTITY) AS QUANTITY --.... OTHERCOLUMNS
FROM #TRANSFER
GROUP BY REFNUM
)
SELECT O.REFNUM
,ISNULL(I.QUANTITY, 0) - ISNULL(O.QUANTITY, 0) - ISNULL(T.QUANTITY, 0) AS QUANTITY -- WHAT EVER IS YOUR EXPRESSION
FROM #IN I
LEFT JOIN CTE_OUT O ON I.REFNUM = O.REFNUM
LEFT JOIN CTE_TRANSFER T ON O.REFNUM = T.REFNUM
GROUP BY O.REFNUM -- OTHER COLUMNS 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/55179241
复制相关文章
相似问题