IF(OBJECT_ID('tb') IS NOT NULL)
DROP TABLE tb
CREATE TABLE tb
(
id INT IDENTITY(1,1) PRIMARY KEY,
EventType VARCHAR(60)
)
INSERT INTO tb
SELECT '睡觉' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '吃饭' UNION ALL
SELECT '吃饭' UNION ALL
SELECT '吃饭' UNION ALL
SELECT '吃饭' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '睡觉' SELECT* FROM tb
--将连续的相同的 EventType 放在一起
--最后展现效果
--StartID EndID EventType
-- 1 5 睡觉
-- 6 9 吃饭
-- 10 14 睡觉
请各位赐教
INSERT INTO @a
SELECT '睡觉' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '睡觉1' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '吃饭' UNION ALL
SELECT '吃饭' UNION ALL
SELECT '吃饭' UNION ALL
SELECT '吃饭' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '睡觉' SELECT MIN(id)s,id1-1 e,EventType
FROM(
SELECT id,EventType,id1=isnull(( SELECT min(id) FROM @a WHERE id>a.id AND EventType<>a.EventType ),(SELECT MAX(id)+1 FROM @a))
FROM @a a
)aa GROUP BY id1,EventType
ORDER BY 1,2,3
--result
/*s e EventType
----------- ----------- ------------------------------------------------------------
1 2 睡觉
3 3 睡觉1
4 5 睡觉
6 9 吃饭
10 14 睡觉(所影响的行数为 5 行)*/
应该改为 INSERT INTO tb(EventType)
不然报错。
insert into @a select * from tb
SELECT MIN(id)s,id1-1 e,EventType
FROM(
SELECT id,EventType,id1=isnull(( SELECT min(id) FROM @a WHERE id>a.id AND EventType<>a.EventType ),(SELECT MAX(id)+1 FROM @a))
FROM @a a
)aa GROUP BY id1,EventType
ORDER BY 1,2,3
DROP TABLE tb
CREATE TABLE tb
(
id INT IDENTITY(1,1) PRIMARY KEY,
EventType VARCHAR(60),s_code int
)
INSERT INTO tb
SELECT '睡觉','' UNION ALL
SELECT '睡觉','' UNION ALL
SELECT '睡觉','' UNION ALL
SELECT '睡觉','' UNION ALL
SELECT '睡觉','' UNION ALL
SELECT '吃饭','' UNION ALL
SELECT '吃饭','' UNION ALL
SELECT '吃饭','' UNION ALL
SELECT '吃饭','' UNION ALL
SELECT '睡觉','' UNION ALL
SELECT '睡觉','' UNION ALL
SELECT '睡觉','' UNION ALL
SELECT '睡觉','' UNION ALL
SELECT '睡觉',''
declare @num int
declare @code varchar(10)
set @num=1
set @code=''
update tb
set s_code=@num,@num=(case when a.EventType=@code then @num else @num+1 end),@code=a.EventType
from tb aselect min(id),max(id),EventType
from tb
group by s_code,EventType
order by min(id)
drop table tb
(所影响的行数为 13 行)
(所影响的行数为 13 行) EventType
----------- ----------- ------------------------------------------------------------
1 5 睡觉
6 9 吃饭
10 13 睡觉(所影响的行数为 3 行)
DROP TABLE tb
CREATE TABLE tb
(
id INT IDENTITY(1,1) PRIMARY KEY,
EventType VARCHAR(60),s_code int
)
INSERT INTO tb
SELECT '睡觉','' UNION ALL
SELECT '睡觉','' UNION ALL
SELECT '睡觉','' UNION ALL
SELECT '睡觉','' UNION ALL
SELECT '睡觉','' UNION ALL
SELECT '吃饭','' UNION ALL
SELECT '吃饭','' UNION ALL
SELECT '吃饭','' UNION ALL
SELECT '吃饭','' UNION ALL
SELECT '睡觉','' UNION ALL
SELECT '睡觉','' UNION ALL
SELECT '睡觉','' UNION ALL
SELECT '睡觉',''
declare @num int
declare @code varchar(10)
set @num=1
set @code=''
update tb
set s_code=@num,@num=(case when a.EventType=@code then @num else @num+1 end),@code=a.EventType
from tb aselect min(id),max(id),EventType
from tb
group by s_code,EventType
order by min(id)
drop table tb
INSERT INTO 表(EventType)
SELECT '睡觉' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '吃饭' UNION ALL
SELECT '吃饭' UNION ALL
SELECT '吃饭' UNION ALL
SELECT '吃饭' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '睡觉' UNION ALL
SELECT '睡觉' declare @BeginID int,@LastType VARCHAR(60)
set @BeginID=-1
set @LastType=''update 表
set @BeginID=StartID=case EventType when @LastType then @BeginID else id end,
@LastType=EventType
select EventType,min(id) 'StartID',max(id) 'EndID' from 表 group by StartID,EventType order by StartIDdrop table 表
朋友吗,帮忙 解释下这句话的:
declare @num int
declare @code varchar(10)
set @num=0
set @code=''update tb
set s_code=@num,@num=(case when a.EventType=@code then @num else @num+1 end),@code=a.EventType
from tb a这里的update 方式 我不太懂。