SQL语句：统计连续不合格的最大个数

xugeng 2014-12-05 03:30:13

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

...全文

551 22 打赏收藏转发到动态举报

写回复

用AI写文章

22 条回复

切换为时间正序

请发表友善的回复…

发表回复

xugeng 2015-03-11

打赏
举报

在触发器上面使用，频繁死锁，有没有办法？

Ginnnnnnnn 2014-12-10

打赏
举报


USE tempdb
GO

;WITH CTE (AutoId,[订单编号],[不合格],[时间]) AS
(
	SELECT 1,'A',1,'2014-11-1' UNION ALL
	SELECT 2,'B',1,'2014-11-2' UNION ALL
	SELECT 3,'A',1,'2014-11-3' UNION ALL
	SELECT 4,'A',1,'2014-11-4' UNION ALL
	SELECT 5,'B',1,'2014-11-5' UNION ALL
	SELECT 6,'A',1,'2014-11-6' UNION ALL
	SELECT 7,'A',0,'2014-11-7' UNION ALL
	SELECT 8,'B',1,'2014-11-8' UNION ALL
	SELECT 9,'B',1,'2014-11-9' UNION ALL
	SELECT 10,'B',1,'2014-11-10' UNION ALL
	SELECT 11,'B',0,'2014-11-10'
)
,CTE2 AS(
SELECT  [AutoId],
		[订单编号] ,
		[时间],
		ROW_NUMBER() OVER (PARTITION BY [订单编号] ORDER BY [AutoId]) AS [订单序号],
		ROW_NUMBER() OVER (PARTITION BY [订单编号] ORDER BY [AutoId]) - ROW_NUMBER() OVER (PARTITION BY [订单编号],[不合格] ORDER BY [AutoId]) AS [连续不合格数]
	FROM CTE)
SELECT b.[订单编号],b.[连续不合格数],c.[时间]
	FROM (SELECT [订单编号],MAX([连续不合格数]) AS [连续不合格数] FROM CTE2 GROUP BY [订单编号]) a
		INNER JOIN CTE2 b ON a.[订单编号] = b.[订单编号] AND a.[连续不合格数] = b.[连续不合格数]
		INNER JOIN CTE2 c ON b.[订单编号] = c.[订单编号] AND b.订单序号 = c.订单序号 + 1

Tiger_Zhao 2014-12-10

打赏
举报

SQL Server 2000按我#11的方式来，就是加个[台位号]字段而已。
这样都搞不定，基础不够啊。
先换些简单的需求来做，不要一上来就是这么复杂的。

xugeng 2014-12-10

打赏
举报

我的数据库是sqlserver2000，报错如下：消息 170，级别 15，状态 1，第 23 行第 1 行: ';' 附近有语法错误。消息 195，级别 15，状态 10，第 29 行 'row_number' 不是可以识别的函数名。

引用 17 楼 roy_88 的回复:

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
	drop table #T
