如下數據：
A
-----------------------------
1
1
1
2
2
3
3
5
5
1
2
2
需要查詢的結果為6, 即連續相同的字符統計為1, 最後合計為6
------解决思路----------------------
with table1 as
(
select 1 id, 1 col1 union all
select 2 id, 1 col1 union all
select 3 id, 1 col1 union all
select 4 id, 2 col1 union all
select 5 id, 2 col1 union all
select 6 id, 3 col1 union all
select 7 id, 3 col1 union all
select 8 id, 5 col1 union all
select 9 id, 5 col1 union all
select 10 id, 1 col1 union all
select 11 id, 2 col1 union all
select 12 id, 2 col1
)
select SUM(case when b.id is null then 1 else 0 end)
from (select *,ROW_NUMBER() over(order by id) id2 from table1)a
left join (select *,ROW_NUMBER() over(order by id) id2 from table1)b on a.id2=b.id2-1 and a.col1=b.col1
------解决思路----------------------
SELECT TOP 1 A,COUNT(A)COUNTS FROM tb GROUP BY A ORDER BY COUNTS desc
SELECT TOP 1 COUNT(A)COUNTS FROM tb GROUP BY A ORDER BY COUNTS DESC
------解决思路----------------------
create table test(A varchar(10))
go
insert into test(A) values
(1),(1),(1),
(2),(2),
(3),(3),(3),
(5),(5),
(6),
(5),
(1),
(2),(2)
go
select * from test
go
with m as
(
select
ROW_NUMBER() over(order by getdate()) rn , A from test
) ,
mt as (
select m.* , 1 as tag from m where rn = 1
union all
select m.* , case when m.A <> mt.A then 1 else 0 end
from m , mt where m.rn = mt.rn + 1
)
select sum(tag)
from mt
go
drop table test
go
(15 行受影响)
A
----------
1
1
1
2
2
3
3
3
5
5
6
5
1
2
2
(15 行受影响)
-----------
8
(1 行受影响)