SQL查询根据不同的单价、不同时间的数量得到合计值

Question

我有一个交易表，它是这样的:数量是基于不同单价的库存总量。让我们称它为T

id | transaction_time | item | unit_price | quantity | subtotal
1      2012-5-15          A         1.00         15         15.00
2      2012-5-15          A         3.00         15         45.00
3      2012-5-15          B         1.00         10         10.00
4      2012-6-10          A         2.00         15         30.00
5      2012-6-15          A         2.00         10         20.00

我需要通过time...however获取库存中每个项目的总价值，相同的项目基于不同的单价。例如，A的结果是：

transaction_time | item |  quantity | subtotal
    2012-5-15          A     30         60.00
    2012-6-10          A     45         90.00
    2012-6-15          A     40         80.00

2012-5- 15，我们有15个项目A，价格为1.00，15个项目A，价格为3.00，因此总数量为30，小计为15*1+15*3=60。

2012-6-10我们还有15个项目A，价格为2，所以总数量变成了30+15=45，小计变成了60+15*2=90

2012-6-15我们有10个价格为2的项目A，所以价格为2的项目A从15个减少到10个。总数量变成40，小计减少-2*5，变成80。

我试过了

select transaction_time,sum(quantity),sum(subtotal)
where id in(select max(id) from T group by unit_price,item)
group by item
having item=A

这只给了我最后一行

2012-6-15          A     40         80.00

Answer 1

下面的查询(有点复杂，可能很慢，需要优化)可以工作，请检查DEMO

SELECT tr_sub.cur_tt, tr_sub.item, sum(tr.quantity), sum(tr.quantity*tr.unit_price) 
FROM 
  (SELECT tr1.transaction_time as cur_tt, max(tr2.transaction_time) as prev_tt, tr1.item as item, 
     IF (tr1.unit_price=tr2.unit_price, tr1.unit_price, tr2.unit_price) as t_p 
     FROM transactions tr1 LEFT JOIN transactions tr2 ON 
     tr1.transaction_time>=tr2.transaction_time AND tr1.item=tr2.item
    GROUP BY tr1.item, tr1.transaction_time,  t_p
   ) as tr_sub INNER JOIN transactions tr ON 
   tr_sub.prev_tt=tr.transaction_time 
   AND tr_sub.item=tr.item 
   AND tr_sub.t_p=tr.unit_price
GROUP BY tr_sub.item, tr_sub.cur_tt
ORDER BY tr_sub.cur_tt, tr_sub.item

Answer 2

您需要首先确定特定项目的所有可能的unit_price值：

SELECT DISTINCT unit_price
FROM t
WHERE item = 'A'

输出：

unit_price
----------
1
3
2

您还需要确定所有可能的transaction_times

SELECT DISTINCT transaction_time
FROM t
WHERE item = 'A';

输出：

transaction_time
----------------
2012-05-15
2012-06-10
2012-06-15

现在在上述两个集合之间执行CROSS JOIN

SELECT *
FROM (
   SELECT DISTINCT transaction_time
   FROM t
   WHERE item = 'A') AS times
CROSS JOIN (
   SELECT DISTINCT unit_price
   FROM t
   WHERE item = 'A') AS up
ORDER BY times.transaction_time   

要获得以下信息：

transaction_time unit_price
----------------------------
2012-05-15       3
2012-05-15       2
2012-05-15       1
2012-06-10       3
2012-06-10       2
2012-06-10       1
2012-06-15       1
2012-06-15       3
2012-06-15       2

现在使用上面的代码并执行相关子查询，以便从项目'A'中获取每个transaction_time的unit_price

SELECT transaction_time, unit_price,
       (SELECT quantity
        FROM t
        WHERE t.item = 'A' 
              AND t.unit_price = up.unit_price
              AND t.transaction_time <= times.transaction_time
        ORDER BY transaction_time DESC LIMIT 1) AS quantity
FROM (
   SELECT DISTINCT transaction_time
   FROM t
   WHERE item = 'A') AS times
CROSS JOIN (
   SELECT DISTINCT unit_price
   FROM t
   WHERE item = 'A') AS up
ORDER BY times.transaction_time

输出：

transaction_time     unit_price  quantity
----------------------------------------
15.05.2012 00:00:00  1           15
15.05.2012 00:00:00  3           15
15.05.2012 00:00:00  2           NULL
10.06.2012 00:00:00  1           15
10.06.2012 00:00:00  3           15
10.06.2012 00:00:00  2           15
15.06.2012 00:00:00  1           15
15.06.2012 00:00:00  3           15
15.06.2012 00:00:00  2           10

最后的结果很简单，就是对上面的内容执行GROUP BY：

SELECT transaction_time, 
       'A' AS item,
       SUM(quantity) AS quantity,
       SUM(quantity*unit_price) AS subtotal
FROM (           
    SELECT transaction_time, unit_price,
           (SELECT quantity
            FROM t
            WHERE t.item = 'A' 
                  AND t.unit_price = up.unit_price
                  AND t.transaction_time <= times.transaction_time
            ORDER BY transaction_time DESC LIMIT 1) AS quantity
    FROM (
       SELECT DISTINCT transaction_time
       FROM t
       WHERE item = 'A') AS times
    CROSS JOIN (
       SELECT DISTINCT unit_price
       FROM t
       WHERE item = 'A') AS up) AS x
GROUP BY transaction_time

输出：

transaction_time  item  quantity  subtotal
----------------------------------------------
15.05.2012        A     30        60
10.06.2012        A     45        90
15.06.2012        A     40        80