none
求连续数据 RRS feed

  • 问题

  • 事例数据

    with CET_Table as 
    (
    select 'A' name,1 as [month],0 as value
    union all select 'A',2,1
    union all select 'A',3,0
    union all select 'A',4,0 
    union all select 'A',5,1
    union all select 'A',6,0 
    union all select 'A',7,0 
    union all select 'A',8,0 
    union all select 'A',9,0 
    union all select 'A',10,0 
    union all select 'A',11,1 
    union all select 'A',12,1  
    union all select 'B',1,1
    union all select 'B',2,0
    union all select 'B',3,0
    union all select 'B',4,1
    union all select 'B',5,1
    union all select 'B',6,1
    union all select 'B',7,1
    union all select 'B',8,1
    union all select 'B',9,0
    union all select 'B',10,0
    union all select 'B',11,0
    union all select 'B',12,1
    )
    select * from CET_Table
    
    结果:
    
    name month value number
    A 1 0 1
    A 2 1 1
    A 3 0 1
    A 4 0 2
    A 5 1 1
    A 6 0 1
    A 7 0 2
    A 8 0 3
    A 9 0 4
    A 10 0 5
    A 11 1 1
    A 12 1 2
    B 1 1 1
    B 2 0 1
    B 3 0 2
    B 4 1 1
    B 5 1 2
    B 6 1 3
    B 7 1 4
    B 8 1 5
    B 9 0 1
    B 10 0 2
    B 11 0 3
    B 12 1 1
    

    这是A和B两种产品的一年的销售情况,每个月都有, 0 代表本月份没有销售,1代表本月份有销售.

    现在求,一年中连续没有销售的最大月数,和一年中连续销售的最大月数

    比如A产品 连续销售的最大月数为 11月和12月,所以为 2

    A产品连续没有销售的最大月数为 6月,7月,8月,9月,10月 为 5

    想了两天没有想通应该怎么写句子,希望高手指点.谢谢!

    2012年4月13日 1:30

答案

  • 你好,

    declare @month table (months int)
    insert into @month values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
    with CET_Table as 
    (
     select 'A' name,1 as [month],10 as moeny
     union all select 'A',2,100
     union all select 'A',5,21
     union all select 'A',11,111 
    union all select 'A',12,57  
    union all select 'B',4,60
     union all select 'B',5,30
     union all select 'B',6,20
     union all select 'B',7,10
     union all select 'B',8,100
     union all select 'B',12,67
     ),
      CTE_changed as
     (
    select name = ( case when t.name IS NULL then 'A' else t.name end )
    ,month = ( case when t.month IS NULL then m.months else t.month end )
    ,value = ( case when t.moeny IS NULL then 0 else 1 end )
    ,moeny = ( case when t.moeny IS NULL then 0 else t.moeny end )
    from @month m left join CET_Table t 
    on m.months = t.month AND t.name = 'A' 
    union all 
    select 
    name = ( case when t.name IS NULL then 'B' else t.name end )
    ,month = ( case when t.month IS NULL then m.months else t.month end )
    ,value = ( case when t.moeny IS NULL then 0 else 1 end )
    ,moeny  = ( case when t.moeny IS NULL then 0 else t.moeny end )
    from @month m left join CET_Table t 
    on m.months = t.month AND t.name = 'B' 
    )
    
    select name as [产品],
      max(case when value = 1 then cnt else 0 end) as [连续销售的最大月数],
      max(case when value = 0 then cnt else 0 end) as [连续没有销售的最大月数]
    from
     (
      select name,gid,value,count(1) as cnt from
      (
        select *,gid=month-(select count(*) from CTE_changed  where name=t.name and value = t.value and month<t.month) 
        from CTE_changed t
      ) a
      group by name,gid,value
     ) b
    group by name

    Thanks.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.


    2012年4月16日 5:24
    版主
  • 谢谢 百年树人 的回答,思想很巧妙,

    如果现在问题难度升级,

    示例数据库:

    with CET_Table as
    (
    select 'A' name,1 as [month],10 as moeny
    union all select 'A',2,100
    union all select 'A',5,21
    union all select 'A',11,111
    union all select 'A',12,57 
    union all select 'B',4,60
    union all select 'B',5,30
    union all select 'B',6,20
    union all select 'B',7,10
    union all select 'B',8,100
    union all select 'B',12,67
    )

    也就是说平时,数据只存有销售的月份,而且存的都是本月的金额。像这样的情况应该怎么样达到上面的效果呀,望高手不吝赐教,谢谢。


    ;with CET_Table as 
    (
     select 'A' name,1 as [month],10 as moeny
     union all select 'A',2,100
     union all select 'A',5,21
     union all select 'A',11,111 
    union all select 'A',12,57  
    union all select 'B',4,60
     union all select 'B',5,30
     union all select 'B',6,20
     union all select 'B',7,10
     union all select 'B',8,100
     union all select 'B',12,67
     ),
    tmpTB as(
    select t1.name,t1.month,(case when t2.moeny is not null then 1 else 0 end) as value
    from(
      select * from
      (select distinct name from CET_Table) a,
      (select top 12 month=row_number() over(order by getdate()) from sys.objects) b
    ) t1
    left join CET_Table t2 on t1.name=t2.name and t1.month=t2.month
    )
    
    select name as [产品],
      max(case when value=1 then cnt else 0 end) as [连续销售的最大月数],
      max(case when value=0 then cnt else 0 end) as [连续没有销售的最大月数]
    from
     (
      select name,gid,value,count(1) as cnt from
      (
        select *,gid=month-(select count(*) from tmpTB where name=t.name and value=t.value and month<t.month) 
        from tmpTB t
      ) a
      group by name,gid,value
     ) b
    group by name
    
    /**
    产品   连续销售的最大月数   连续没有销售的最大月数
    ---- ----------- -----------
    A    2           5
    B    5           3
    
    (2 行受影响)
    **/
    


    学如逆水行舟,不进则退!

    2012年4月17日 16:05

