我总共有 160 个库存数量。
如何获得前 100 个数量总金额和后 60 个数量总金额?
该表按日期和库存 ID 排序。
tblStock
+----------+------------+----------+----------+------------+
+ Stock ID + Product ID + Quantity + Amount + Date +
+----------+------------+----------+----------+------------+
+ 1 + 1001 + 50 + 10.00 + 2014-11-10 +
+----------+------------+----------+----------+------------+
+ 2 + 1001 + 70 + 11.00 + 2014-11-11 +
+----------+------------+----------+----------+------------+
+ 3 + 1001 + 30 + 9.90 + 2014-11-12 +
+----------+------------+----------+----------+------------+
+ 4 + 1001 + 10 + 10.20 + 2014-11-13 +
+----------+------------+----------+----------+------------+
我需要下面的两个结果。谢谢
Result A (first 100 quantity)
+------------+----------+--------------+
+ Product ID + Quantity + Total Amount +
+------------+----------+--------------+
+ 1001 + 100 + 1050.00 +
+------------+----------+--------------+
Result B (last 60 quantity)
+------------+----------+--------------+
+ Product ID + Quantity + Total Amount +
+------------+----------+--------------+
+ 1001 + 60 + 619.00 +
+------------+----------+--------------+
对于前 100
个选择
Product ID
,总和(数量)作为数量,总和(金额)作为TotalAmount
从 (选择 * 从 tblstock 按tblStock
订购。Stock ID
ASC 限制 100) t1 按Date
分组,Product ID
对于最后 60
选择Product ID
, 总和(数量)作为数量, 总和(金额) 作为
Total Amount
从 (选择 * 从 tblstock 订单中按tblStock
.Stock ID
DESC 限制 60) t1 按Date
分组,Product ID
试试这个:
结果 A:
SELECT A.ProductID AS 'Product ID', '100' AS 'Quantity', SUM(A.Amount) as 'Total Amount'
FROM tblStock A
WHERE StockID IN (SELECT B.StockID from tblStock B ORDER BY B.StockID ASC LIMIT 100)
结果 B:
SELECT A.ProductID AS 'Product ID', '60' AS 'Quantity', SUM(A.Amount) as 'Total Amount'
FROM tblStock A
WHERE StockID IN (SELECT B.StockID from tblStock B ORDER BY B.StockID DESC LIMIT 60)
您可以使用 JOIN,而不是使用 IN。
结果 A:
SELECT A.ProductID AS 'Product ID', '100' AS 'Quantity', SUM(A.Amount) AS 'Total Amount'
FROM tblStock AS A
INNER JOIN
(SELECT StockID from tblStock ORDER BY StockID ASC LIMIT 100) AS B
ON A.StockID = B.StockID
结果 B:
SELECT A.ProductID AS 'Product ID', '60' AS 'Quantity', SUM(A.Amount) AS 'Total Amount'
FROM tblStock AS A
INNER JOIN
(SELECT StockID from tblStock ORDER BY StockID DESC LIMIT 60) AS B
ON A.StockID = B.StockID