如何分离记录以获取金额 [英] How to separate a record to get the amount

问题描述

我的库存总量为160.

I got total 160 Quantity in the Stock.

如何获得前100个数量的总金额和后60个数量的总金额?

How to get first 100 quantity total amount and last 60 quantity total amount?

该表按日期和库存ID排序.

The table is sort by Date and Stock 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 +
+----------+------------+----------+----------+------------+

我需要以下两个结果.谢谢

I need two result of below. Thanks

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 +
+------------+----------+--------------+

推荐答案

尝试一下:

结果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.

Instead of using IN, you can use JOIN.

结果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

这篇关于如何分离记录以获取金额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！