全部回复

  • 学习后搞出来一个

    select * ,number = (select COUNT(1) from CET_Table B where B.name = A.name and B.value = A.value and B.month<=A.month
    and B.month>=(select isnull(max(c.month),1) from CET_Table C where C.name = A.name and C.value <> A.value and C.month<=A.month))
    from CET_Table A

    2012年4月13日 2:35
  • with CET_Table as 
    (
    select 'A' name,1 as [month],0 as value
    union all select 'A',2,1
    union all select 'A',3,0
    union all select 'A',4,0 
    union all select 'A',5,1
    union all select 'A',6,0 
    union all select 'A',7,0 
    union all select 'A',8,0 
    union all select 'A',9,0 
    union all select 'A',10,0 
    union all select 'A',11,1 
    union all select 'A',12,1  
    union all select 'B',1,1
    union all select 'B',2,0
    union all select 'B',3,0
    union all select 'B',4,1
    union all select 'B',5,1
    union all select 'B',6,1
    union all select 'B',7,1
    union all select 'B',8,1
    union all select 'B',9,0
    union all select 'B',10,0
    union all select 'B',11,0
    union all select 'B',12,1
    )
    
    -->查询语句
    select name as [产品],
      max(case when value=1 then cnt else 0 end) as [连续销售的最大月数],
      max(case when value=0 then cnt else 0 end) as [连续没有销售的最大月数]
    from
     (
      select name,gid,value,count(1) as cnt from
      (
        select *,gid=month-(select count(*) from CET_Table where name=t.name and value=t.value and month<t.month) 
        from CET_Table t
      ) a
      group by name,gid,value
     ) b
    group by name
    
    -->查询结果
    /**
    产品   连续销售的最大月数   连续没有销售的最大月数
    ---- ----------- -----------
    A    2           5
    B    5           3
    
    (2 行受影响)
    **/


    学如逆水行舟,不进则退!

    2012年4月13日 6:41
  • 谢谢 百年树人 的回答,思想很巧妙,

    如果现在问题难度升级,

    示例数据库:

    with CET_Table as
    (
    select 'A' name,1 as [month],10 as moeny
    union all select 'A',2,100
    union all select 'A',5,21
    union all select 'A',11,111
    union all select 'A',12,57 
    union all select 'B',4,60
    union all select 'B',5,30
    union all select 'B',6,20
    union all select 'B',7,10
    union all select 'B',8,100
    union all select 'B',12,67
    )

    也就是说平时,数据只存有销售的月份,而且存的都是本月的金额。像这样的情况应该怎么样达到上面的效果呀,望高手不吝赐教,谢谢。

    2012年4月13日 15:58
  • 你好,

    declare @month table (months int)
    insert into @month values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
    with CET_Table as 
    (
     select 'A' name,1 as [month],10 as moeny
     union all select 'A',2,100
     union all select 'A',5,21
     union all select 'A',11,111 
    union all select 'A',12,57  
    union all select 'B',4,60
     union all select 'B',5,30
     union all select 'B',6,20
     union all select 'B',7,10
     union all select 'B',8,100
     union all select 'B',12,67
     ),
      CTE_changed as
     (
    select name = ( case when t.name IS NULL then 'A' else t.name end )
    ,month = ( case when t.month IS NULL then m.months else t.month end )
    ,value = ( case when t.moeny IS NULL then 0 else 1 end )
    ,moeny = ( case when t.moeny IS NULL then 0 else t.moeny end )
    from @month m left join CET_Table t 
    on m.months = t.month AND t.name = 'A' 
    union all 
    select 
    name = ( case when t.name IS NULL then 'B' else t.name end )
    ,month = ( case when t.month IS NULL then m.months else t.month end )
    ,value = ( case when t.moeny IS NULL then 0 else 1 end )
    ,moeny  = ( case when t.moeny IS NULL then 0 else t.moeny end )
    from @month m left join CET_Table t 
    on m.months = t.month AND t.name = 'B' 
    )
    
    select name as [产品],
      max(case when value = 1 then cnt else 0 end) as [连续销售的最大月数],
      max(case when value = 0 then cnt else 0 end) as [连续没有销售的最大月数]
    from
     (
      select name,gid,value,count(1) as cnt from
      (
        select *,gid=month-(select count(*) from CTE_changed  where name=t.name and value = t.value and month<t.month) 
        from CTE_changed t
      ) a
      group by name,gid,value
     ) b
    group by name

    Thanks.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.


    2012年4月16日 5:24
    版主
  • 谢谢 百年树人 的回答,思想很巧妙,

    如果现在问题难度升级,

    示例数据库:

    with CET_Table as
    (
    select 'A' name,1 as [month],10 as moeny
    union all select 'A',2,100
    union all select 'A',5,21
    union all select 'A',11,111
    union all select 'A',12,57 
    union all select 'B',4,60
    union all select 'B',5,30
    union all select 'B',6,20
    union all select 'B',7,10
    union all select 'B',8,100
    union all select 'B',12,67
    )

    也就是说平时,数据只存有销售的月份,而且存的都是本月的金额。像这样的情况应该怎么样达到上面的效果呀,望高手不吝赐教,谢谢。


    ;with CET_Table as 
    (
     select 'A' name,1 as [month],10 as moeny
     union all select 'A',2,100
     union all select 'A',5,21
     union all select 'A',11,111 
    union all select 'A',12,57  
    union all select 'B',4,60
     union all select 'B',5,30
     union all select 'B',6,20
     union all select 'B',7,10
     union all select 'B',8,100
     union all select 'B',12,67
     ),
    tmpTB as(
    select t1.name,t1.month,(case when t2.moeny is not null then 1 else 0 end) as value
    from(
      select * from
      (select distinct name from CET_Table) a,
      (select top 12 month=row_number() over(order by getdate()) from sys.objects) b
    ) t1
    left join CET_Table t2 on t1.name=t2.name and t1.month=t2.month
    )
    
    select name as [产品],
      max(case when value=1 then cnt else 0 end) as [连续销售的最大月数],
      max(case when value=0 then cnt else 0 end) as [连续没有销售的最大月数]
    from
     (
      select name,gid,value,count(1) as cnt from
      (
        select *,gid=month-(select count(*) from tmpTB where name=t.name and value=t.value and month<t.month) 
        from tmpTB t
      ) a
      group by name,gid,value
     ) b
    group by name
    
    /**
    产品   连续销售的最大月数   连续没有销售的最大月数
    ---- ----------- -----------
    A    2           5
    B    5           3
    
    (2 行受影响)
    **/
    


    学如逆水行舟,不进则退!

    2012年4月17日 16:05