34,588
社区成员
发帖
与我相关
我的任务
分享
IF(OBJECT_ID('cha') IS NOT NULL) DROP TABLE cha
CREATE TABLE cha
(
id INT,
VALUE INT,
msgtitle VARCHAR(50)
)
INSERT cha
SELECT 1,1,'开始' union all
SELECT 3,4,'结束' union all
SELECT 5,6,'开始' union all
SELECT 8,10,'结束' union all
SELECT 11,20,'开始' union all
SELECT 16,25,'结束' union all
SELECT 20,1,'开始' union all--此行相邻的没有 结束,所以 无效,舍弃掉
SELECT 25,30,'开始' union all
SELECT 36,34,'结束'
--查询出完整过程的详情(按照id排序,所有相邻的'开始'到'结束'为一个完整过程,不连续的舍弃)
SELECT * FROM cha
最终效果
msgtitle value msgtitle value
开始 1 结束 4
开始 6 结束 10
开始 20 结束 25
开始 30 结束 34
SELECT IDENTITY(INT,1,1) AS id, VALUE,a.msgtitle INTO #temp FROM cha a ORDER BY id
SELECT a.msgtitle,a.value,b.msgtitle,b.value FROM #temp a
LEFT JOIN #temp b ON a.id=b.id-1
WHERE ISNULL(b.id,0)!=0 AND a.msgtitle='开始' AND b.msgtitle='结束'
ORDER BY a.id
;with cte as
(
select ROW_NUMBER()over(order by id)as row,*
from cha
)
select *
from cte as t
inner join cte t1
on t.row=t1.row-1
and t.msgtitle<>t1.msgtitle
where t.msgtitle='开始'