最后一次数据mysql中的组条件

Question

我有这样的数据：

Table LOT
+-------+--------+
|Lot_id | Prod_id|
+-------+--------+
| LOT-1 | Prd-1  |
| LOT-1 | Prd-2  |
| LOT-1 | Prd-3  |
| LOT-2 | Prd-4  |
+-------+--------+

Table Process
+-------+--------+--------+------------+----------+
|proc_id|proc_cat|proc_seq|proc_prod_id|t_proc_qty|
+-------+--------+--------+------------+----------+
|   1   | Proc-A |   1    |    Prd-1   |   100    |
|   2   | Proc-H |   2    |    Prd-1   |   100    |
|   3   | Proc-D |   3    |    Prd-1   |   100    |
|   4   | Proc-A |   1    |    Prd-2   |   100    |
|   5   | Proc-H |   2    |    Prd-2   |   100    |
|   6   | Proc-D |   3    |    Prd-2   |    20    |
|   7   | Proc-Q |   4    |    Prd-2   |    20    |
|   8   | Proc-A |   1    |    Prd-3   |   100    |
|   9   | Proc-H |   2    |    Prd-3   |   100    |
|  10   | Proc-D |   3    |    Prd-3   |    50    |
|  11   | Proc-O |   1    |    Prd-4   |    80    |
|  12   | Proc-F |   2    |    Prd-4   |    80    |
|  13   | Proc-H |   3    |    Prd-4   |    80    |
+-------+--------+--------+------------+----------+

我想要这样的数据，如果我只想选择LOT=LOT-1。表批次与表进程和数据的累加和(T_proc_qty)从上一次proc_seq开始，每个proc_prod_id和组按proc_cat进行，顺序按proc_seq进行。

    +--------+--------+----------+
    |proc_cat|proc_seq|t_proc_qty|
    +--------+--------+----------+
    | Proc-D |   3    |   150    |->accumulated from Prd-1 and prd-3 in last process is seq 3
    | Proc-Q |   4    |    20    |->accumulated from Prd-2 in last process is seq 4
    +--------+--------+----------+

我在MySQL中使用什么查询？

我塞进了问号

SELECT proc_cat, proc_seq, SUM(t_proc_qty) 
FROM Process 
LEFT JOIN Lot ON proc_prod_id=Prod_id 
WHERE Lot_id='LOT-1' 
GROUP BY proc_prod_id 
ORDER BY proc_seq DESC LIMIT 1

这个用于试用查询SQLFiddle的架构

Answer 1

在表Process中，您希望记录的每个proc_prod_id具有最高的proc_id

select *
from process
where not exists 
(
  select * 
  from process later 
  where later.proc_prod_id = process.proc_prod_id
  and later.proc_id > process.proc_id
);

从这些数据中，您希望为每个proc_cat和proc_sec提供一个聚合。而且，您还希望只考虑表prod_id中的“LOT-1”。

完整的查询：

select proc_cat, proc_seq, sum(t_proc_qty)
from process
where proc_prod_id in (select prod_id from lot where lot_id = 'LOT-1')
and not exists 
(
  select * 
  from process later 
  where later.proc_prod_id = process.proc_prod_id
  and later.proc_id > process.proc_id
)
group by proc_cat, proc_seq
order by proc_cat, proc_seq;

SQL小提琴：http://sqlfiddle.com/#!9/1fa3fd/5