求个SQL语句,我认为特复杂,逻辑性很强,不过高手们肯定会写!_百度知 ...
发布网友
发布时间：2022-04-30 10:30
共5个回答
热心网友
时间：2022-04-08 22:22
ID INT IDENTITY(1, 1) PRIMARY KEY,
Name VARCHAR(10)
);
go
INSERT INTO Depot
SELECT '商品仓' UNION ALL
SELECT '成品一仓' UNION ALL
SELECT '成品二仓';
go
-- 为了造测试数据，这里不自增了
CREATE TABLE Container(
ID INT PRIMARY KEY,
Name VARCHAR(20),
DepotID INT
);
INSERT INTO Container
SELECT 21, '21号仓位', 3 UNION ALL
SELECT 34, '34号仓位', 1 UNION ALL
SELECT 7, '7号仓位', 1;
go
CREATE TABLE Stock(
ID INT IDENTITY(1,1) PRIMARY KEY,
ConID INT,
GoodsID INT,
InCount INT,
OutCount INT,
LastCount INT
);
go
INSERT INTO Stock
SELECT 34, 1, 40, 0 , 40 UNION ALL
SELECT 34, 1, 0 , 26, 14 UNION ALL
SELECT 34, 1, 5 , 0 , 19 UNION ALL
SELECT 34, 1, 0 , 17, 2 UNION ALL
SELECT 21, 1, 67, 0 , 67 UNION ALL
SELECT 21, 1, 0 , 10, 57 UNION ALL
SELECT 21, 1, 3 , 0 , 60 UNION ALL
SELECT 7 , 1, 5 , 0 , 5 UNION ALL
SELECT 7 , 1, 0 , 5 , 0
go
-- 上面是测试表/数据
-- 下面是查询
WITH
stockCTE
AS
(
SELECT
Depot.ID AS DepotID,
Container.ID AS ConID,
Stock.GoodsID,
Depot.Name AS DepotName,
Stock.ID AS StockID,
LastCount
FROM
Depot
JOIN Container
ON (Depot.ID = Container.DepotID)
JOIN Stock
ON (Container.ID = Stock.ConID)
)
SELECT
DepotID,
GoodsID,
DepotName,
SUM(LastCount) AS StockCount
FROM
stockCTE
WHERE
( SELECT
COUNT(1)
FROM
stockCTE topn
WHERE
topn.DepotID = stockCTE.DepotID
AND topn.ConID = stockCTE.ConID
AND topn.GoodsID = stockCTE.GoodsID
AND stockCTE.StockID < topn.StockID
) < 1
GROUP BY
DepotID,
GoodsID,
DepotName
DepotID GoodsID DepotName StockCount
----------- ----------- ---------- -----------
1 1 商品仓 2
3 1 成品二仓 60
(2 行受影响)
热心网友
时间：2022-04-08 23:40
借 wangqing999的测试数据修改了下
SELECT B.DEPOTID,A.GOODSID,B.NAME,SUM(A.LASTCOUNT)
FROM STOCK A
LEFT JOIN
(SELECT M.ID AS CID,N.ID,M.DEPOTID,N.NAME FROM CONTAINER M LEFT JOIN DEPOT n
ON M.DEPOTID=N.ID) B
ON A.CONID = B.CID
WHERE NOT EXISTS(SELECT 1 FROM STOCK WHERE CONID=A.CONID AND ID>A.ID)
AND A.GOODSID=1
GROUP BY B.DEPOTID,A.GOODSID,B.NAME
结果
-----------
DEPOTID GOODSID NAME
----------- ----------- ---------- -----------
3 1 成品二仓 60
1 1 商品仓 2
(2 行受影响)
热心网友
时间：2022-04-09 01:15
from Container c join depot d on c.depotid=d.id
join (select conid,goodsid,sum(incount-outcount) StockCount from stock group by conid,conid,goodsid)
t on c.id=t.conid
我靠刚一写完就看到这么多答案了，不过测试了下都在报错 各位还是要注意下要求
热心网友
时间：2022-04-09 03:06
declare @Container TABLE(ID int, DepotID int);
declare @stock TABLE(ID int, ConID int, GoodsID int, InCount int, OutCount int, LastCount int);
INSERT INTO @Depot VALUES(1, 'M');
INSERT INTO @Depot VALUES(2, 'P_1');
INSERT INTO @Depot VALUES(3, 'P_2');
INSERT INTO @Container VALUES(3, 2);
INSERT INTO @Container VALUES(21, 3);
INSERT INTO @Container VALUES(7, 1);
INSERT INTO @Container VALUES(34, 1);
INSERT INTO @stock VALUES(1, 34, 1, 40, 0, 40);
INSERT INTO @stock VALUES(2, 34, 1, 0, 26, 14);
INSERT INTO @stock VALUES(3, 34, 1, 5, 0, 19);
INSERT INTO @stock VALUES(4, 34, 1, 0, 17, 2);
INSERT INTO @stock VALUES(5, 21, 1, 67, 0, 67);
INSERT INTO @stock VALUES(6, 21, 1, 0, 10, 57);
INSERT INTO @stock VALUES(7, 21, 1, 3, 0, 60);
INSERT INTO @stock VALUES(8, 7, 1, 5, 0, 5);
INSERT INTO @stock VALUES(9, 7, 1, 0, 5, 0);
------------- 前面都是准备数据，真正的查询从这里开始。
SELECT c.DepotID, t.Mid AS GoodsID, s.LastCount, d.name
FROM
(SELECT MAX(ID) AS Mid, ConID FROM @stock group by ConID) t
INNER JOIN @stock s ON t.Mid = s.ID AND s.LastCount>0
INNER JOIN @Container c ON s.ConID = c.ID
INNER JOIN @Depot d ON c.DepotID = d.ID
热心网友
时间：2022-04-09 05:14
select DepotID,max(GoodsID),max(DepotName),sum(LastCount)
from Container,Stock,Depot where ConID=Container.ID and Depot.ID=DepotID
and Stock.ID in (select max(ID) from Stock where GoodsID=1 group by ConID)
group by DepotID
