SQL如何统计连续不合格产品的最大个数?

[sixgj]

AutoId 订单编号 不合格 时间
1 A 1 2014-11-1
2 B 1 2014-11-2
3 A 1 2014-11-3
4 A 1 2014-11-4
5 B 1 2014-11-5
6 A 1 2014-11-6
7 A 0 2014-11-7
8 B 1 2014-11-8
9 B 1 2014-11-9
10 B 1 2014-11-10


统计 同一订单的连续不合格的最大个数，结果如下：

编号 不合格 最后时间 开始时间
A 4 (说明：1,3,4,6) 2014-11-6 2014-11-1
B 3 (说明：8,9,10) 2014-11-10 2014-11-8

[BlueStorm]

这个问题无法用一个简单查询语句解决。 解决问题的思路：先统计合格的记录作为边界条件，再统计不合格的记录。

set nocount on;
declare @order table (AutoId integer, 订单编号 char(1), 不合格 bit, 时间 date);
insert into @order values
  (1, 'A', 1, '2014-11-1'),
  (2, 'B', 1, '2014-11-2'),
  (3, 'A', 1, '2014-11-3'),
  (4, 'A', 1, '2014-11-4'),
  (5, 'B', 1, '2014-11-5'),
  (6, 'A', 1, '2014-11-6'),
  (7, 'A', 0, '2014-11-7'),
  (8, 'B', 1, '2014-11-8'),
  (9, 'B', 1, '2014-11-9'),
  (10,'B', 1, '2014-11-10');

declare @minid integer = (select min(AutoId) from @order) - 1;
declare @maxid integer = (select max(AutoId) from @order) + 1;

declare @ids table (id integer);
insert into @ids select AutoId from @order where 不合格=0;
insert into @ids values (@minid), (@maxid);

declare @group table (startid integer, endid integer);
insert into @group
  select startid=id, endid = (select min(id) from @ids where id > a.id)
    from @ids a where id < @maxid;

with t_cnt as
 (select a.startid, a.endid, b.订单编号, 不合格数量 = count(*)
    from @group a, @order b
    where b.不合格=1 and b.AutoId between a.startid and a.endid
    group by a.startid, a.endid, b.订单编号),
 t_max_cnt as
   (select * from t_cnt a where 不合格数量 = (select max(不合格数量) from t_cnt where startid=a.startid))
 select a.订单编号, b.不合格数量, 最后时间=max(时间), 开始时间=min(时间)
   from @order a, t_max_cnt b
   where (a.AutoId between b.startid and b.endid) and a.订单编号=b.订单编号 and a.不合格 = 1
   group by a.订单编号, b.不合格数量
   order by 开始时间;

[BlueStorm]

如果一定要写成一个查询语句，可以这样写：

set nocount on;
declare @order table (AutoId integer, 订单编号 char(1), 不合格 bit, 时间 date);
insert into @order values
  (1, 'A', 1, '2014-11-1'),
  (2, 'B', 1, '2014-11-2'),
  (3, 'A', 1, '2014-11-3'),
  (4, 'A', 1, '2014-11-4'),
  (5, 'B', 1, '2014-11-5'),
  (6, 'A', 1, '2014-11-6'),
  (7, 'A', 0, '2014-11-7'),
  (8, 'B', 1, '2014-11-8'),
  (9, 'B', 1, '2014-11-9'),
  (10,'B', 1, '2014-11-10');
------------------------------------------------------------------
with 
  t_ids as
   (select id=AutoId from @order where 不合格=0
    union
    select min(AutoId)-1 from @order
    union
    select max(AutoId)+1 from @order),
  t_group as
   (select startid=id, endid = (select min(id) from t_ids where id > a.id)
      from t_ids a where id < (select max(AutoId)+1 from @order)),
  t_cnt as
   (select a.startid, a.endid, b.订单编号, 不合格数量 = count(*)
      from t_group a, @order b
      where b.不合格=1 and b.AutoId between a.startid and a.endid
      group by a.startid, a.endid, b.订单编号),
  t_max_cnt as
   (select * from t_cnt a where 不合格数量 = (select max(不合格数量) from t_cnt where startid=a.startid))
 select a.订单编号, b.不合格数量, 最后时间=max(时间), 开始时间=min(时间)
   from @order a, t_max_cnt b
   where (a.AutoId between b.startid and b.endid) and a.订单编号=b.订单编号 and a.不合格 = 1
   group by a.订单编号, b.不合格数量
   order by 开始时间;