求解流水数据分组统计

[削死壹狼]

大家好，现有一组原始流水数据，需要用一条SQL语句查询出如下结果，百思不得解啊，求助！

按编码Code分组，并且当Quantity之和等于0时，输出其信息及起始批次号和终止批次号Lot。

例如测试数据

lot code name quantity
l1	c1	n1	+1
l1	c2	n2	+1
l2	c1	n1	+2
l3	c1	n1	+3
l4	c1	n1	-4
l4	c2	n2	+1
l5	c1	n1	+1
l6	c1	n1	-3
l7	c1	n1	+1
l8	c1	n1	-1
l8	c2	n2	-2

按要求输出为

code name lot_s lot_e
c1	n1	l1	l6
c1	n1	l7	l8
c2	n2	l1	l8

测试数据

create table test(lot char(10),code char(6),name char(6),quantity integer(5)); 
insert into test values('l1','c1','n1',+1);
insert into test values('l1','c2','n2',+1);
insert into test values('l2','c1','n1',+2);
insert into test values('l3','c1','n1',+3);
insert into test values('l4','c1','n1',-4);
insert into test values('l4','c2','n2',+1);
insert into test values('l5','c1','n1',+1);
insert into test values('l6','c1','n1',-3);
insert into test values('l7','c1','n1',+1);
insert into test values('l8','c1','n1',-1);
insert into test values('l8','c2','n2',-2);

[ACMAIN_CHM]

mysql> select * from test;
+------+------+------+----------+
| lot  | code | name | quantity |
+------+------+------+----------+
| l1   | c1   | n1   |        1 |
| l1   | c2   | n2   |        1 |
| l2   | c1   | n1   |        2 |
| l3   | c1   | n1   |        3 |
| l4   | c1   | n1   |       -4 |
| l4   | c2   | n2   |        1 |
| l5   | c1   | n1   |        1 |
| l6   | c1   | n1   |       -3 |
| l7   | c1   | n1   |        1 |
| l8   | c1   | n1   |       -1 |
| l8   | c2   | n2   |       -2 |
+------+------+------+----------+
11 rows in set (0.00 sec)

mysql> select code ,name
    ->  , (select max(lot) from test b
    ->          where code=a.code
    ->          and 0=(select sum(quantity) from test where code=a.code and lotbetween b.lot and a.lot)
    ->  ) as lot_s
    ->  ,lot as lot_e
    -> from test a
    -> where (select sum(quantity) from test where code=a.code and lot<=a.lot)=0;
+------+------+-------+-------+
| code | name | lot_s | lot_e |
+------+------+-------+-------+
| c1   | n1   | l1    | l6    |
| c1   | n1   | l7    | l8    |
| c2   | n2   | l1    | l8    |
+------+------+-------+-------+
3 rows in set (0.03 sec)

mysql>

[削死壹狼]

多谢！