# 求表中每行数的断点数，求教大侠们？

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 ?

...全文
101 点赞 收藏 4

4 条回复

Tiger_Zhao 2015-10-08

zongliang2015 2015-10-08

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``````

2.1w+

MS-SQL Server 疑难问题

2015-10-08 12:57