Go
Create table #T([AutoId] int,[台位号] nvarchar(2),[订单号] nvarchar(2),[不合格] int,[日期] Datetime)
Insert #T
select 1,N'T1',N'A1',1,'2014/1/1' union all
select 2,N'T1',N'A1',1,'2014/1/2' union all
select 3,N'T1',N'A1',1,'2014/1/3' union all
select 4,N'T2',N'A1',1,'2014/1/4' union all
select 5,N'T2',N'A1',1,'2014/1/5' union all
select 6,N'T3',N'A1',1,'2014/1/6' union all
select 7,N'T3',N'A1',1,'2014/1/7' union all
select 8,N'T3',N'A1',1,'2014/1/8' union all
select 9,N'T1',N'A1',0,'2014/1/9' union all
select 10,N'T1',N'A1',1,'2014/1/10' union all
select 11,N'T2',N'A1',0,'2014/1/11' union all
select 12,N'T2',N'A1',1,'2014/1/12' union all
select 13,N'T3',N'A1',1,'2014/1/13' union all
select 14,N'T3',N'A1',1,'2014/1/14'
Go
;with T1
as
(
select*
from (
Select *
,row_number()over(order by [台位号],[订单号],[AutoId])-row_number()over(order by [台位号],[订单号],[不合格],[AutoId]) as grp from #T) as a
),T2
as
(
select [台位号],[订单号],grp,count(1) as Con,max([日期]) as [日期] from T1  where [不合格]=1 group by [台位号],[订单号],grp
)

select 
	[台位号],[订单号] ,[Con] as [不合格],[日期]
from T2 as a 
where not exists(select 1 from T2 where [台位号]=a.[台位号] and [订单号]=a.[订单号] and Con>a.Con)

/*
台位号	订单号	不合格	日期
T1	A1	3	2014-01-03 00:00:00.000
T2	A1	2	2014-01-05 00:00:00.000
T3	A1	5	2014-01-14 00:00:00.000
*/

中国风 2014-12-10

打赏
举报

引用 18 楼 xugeng 的回复:

我的数据库是sqlserver2000，报错如下：消息 170，级别 15，状态 1，第 23 行第 1 行: ';' 附近有语法错误。消息 195，级别 15，状态 10，第 29 行 'row_number' 不是可以识别的函数名。

--> --> (Roy)生成測試數據
  
if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([AutoId] int,[台位号] nvarchar(2),[订单号] nvarchar(2),[不合格] int,[日期] Datetime)
Insert #T
select 1,N'T1',N'A1',1,'2014/1/1' union all
select 2,N'T1',N'A1',1,'2014/1/2' union all
select 3,N'T1',N'A1',1,'2014/1/3' union all
select 4,N'T2',N'A1',1,'2014/1/4' union all
select 5,N'T2',N'A1',1,'2014/1/5' union all
select 6,N'T3',N'A1',1,'2014/1/6' union all
select 7,N'T3',N'A1',1,'2014/1/7' union all
select 8,N'T3',N'A1',1,'2014/1/8' union all
select 9,N'T1',N'A1',0,'2014/1/9' union all
select 10,N'T1',N'A1',1,'2014/1/10' union all
select 11,N'T2',N'A1',0,'2014/1/11' union all
select 12,N'T2',N'A1',1,'2014/1/12' union all
select 13,N'T3',N'A1',1,'2014/1/13' union all
select 14,N'T3',N'A1',1,'2014/1/14'
Go
--[AutoId]有断号时需要重新生成序号
Select *
,ID=IDENTITY(INT,1,1)
,ID2=(SELECT COUNT(1) FROM #T WHERE [台位号]=a.[台位号] AND [订单号]=a.[订单号]  AND [不合格]=a.[不合格] AND [AutoId]<=a.[AutoId] )
INTO #1
from #T AS a
ORDER BY  [台位号],[订单号],[AutoId]

SELECT  [台位号],[订单号],ID-ID2 AS Grp,count(1) as Con,max([日期]) as [日期] INTO #2 FROM #1 where [不合格]=1 GROUP BY [台位号],[订单号],ID-ID2


 
select 
    [台位号],[订单号] ,[Con] as [不合格],[日期]
from #2 as a 
where not exists(select 1 from #2 where [台位号]=a.[台位号] and [订单号]=a.[订单号] and Con>a.Con)
/*
台位号	订单号	不合格	日期
T1	A1	3	2014-01-03 00:00:00.000
T2	A1	2	2014-01-05 00:00:00.000
T3	A1	5	2014-01-14 00:00:00.000
*/

Tiger_Zhao 2014-12-09

打赏
举报

如果明细记录插入时，同一个需要统计的分组(台位号,订单号)不大会并发，可以用触发器。
否则容易在更新统计记录时产生更新冲突。

中国风 2014-12-09

打赏
举报

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
	drop table #T
Go
Create table #T([AutoId] int,[台位号] nvarchar(2),[订单号] nvarchar(2),[不合格] int,[日期] Datetime)
Insert #T
select 1,N'T1',N'A1',1,'2014/1/1' union all
select 2,N'T1',N'A1',1,'2014/1/2' union all
select 3,N'T1',N'A1',1,'2014/1/3' union all
select 4,N'T2',N'A1',1,'2014/1/4' union all
select 5,N'T2',N'A1',1,'2014/1/5' union all
select 6,N'T3',N'A1',1,'2014/1/6' union all
select 7,N'T3',N'A1',1,'2014/1/7' union all
select 8,N'T3',N'A1',1,'2014/1/8' union all
select 9,N'T1',N'A1',0,'2014/1/9' union all
select 10,N'T1',N'A1',1,'2014/1/10' union all
select 11,N'T2',N'A1',0,'2014/1/11' union all
select 12,N'T2',N'A1',1,'2014/1/12' union all
select 13,N'T3',N'A1',1,'2014/1/13' union all
select 14,N'T3',N'A1',1,'2014/1/14'
Go
;with T1
as
(
select*
from (
Select *
,row_number()over(order by [台位号],[订单号],[AutoId])-row_number()over(order by [台位号],[订单号],[不合格],[AutoId]) as grp from #T) as a
),T2
as
(
select [台位号],[订单号],grp,count(1) as Con,max([日期]) as [日期] from T1  where [不合格]=1 group by [台位号],[订单号],grp
)

select 
	[台位号],[订单号] ,[Con] as [不合格],[日期]
from T2 as a 
where not exists(select 1 from T2 where [台位号]=a.[台位号] and [订单号]=a.[订单号] and Con>a.Con)

/*
台位号	订单号	不合格	日期
T1	A1	3	2014-01-03 00:00:00.000
T2	A1	2	2014-01-05 00:00:00.000
T3	A1	5	2014-01-14 00:00:00.000
*/

xugeng 2014-12-09

打赏
举报

这个代码如果放在触发器中，是否合适？因为要需要实时统计变化量。

xugeng 2014-12-09

打赏
举报

如果时间相同，则计算出错：改了半天，好像还是不对，请再看看，谢谢 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-02' UNION ALL SELECT 4,'A',1,'2014-11-02' 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

xiaodongni 2014-12-08

打赏
举报


create  table test (autoid int,订单编号 varchar(2),不合格 int ,时间 date)
insert into test 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 cte as 
(select * ,1 as groupid from test  where autoid=1 
union all
select b.*,case when  a.不合格=b.不合格  then a.groupid else a.groupid+1 end as groupid 
  from cte as a  join test as b on a.autoid=b.autoid-1)
 select 订单编号,sums,时间  from ( select *,ROW_NUMBER()over(partition by 订单编号 order by  sums desc ) as rn  from
 (select 订单编号,count(订单编号) as sums,max(时间) as 时间 from cte
  where 不合格=1
  group by groupid ,订单编号) as a ) as a 
  where rn =1
  
  --结果
订单编号 sums        时间
---- ----------- ----------
a    4           2014-11-06
b    3           2014-11-10

(2 行受影响)

Charger1991 2014-12-08

打赏
举报

之前已经有过类似的问题了，忘了是哪个贴，一句sql就可以搞定

  select 订单编号,SUM(case 不合格 when 1 then 1 else 0 end) from 订单表 group by 订单编号

Tiger_Zhao 2014-12-08

打赏
举报

WITH table1(AutoId,订单编号,不合格,时间) AS (

    SELECT 1,'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' 

)

,t1 AS(

	SELECT *

		,ROW_NUMBER()OVER(PARTITION BY 订单编号 ORDER BY 时间) rn1

		,ROW_NUMBER()OVER(PARTITION BY 订单编号,不合格 ORDER BY 时间) rn2

	FROM table1

)

,t2 AS (

    SELECT 订单编号,

           COUNT(*)不合格,

           MAX(时间)时间,

           RANK() OVER(PARTITION BY 订单编号 ORDER BY COUNT(*),MAX(时间) DESC) rk

      FROM t1

     WHERE 不合格=1

  GROUP BY 订单编号,rn1-rn2

)

SELECT * 

  FROM t2 

 WHERE rk = 1

订单编号      不合格 时间                         rk

-------- ----------- ---------- --------------------

A                  4 2014-11-06                    1

B                  5 2014-11-10                    1

Zhan-- 2014-12-08

打赏
举报

select count(*),max(时间) from A where 不合格<>'0'
group by 订单编号

xugeng 2014-12-08

打赏
举报

引用楼主 xugeng 的回复:

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

如上所示： 1）订单A 1,3,4,6 连续不合格意味着碰到同一定单的中合格的一条就停止统计，要统计出最大的这个数目 2）订单B 2,5,8,9,10 上面写错了 3） “时间”字段记录最后一条的记录的时间

Tiger_Zhao 2014-12-08

打赏
举报

原SQL 中，无论 SELECT、GROUP BY，还是 ON 中的表关联，只要有[订单编号]的，都加上[台位号]。

xugeng 2014-12-08

打赏
举报

to Tiger_Zhao: 好复杂，好像是正确的，如果加一个台位列，如何处理？ AutoId 台位号订单号不合格日期 1 T1 A1 1 2014/1/1 2 T1 A1 1 2014/1/2 3 T1 A1 1 2014/1/3 4 T2 A1 1 2014/1/4 5 T2 A1 1 2014/1/5 6 T3 A1 1 2014/1/6 7 T3 A1 1 2014/1/7 8 T3 A1 1 2014/1/8 9 T1 A1 0 2014/1/9 10 T1 A1 1 2014/1/10 11 T2 A1 0 2014/1/11 12 T2 A1 1 2014/1/12 13 T3 A1 1 2014/1/13 14 T3 A1 1 2014/1/14 结果：台位号订单号连续不合格数量最后时间 T1 A1 2 2014/1/2 T1 A2 1 2014/1/2 T2 A1 xx xxxxx

Tiger_Zhao 2014-12-08

打赏
举报

--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.不合格

xugeng 2014-12-08

打赏
举报

引用 9 楼 alimake 的回复:


create  table test (autoid int,订单编号 varchar(2),不合格 int ,时间 date)
insert into test 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 cte as 
(select * ,1 as groupid from test  where autoid=1 
union all
select b.*,case when  a.不合格=b.不合格  then a.groupid else a.groupid+1 end as groupid 
  from cte as a  join test as b on a.autoid=b.autoid-1)
 select 订单编号,sums,时间  from ( select *,ROW_NUMBER()over(partition by 订单编号 order by  sums desc ) as rn  from
 (select 订单编号,count(订单编号) as sums,max(时间) as 时间 from cte
  where 不合格=1
  group by groupid ,订单编号) as a ) as a 
  where rn =1
  
  --结果
订单编号 sums        时间
---- ----------- ----------
a    4           2014-11-06
b    3           2014-11-10

(2 行受影响)

数据库是sqlserver2000 ,运行报错：消息 170，级别 15，状态 1，第 3 行第 3 行: ',' 附近有语法错误。消息 195，级别 15，状态 10，第 18 行 'ROW_NUMBER' 不是可以识别的函数名。消息 156，级别 15，状态 1，第 21 行在关键字 'as' 附近有语法错误。

赫敏璋 2014-12-05

打赏
举报

你好，这是我在mssql上建立的数据库

这是我写的sql语句，请您过目

select bianhao as "订单编号",count(hege) as "不合格" 

from Test1

group by bianhao;

运行得到的答案
订单编号不合格
A 5
B 5

时间我不知道该如何显示，您能提供一下思路吗

还在加载中灬 2014-12-05

打赏
举报

;WITH CTE AS(
	SELECT *
		,ROW_NUMBER()OVER(ORDER BY 时间)RN
		,ROW_NUMBER()OVER(PARTITION BY 不合格 ORDER BY 时间)RN1
	FROM TB
)
,CTE2 AS(
	SELECT 订单编号,不合格,COUNT(1)不合格数,MAX(时间)时间
		,ROW_NUMBER()OVER(PARTITION BY 订单编号 ORDER BY COUNT(1) DESC)RN3
	FROM CTE
	GROUP BY 订单编号,不合格,RN-RN1
)
SELECT 订单编号,不合格数 不合格,时间
FROM CTE2
WHERE 不合格=1 AND RN3=1

大概猜了一下，不知道是不是这样

加载更多回复（2）