当前位置: 代码迷 >> Sql Server >> SQL语句:统计接续不合格的最大个数
  详细解决方案

SQL语句:统计接续不合格的最大个数

热度:40   发布时间:2016-04-24 09:28:49.0
SQL语句:统计连续不合格的最大个数

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
B                                    3            (说明:8,9,10)            2014-11-10
------解决思路----------------------
--SQL Server 2000
-- 测试数据
SELECT *
  INTO #table1
  FROM (
    SELECT 1 AutoId,'A' 订单编号,1 不合格,'2014-11-01' 时间 UNION ALL
    SELECT 2,'B',1,'2014-11-02' UNION ALL
    SELECT 3,'A',1,'2014-11-03' UNION ALL
    SELECT 4,'A',1,'2014-11-04' UNION ALL
    SELECT 5,'B',1,'2014-11-05' UNION ALL
    SELECT 6,'A',1,'2014-11-06' UNION ALL
    SELECT 7,'A',0,'2014-11-07' UNION ALL
    SELECT 8,'B',1,'2014-11-08' UNION ALL
    SELECT 9,'B',1,'2014-11-09' UNION ALL
    SELECT 10,'B',1,'2014-11-10' 
) t
GO

-- 求分段范围
    SELECT t1.订单编号,
           t1.时间,
           MAX(t0.时间) 时间0,
           MIN(t2.时间) 时间2
      INTO #table2
      FROM #table1 t1
 LEFT JOIN #table1 t0
        ON t0.订单编号 = t1.订单编号
       AND t0.不合格 = 0
       AND t0.时间 < t1.时间
 LEFT JOIN #table1 t2
        ON t2.订单编号 = t1.订单编号
       AND t2.不合格 = 0
       AND t2.时间 > t1.时间
     WHERE t1.不合格 = 1
  GROUP BY t1.订单编号, t1.时间
  ORDER BY t1.订单编号, t1.时间
--SELECT * FROM #table2

-- 分段统计
    SELECT 订单编号,
           COUNT(*) 不合格,
           MAX(时间)时间
      INTO #table3
      FROM #table2
  GROUP BY 订单编号,时间0,时间2
--SELECT * FROM #table3

-- 对应多个分段都是最大的不合格次数,求时间最大的分段
SELECT t.订单编号,
       t.不合格,
       MAX(t.时间) 时间
  FROM #table3 t
  JOIN (  SELECT 订单编号,
                 MAX(不合格) 不合格
            FROM #table3
        GROUP BY 订单编号
       ) tm
    ON tm.订单编号 = t.订单编号
   AND tm.不合格 = t.不合格
GROUP BY t.订单编号, t.不合格
  相关解决方案