求表中每行数的断点数,求教大侠们?

zongliang2015 2015-10-08 12:57:47
如下表举例:
a1 a2 a3 a4 a5 a6 a7 a8 a9 abreak(断点数)
0 0 0 0 1 1 1 2 2 2
0 0 0 0 1 1 2 1 2 4
0 0 0 0 1 1 2 2 1 3
0 0 0 0 1 1 2 2 2 2
0 0 0 0 1 2 1 1 2 4
0 0 0 0 1 2 1 2 1 ?
0 0 0 0 1 2 1 2 2 ?
0 0 0 0 1 2 3 1 1 ?
举例第一行数据,从左往右看0000变为1,第一个不相同,断点数为1,由111往右遇到2,不相同,断点数为2,则本行断点为2;
第二行数据,从左往右看0000变为1,第一个不相同,断点数为1,由11往右变2,断点数为2,由2往右变1,断点数为3,由1变2,断点数为4;
依次类推。
求其他值得断点数。
...全文
101 点赞 收藏 4
写回复
4 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
Tiger_Zhao 2015-10-08
你要统计的是变化次数,所以和取值范围是没有关系的。
回复
zongliang2015 2015-10-08
忘了说了,假设就只有0,1,2三个类型的数据。是否可以有简单的解法。
回复
Tiger_Zhao 2015-10-08
直接可以算的,用不着行列转换
/* 测试数据
WITH table1(a1,a2,a3,a4,a5,a6,a7,a8,a9)AS(
SELECT 0,0,0,0,1,1,1,2,2 UNION ALL
SELECT 0,0,0,0,1,1,2,1,2 UNION ALL
SELECT 0,0,0,0,1,1,2,2,1 UNION ALL
SELECT 0,0,0,0,1,1,2,2,2 UNION ALL
SELECT 0,0,0,0,1,2,1,1,2 UNION ALL
SELECT 0,0,0,0,1,2,1,2,1 UNION ALL
SELECT 0,0,0,0,1,2,1,2,2 UNION ALL
SELECT 0,0,0,0,1,2,3,1,1
)*/
SELECT *,
SIGN(ABS(a1-a2))+
SIGN(ABS(a2-a3))+
SIGN(ABS(a3-a4))+
SIGN(ABS(a4-a5))+
SIGN(ABS(a5-a6))+
SIGN(ABS(a6-a7))+
SIGN(ABS(a7-a8))+
SIGN(ABS(a8-a9)) abreak
FROM table1

         a1          a2          a3          a4          a5          a6          a7          a8          a9      abreak
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
0 0 0 0 1 1 1 2 2 2
0 0 0 0 1 1 2 1 2 4
0 0 0 0 1 1 2 2 1 3
0 0 0 0 1 1 2 2 2 2
0 0 0 0 1 2 1 1 2 4
0 0 0 0 1 2 1 2 1 5
0 0 0 0 1 2 1 2 2 4
0 0 0 0 1 2 3 1 1 4
回复
qq_17482963 2015-10-08
select * into #t from(
select 0 a1,0 a2,0 a3,0 a4,1 a5,1 a6,1 a7,2 a8,2 a9 union all
select 0 ,0 ,0 ,0 ,1 ,1 ,2 ,1 ,2   union all
select 0 ,0 ,0 ,0 ,1 ,1 ,2 ,2 ,1   union all
select 0 ,0 ,0 ,0 ,1 ,1 ,2 ,2 ,2   union all
select 0 ,0 ,0 ,0 ,1 ,2 ,1 ,1 ,2   union all
select 0 ,0 ,0 ,0 ,1 ,2 ,1 ,2 ,1   union all
select 0 ,0 ,0 ,0 ,1 ,2 ,1 ,2 ,2   union all
select 0 ,0 ,0 ,0 ,1 ,2 ,3 ,1 ,1 ) a

;with tb as
(
	select ROW_NUMBER()over(order by getdate())id,* from #t
),
tb1 as
(
	select *,ROW_NUMBER()over(PARTITION by id order by getdate())rn from tb p unpivot(num for name in (a1,a2,a3,a4,a5,a6,a7,a8,a9))t
)
select a.*,b.[abreak] from tb a inner join 
(
select a.id,sum(case when a.num=b.num then 0 else 1 end)[abreak] from tb1 a,tb1 b where a.id=b.id and a.rn+1=b.rn group by a.id
) b on a.id=b.id

drop table #t
id                   a1          a2          a3          a4          a5          a6          a7          a8          a9          abreak
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1                    0           0           0           0           1           1           1           2           2           2
2                    0           0           0           0           1           1           2           1           2           4
3                    0           0           0           0           1           1           2           2           1           3
4                    0           0           0           0           1           1           2           2           2           2
5                    0           0           0           0           1           2           1           1           2           4
6                    0           0           0           0           1           2           1           2           1           5
7                    0           0           0           0           1           2           1           2           2           4
8                    0           0           0           0           1           2           3           1           1           4
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-10-08 12:57
社区公告
暂无公告