22,209
社区成员
发帖
与我相关
我的任务
分享
with t as (
select '王五' as name
union all select '王五'
union all select '王五'
union all select '李三'
union all select '王五'
union all select '赵四'
union all select '田强'
union all select '田强'
union all select '李三'
),t1 as (
select *,ROW_NUMBER() over(order by @@rowcount) as sn from t
)
select a.*,(case when a.name=b.name then 1 else 0 end) as 连续
from t1 a
left join t1 b on a.sn=b.sn+1
where a.name<>isnull(b.name,'')
其实很简单,你可以把最后的where去掉,看看得到的结果
CREATE TABLE #T (NAME VARCHAR(10))
INSERT INTO #T
SELECT 'A' UNION ALL
SELECT 'A' UNION ALL
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'A' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
SELECT 'D' UNION ALL
SELECT 'A' UNION ALL
SELECT 'A' UNION ALL
SELECT 'B'
--标记行号
SELECT *,ROW_NUMBER() OVER(ORDER BY (SELECT 1)) RN INTO #T1 FROM #T
DECLARE @NUM INT = 1
--遍历原表
WHILE @NUM <= (SELECT MAX(RN) FROM #T1)
BEGIN
--第一次取值时创建并插入临时表
IF object_id('tempdb..#T2') IS NULL
BEGIN
SELECT * INTO #T2 FROM #T1 WHERE RN = 1
CONTINUE
END
--获取的原表的值与临时表最后一行的数据对比,不同则插入
IF (SELECT NAME FROM #T1 WHERE RN = @NUM) != (SELECT TOP 1 NAME FROM #T2 ORDER BY RN DESC)
BEGIN
INSERT INTO #T2 SELECT * FROM #T1 WHERE RN = @NUM
END
SET @NUM = @NUM +1
END
SELECT * FROM #T2 ORDER BY RN
DROP TABLE #T,#T1,#T2
用游标好像也是这样,但是比较这样比较麻烦,不如直接group by表内行号减去组内行号的值
CREATE TABLE #T (NAME VARCHAR(10))
INSERT INTO #T
SELECT 'A' UNION ALL
SELECT 'A' UNION ALL
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'A' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
SELECT 'D' UNION ALL
SELECT 'A' UNION ALL
SELECT 'A' UNION ALL
SELECT 'B'
WITH CTE
AS
(SELECT *,
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS SEQ_1
FROM #T)
SELECT NAME
FROM
(SELECT *,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY SEQ_1) AS SEQ_2 FROM CTE) AS A
GROUP BY NAME,SEQ_1-SEQ_2
ORDER BY MIN(SEQ_1)