34,838
社区成员




;WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY StockID ORDER BY StockInID DESC) rn FROM dbo.StockIn
),cteb AS (
SELECT ctea.*,
CASE
WHEN [Count] > ctea.StockInCount THEN
0
ELSE
ctea.StockInCount - [Count]
END AS 已出库数量,
CONVERT(DECIMAL(18,10),CASE
WHEN [Count] > ctea.StockInCount THEN
ctea.StockInCount
ELSE
[Count]
END) AS 剩余数量,
CONVERT(DECIMAL(18,10),CASE
WHEN [Count] > ctea.StockInCount THEN
[Count] -ctea.StockInCount
ELSE
0
END) AS tempcount
FROM ctea
JOIN dbo.Stock
ON Stock.StockID = ctea.StockID
WHERE rn = 1
UNION ALL
SELECT ctea.*,
CASE
WHEN cteb.tempcount > ctea.StockInCount THEN
0
ELSE
ctea.StockInCount - cteb.tempcount
END AS 已出库数量,
CONVERT(DECIMAL(18,10),CASE
WHEN cteb.tempcount > ctea.StockInCount THEN
ctea.StockInCount
ELSE
cteb.tempcount
END) AS 剩余数量,
CONVERT(DECIMAL(18,10),CASE WHEN cteb.tempcount-ctea.StockInCount>0 THEN cteb.tempcount-ctea.StockInCount ELSE 0 END) AS tempcount
FROM ctea JOIN cteb ON ctea.rn = cteb.rn+1 AND cteb.StockID = ctea.StockID
)
SELECT StockInID,StockInCount,StockID,已出库数量,剩余数量 FROM cteb ORDER BY cteb.StockInID
CREATE VIEW view_t
AS
WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY StockID ORDER BY StockInID DESC) rn FROM dbo.StockIn
),cteb AS (
SELECT ctea.*,
CASE
WHEN [Count] > ctea.StockInCount THEN
0
ELSE
ctea.StockInCount - [Count]
END AS 已出库数量,
CONVERT(DECIMAL(18,10),CASE
WHEN [Count] > ctea.StockInCount THEN
ctea.StockInCount
ELSE
ctea.StockInCount - [Count]
END) AS 剩余数量,
CONVERT(DECIMAL(18,10),[Count]-ctea.StockInCount) AS tempcount
FROM ctea
JOIN dbo.Stock
ON Stock.StockID = ctea.StockID
WHERE rn = 1
AND ctea.StockID = 1 --读取第一条
UNION ALL
SELECT ctea.*,
CASE
WHEN cteb.tempcount > ctea.StockInCount THEN
0
ELSE
ctea.StockInCount - cteb.tempcount
END AS 已出库数量,
CONVERT(DECIMAL(18,10),CASE
WHEN cteb.tempcount > ctea.StockInCount THEN
ctea.StockInCount
ELSE
cteb.tempcount
END) AS 剩余数量,
CONVERT(DECIMAL(18,10),CASE WHEN cteb.tempcount-ctea.StockInCount>0 THEN cteb.tempcount-ctea.StockInCount ELSE 0 END) AS tempcount
FROM ctea JOIN cteb ON ctea.rn = cteb.rn+1 AND cteb.StockID = ctea.StockID
)
SELECT * FROM cteb
读取view数据
SELECT * FROM dbo.view_t ORDER BY StockInID
;WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY StockID ORDER BY StockInID DESC) rn FROM dbo.StockIn
),cteb AS (
SELECT ctea.*,
CASE
WHEN [Count] > ctea.StockInCount THEN
0
ELSE
ctea.StockInCount - [Count]
END AS 已出库数量,
CONVERT(DECIMAL(18,10),CASE
WHEN [Count] > ctea.StockInCount THEN
ctea.StockInCount
ELSE
ctea.StockInCount - [Count]
END) AS 剩余数量,
CONVERT(DECIMAL(18,10),[Count]-ctea.StockInCount) AS tempcount
FROM ctea
JOIN dbo.Stock
ON Stock.StockID = ctea.StockID
WHERE rn = 1
AND ctea.StockID = 1 --读取第一条
UNION ALL
SELECT ctea.*,
CASE
WHEN cteb.tempcount > ctea.StockInCount THEN
0
ELSE
ctea.StockInCount - cteb.tempcount
END AS 已出库数量,
CONVERT(DECIMAL(18,10),CASE
WHEN cteb.tempcount > ctea.StockInCount THEN
ctea.StockInCount
ELSE
cteb.tempcount
END) AS 剩余数量,
CONVERT(DECIMAL(18,10),CASE WHEN cteb.tempcount-ctea.StockInCount>0 THEN cteb.tempcount-ctea.StockInCount ELSE 0 END) AS tempcount
FROM ctea JOIN cteb ON ctea.rn = cteb.rn+1 AND cteb.StockID = ctea.StockID
)
SELECT StockInID,StockInCount,StockID,已出库数量,剩余数量 FROM cteb ORDER BY cteb.StockInID
;WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY StockID ORDER BY StockInID DESC) rn FROM dbo.StockIn
),cteb AS (
SELECT ctea.*,
CASE
WHEN [Count] > ctea.StockInCount THEN
0
ELSE
ctea.StockInCount - [Count]
END AS 已出库数量,
CONVERT(DECIMAL(18,4),CASE
WHEN [Count] > ctea.StockInCount THEN
ctea.StockInCount
ELSE
ctea.StockInCount - [Count]
END) AS 剩余数量,
CONVERT(DECIMAL(18,4),[Count]-ctea.StockInCount) AS tempcount
FROM ctea
JOIN dbo.Stock
ON Stock.StockID = ctea.StockID
WHERE rn = 1
AND ctea.StockID = 1 --读取第一条
UNION ALL
SELECT ctea.*,
CASE
WHEN cteb.tempcount > ctea.StockInCount THEN
0
ELSE
ctea.StockInCount - cteb.tempcount
END AS 已出库数量,
CONVERT(DECIMAL(18,4),CASE
WHEN cteb.tempcount > ctea.StockInCount THEN
ctea.StockInCount
ELSE
cteb.tempcount
END) AS 剩余数量,
CONVERT(DECIMAL(18,4),CASE WHEN cteb.tempcount-ctea.StockInCount>0 THEN cteb.tempcount-ctea.StockInCount ELSE 0 END) AS tempcount
FROM ctea JOIN cteb ON ctea.rn = cteb.rn+1 AND cteb.StockID = ctea.StockID
)
SELECT StockInID,StockInCount,StockID,已出库数量,剩余数量 FROM cteb ORDER BY cteb.StockInID