表结构:
#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个表来获得我想要的结果?(我正在尝试创建一个过程来获得结果。因此,如果创建临时表更容易,我也可以这样做)
发布于 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
https://stackoverflow.com/questions/55179241
复制相似问题