求一sql

[振乾]

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    睡觉

请各位赐教

[chuifengde]

DECLARE @a table( id INT IDENTITY(1,1)    PRIMARY KEY,  EventType VARCHAR(60) )
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 行）*/

[jamk]

纠正一点错误，LZ你的 INSERT INTO tb
应该改为 INSERT INTO tb(EventType)
不然报错。

[jamk]

[Quote=引用 2 楼 kongmajian 的回复:]
纠正一点错误，LZ你的 INSERT INTO tb
应该改为 INSERT INTO tb(EventType)
不然报错。
[/Quote]
当我没说。。。呵呵

[a402626926]

[Quote=引用楼主 gguozhenqian 的回复:]
SQL code



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
SELE……
[/Quote]
declare @a table(id int,eventtype varchar(60))
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

[rucypli]

厉害2楼

[whrspsoft3723]

数据如果不连续，有很多段如何处理？ 有没有考虑过

[yibey]

我差点说2楼厉害，幸亏我看到了3楼

[jimoshatan]

IF(OBJECT_ID('tb') IS NOT NULL)
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 a

select 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 行）

[jimoshatan]

IF(OBJECT_ID('tb') IS NOT NULL)
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 a

select min(id),max(id),EventType
from tb
group by s_code,EventType
order by min(id)
drop table tb

[kadboy]

Create table 表 ( id int IDENTITY(1,1) PRIMARY KEY, EventType VARCHAR(60),StartID int)
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 StartID

drop table 表

[振乾]

[Quote=引用 9 楼 jimoshatan 的回复:]

IF(OBJECT_ID('tb') IS NOT NULL)
DROP TABLE tb
CREATE TABLE tb
(
id INT IDENTITY(1,1) PRIMARY KEY,
EventType VARCHAR(60),s_code int
)
INSERT INTO tb
SELECT '睡觉','' UNI……
[/Quote]

朋友吗，帮忙 解释下这句话的：
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 方式 我不太懂。