怎读出NUM等于3出现多少次、等于2出现多少次、等于1出现多少次

dq9005 2017-06-23 06:05:28

怎读出NUM等于3出现多少次、等于2出现多少次、等于1出现多少次

当是连号123 只记一次三出现的次数连号12只记2出现的次数 SQL怎么写？

Id num

40 2
39 1
38 0
37 1
36 0
35 0
34 0
33 0
32 3
31 2
30 1
29 0
28 1
27 0
26 0
25 1
24 0
23 0
22 0
21 1
20 0
19 1
18 0
17 3
16 2
15 1
14 0
13 2
12 1
11 0
10 0
09 0
08 0

...全文

270 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

kaixinhua 2017-06-25

打赏
举报

我也不太懂哈哈ccc华780

kaixinhua 2017-06-25

打赏
举报

呵呵，ccc华456

顺势而为1 2017-06-24

打赏
举报

大致写了一个, 供楼主参考



if object_id('tempdb..#Tmp_Tbl') is not null
                drop table #Tmp_Tbl
 
CREATE TABLE #Tmp_Tbl (
		List_ID int identity(1,1),
        Id int ,
        num int)
 
Insert into #Tmp_Tbl (Id,num)
Select  40 ,    2 union                         
Select 39 ,    1 union 
Select 38 ,    0 union
Select 37 ,    1 union
Select 36 ,    0 union
Select 35 ,    0 union
Select 34 ,    0 union
Select 33  ,   0 union
Select 32  ,   3 union
Select 31 ,    2 union
Select 30 ,    1 union
Select 29  ,   0 union
Select 28 ,    1 union
Select 27 ,    0 union
Select 26  ,   0 union
Select 25  ,   1 union
Select 24  ,   0 union
Select 23 ,    0 union
Select 22  ,   0 union
Select 21 ,    1 union
Select 20  ,   0 union
Select 19 ,    1 union
Select 18  ,   0 union
Select 17  ,   3 union
Select 16  ,   2 union
Select 15  ,   1 union
Select 14  ,   0 union
Select 13  ,   2 union
Select 12  ,   1 union
Select 11 ,    0 union
Select 10  ,   0 union
Select 09  ,   0 union
Select 08  ,   0 

-- 1出现的次数
Select *
From #Tmp_Tbl a
Where Exists(Select 1 From #Tmp_Tbl b where b.List_ID=a.List_ID-1 and num=0)
     And Exists(Select 1 From #Tmp_Tbl b where b.List_ID=a.List_ID+1 and num=0)
     And num<>0

--两数字连续
Select * 
From #Tmp_Tbl a
Where Exists(Select 1 From #Tmp_Tbl b where b.List_ID=a.List_ID-1 and num>0)
     And (Exists(Select 1 From #Tmp_Tbl b where b.List_ID=a.List_ID+1 and num=0) 
          or Not Exists(Select 1 From #Tmp_Tbl b where b.List_ID=a.List_ID+1))
     And num<>0 and num<3  
     

     
--三数字连续
Select * 
From #Tmp_Tbl a
Where Exists(Select 1 From #Tmp_Tbl b where b.List_ID=a.List_ID-1 and num>0)
     And Exists(Select 1 From #Tmp_Tbl b where b.List_ID=a.List_ID+1 and num>0)
     And num<>0

骑着蜗牛去爬山 2017-06-24

打赏
举报


with tb1(id,number)
as(
select 40,2 union all
select 39,1 union all
select 38,0 union all
select 37,1 union all
select 36,0 union all
select 35,0 union all
select 34,0 union all
select 33,0 union all
select 32,3 union all
select 31,2 union all
select 30,1 union all
select 29,0 union all
select 28,1 union all
select 27,0 union all
select 26,0 union all
select 25,1 union all
select 24,0 union all
select 23,0 union all
select 22,0 union all
select 21,1 union all
select 20,0 union all
select 19,1 union all
select 18,0 union all
select 17,3 union all
select 16,2 union all
select 15,1 union all
select 14,0 union all
select 13,2 union all
select 12,1 union all
select 11,0 union all
select 10,0 union all
select 09,0 union all
select 08,0
),tb as(
select _id=id,number,id=ROW_NUMBER()over(order by id asc) from tb1
),
cte as(
select top 1 *,oldnum=1 from tb order by id desc union all
select t.*,oldnum = case when c.number=t.number+1 then oldnum else oldnum+1 end from cte c join tb t on t.id=c.id-1
)
,gp as(
select count(1) as ct from cte where number !=0 group by oldnum
)
select rtrim(sum(case when ct=1 then 1 else 0 end))+'次',rtrim(sum(case when ct=2 then 1 else 0 end))+'次',rtrim(sum(case when ct=3 then 1 else 0 end))+'次' from gp

二月十六版主 2017-06-24

打赏
举报

引用 5 楼 dq9005 的回复:

引用 4 楼 sinat_28984567 的回复:
321算是连号吗？
嗯是

--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

Create table #T([Id] int,[num] int)

Insert #T

select N'40',2 union all

select N'39',1 union all

select N'38',0 union all

select N'37',1 union all

select N'36',0 union all

select N'35',0 union all

select N'34',0 union all

select N'33',0 union all

select N'32',3 union all

select N'31',2 union all

select N'30',1 union all

select N'29',0 union all

select N'28',1 union all

select N'27',0 union all

select N'26',0 union all

select N'25',1 union all

select N'24',0 union all

select N'23',0 union all

select N'22',0 union all

select N'21',1 union all

select N'20',0 union all

select N'19',1 union all

select N'18',0 union all

select N'17',3 union all

select N'16',2 union all

select N'15',1 union all

select N'14',0 union all

select N'13',2 union all

select N'12',1 union all

select N'11',0 union all

select N'10',0 union all

select N'09',0 union all

select N'08',0

Go

--测试数据结束

;WITH ctea AS (

SELECT  MAX(num) AS num ,

        id - num AS rn

FROM    #T

WHERE num<>0

GROUP BY id - num

)

SELECT  'num' + RTRIM(num) + '出现了' + RTRIM(COUNT(1)) + '次'

FROM    ctea

GROUP BY num