求一个为记录分组并给一个分组号SQL

Question

 
 有一table : t1(c1,c2,c3)
 c1 c2 c3
ZA003 1 2011/8/3 16:57:50
ZA003 1 2011/8/3 16:58:31
ZA003 2 2011/8/3 16:59:12
ZA003 2 2011/8/3 16:59:53
ZA003 2 2011/8/3 17:00:34
ZA003 2 2011/8/3 17:01:15
ZA003 2 2011/8/3 17:01:56
ZA003 2 2011/8/3 17:02:38
ZA003 2 2011/8/3 17:03:19
ZA003 2 2011/8/3 17:03:19
ZA003 1 2011/8/3 17:04:00
ZA003 1 2011/8/3 17:04:41
ZA003 2 2011/8/3 17:05:22
ZA003 1 2011/8/3 17:06:03
ZA003 1 2011/8/3 17:06:44
ZA003 1 2011/8/3 17:07:25
ZA003 1 2011/8/3 17:08:06希望有一SQL,可以产生分组编号，即当c2这列跟前一笔的数值比较，当有变化时，分配新的分组编号。如下结果
c1 c2 c3 　　　　分组号
ZA003 1 2011/8/3 16:57:50 1
ZA003 1 2011/8/3 16:58:31 1
ZA003 2 2011/8/3 16:59:12 2
ZA003 2 2011/8/3 16:59:53 2
ZA003 2 2011/8/3 17:00:34 2
ZA003 2 2011/8/3 17:01:15 2
ZA003 2 2011/8/3 17:01:56 2
ZA003 2 2011/8/3 17:02:38 2
ZA003 2 2011/8/3 17:03:19 2
ZA003 2 2011/8/3 17:03:19 2
ZA003 1 2011/8/3 17:04:00 3
ZA003 1 2011/8/3 17:04:41 3
ZA003 2 2011/8/3 17:05:22 4
ZA003 1 2011/8/3 17:06:03 5
ZA003 1 2011/8/3 17:06:44 5
ZA003 1 2011/8/3 17:07:25 5
ZA003 1 2011/8/3 17:08:06 5

Answer 1

create table tb(c1 int,c2 int,c3 int)
insert into tb
select 1,1,0 union all
select 1,1,1 union all
select 1,2,2 union all
select 1,2,3 union all
select 1,2,4 union all
select 1,1,5 union all
select 1,1,6 union all
select 1,2,7 union all
select 1,1,8 union all
select 1,2,9 union all
select 1,2,10 union all
select 1,1,11 union all
select 1,1,12 union all
select 1,2,13
go;with ach as
(
    select *,px=rank() over (order by c3),
 pid=rank() over (partition by c1,c2 order by c3)
    from tb
)select c1,c2,c3,(case when px-pid=0 then 1 else px-pid end) c4
from ach
order by c3drop table tb/***********c1          c2          c3          c4
----------- ----------- ----------- --------------------
1           1           0           1
1           1           1           1
1           2           2           2
1           2           3           2
1           2           4           2
1           1           5           3
1           1           6           3
1           2           7           4
1           1           8           4
1           2           9           5
1           2           10          5
1           1           11          6
1           1           12          6
1           2           13          7(14 行受影响)

Answer 2

create table t1(c1 varchar(10),c2 int,c3 datetime)
insert into t1 select 'ZA003',1,'2011/8/3 16:57:50' union all
 select 'ZA003',1,'2011/8/3 16:57:50' union all
 select 'ZA003',2,'2011/8/3 16:57:50' union all
 select 'ZA003',2,'2011/8/3 16:57:50' union all
 select 'ZA003',1,'2011/8/3 16:57:50' union all
 select 'ZA003',1,'2011/8/3 16:57:50' 
 
 select *,null as c4 into #tb from t1
 
 declare @num1 int=0,@num2 int=0
 update #tb set c4=@num1,@num1=(case when @num2=c2 then @num1 else @num1+1 end),
                @num2=c2
 select * from #tb               
 drop table #tb                
/*
c1         c2          c3                      c4
---------- ----------- ----------------------- -----------
ZA003      1           2011-08-03 16:57:50.000 1
ZA003      1           2011-08-03 16:57:50.000 1
ZA003      2           2011-08-03 16:57:50.000 2
ZA003      2           2011-08-03 16:57:50.000 2
ZA003      1           2011-08-03 16:57:50.000 3
ZA003      1           2011-08-03 16:57:50.000 3(6 行受影响)
                

Answer 3

declare @tb table (c1 varchar(20),c2 int,c3 datetime)
insert into @tb(c1,c2,c3)
select 'ZA003', 1,'2011-8-3 16:57:50' union all
select 'ZA003', 1,'2011-8-3 16:58:31' union all
select 'ZA003', 2,'2011-8-3 16:59:12' union all
select 'ZA003', 2,'2011-8-3 16:59:53' union all
select 'ZA003', 2,'2011-8-3 17:00:34' union all
select 'ZA003', 2,'2011-8-3 17:01:15' union all
select 'ZA003', 2,'2011-8-3 17:01:56' union all
select 'ZA003', 2,'2011-8-3 17:02:38' union all
select 'ZA003', 2,'2011-8-3 17:03:19' union all
select 'ZA003', 2,'2011-8-3 17:03:19' union all
select 'ZA003', 1,'2011-8-3 17:04:00' union all
select 'ZA003', 1,'2011-8-3 17:04:41' union all
select 'ZA003', 2,'2011-8-3 17:05:22' union all
select 'ZA003', 1,'2011-8-3 17:06:03' union all
select 'ZA003', 1,'2011-8-3 17:06:44' union all
select 'ZA003', 1,'2011-8-3 17:07:25' union all
select 'ZA003', 1,'2011-8-3 17:08:06'
;
WITH CTE AS (
select ROW_NUMBER()over(order by c3) as row,c1,c2,c3 
from @tb
) ,
CTE1 AS (
SELECT A.*,ISNULL(B.c2,A.c2) AS BD 
FROM CTE A LEFT JOIN CTE B ON A.row=B.row+1
),CTE2 AS (
SELECT ROW_NUMBER()over(order by c3)GD,ROW 
FROM CTE1 WHERE C2!=BD
),CTE3 AS (
SELECT ISNULL(A.row,(SELECT MAX(ROW)+1 FROM CTE))-1 AS ROW,ISNULL(B.ROW,0) AS STAT 
FROM CTE2 A FULL JOIN CTE2 B ON A.GD=B.GD+1
)
SELECT DENSE_RANK()OVER(ORDER BY B.STAT) AS 分组号,A.c1,A.c2,A.c3 
FROM CTE1 A, CTE3 B 
WHERE A.row BETWEEN B.STAT AND B.row 
---------------------------------------
分组号                  c1                   c2          c3
-------------------- -------------------- ----------- -----------------------
1                    ZA003                1           2011-08-03 16:57:50.000
1                    ZA003                1           2011-08-03 16:58:31.000
2                    ZA003                2           2011-08-03 16:59:12.000
2                    ZA003                2           2011-08-03 16:59:53.000
2                    ZA003                2           2011-08-03 17:00:34.000
2                    ZA003                2           2011-08-03 17:01:15.000
2                    ZA003                2           2011-08-03 17:01:56.000
2                    ZA003                2           2011-08-03 17:02:38.000
2                    ZA003                2           2011-08-03 17:03:19.000
2                    ZA003                2           2011-08-03 17:03:19.000
3                    ZA003                1           2011-08-03 17:04:00.000
3                    ZA003                1           2011-08-03 17:04:41.000
4                    ZA003                2           2011-08-03 17:05:22.000
5                    ZA003                1           2011-08-03 17:06:03.000
5                    ZA003                1           2011-08-03 17:06:44.000
5                    ZA003                1           2011-08-03 17:07:25.000
5                    ZA003                1           2011-08-03 17:08:06.000(17 行受影响)

Answer 4

WITH CTE AS (
select ROW_NUMBER()over(partition by c1 order by c3) as row,c1,c2,c3 
from @tb
),CTE1 AS (
SELECT A.*,ISNULL(B.c2,A.c2) AS BD 
FROM CTE A LEFT JOIN CTE B ON A.row=B.row+1 AND A.c1=B.c1
),CTE2 AS (
SELECT ROW_NUMBER()over(partition by c1 order by c3)GD,ROW,c1
FROM CTE1 WHERE C2!=BD
),CTE3 AS (
SELECT ISNULL(A.row,(SELECT MAX(ROW)+1 FROM CTE WHERE c1=B.c1))-1 AS ROW,ISNULL(B.ROW,0) AS STAT ,ISNULL(A.c1,B.c1) AS C1
FROM CTE2 A FULL JOIN CTE2 B ON A.GD=B.GD+1 AND A.c1=B.c1
)
SELECT DENSE_RANK()OVER(partition by A.c1 ORDER BY B.STAT) AS 分组号,A.c1,A.c2,A.c3
FROM CTE1 A, CTE3 B 
WHERE A.row BETWEEN B.STAT AND B.row  AND A.c1=B.C1
6楼的SQL语句只是当表中的C1只有“ZA003”一种数据，现在的语句表中的C1字段可以有“ZA003”，“ZA004”.....多种数据，对于C2字段中的值没有现在，只是在对时间排序，C2只要有值就可以了

Answer 5

狂了一会回来想到了...哈哈..create table #tb
( c1 nvarchar(20),c2 int,c3 datetime)
insert #tb
select 'ZA003', 1 ,'2011/8/3 16:57:50' union all
select 'ZA003', 1 ,'2011/8/3 16:58:31' union all
select 'ZA003', 2 ,'2011/8/3 16:59:12' union all
select 'ZA003', 2 ,'2011/8/3 16:59:53' union all
select 'ZA003', 2 ,'2011/8/3 17:00:34' union all
select 'ZA003', 2 ,'2011/8/3 17:01:15' union all
select 'ZA003', 2 ,'2011/8/3 17:01:56' union all
select 'ZA003', 2 ,'2011/8/3 17:02:38' union all
select 'ZA003', 2 ,'2011/8/3 17:03:19' union all
select 'ZA003', 2 ,'2011/8/3 17:03:19' union all
select 'ZA003', 1 ,'2011/8/3 17:04:00' union all
select 'ZA003', 1 ,'2011/8/3 17:04:41' union all
select 'ZA003', 2 ,'2011/8/3 17:05:22' union all
select 'ZA003', 1 ,'2011/8/3 17:06:03' union all
select 'ZA003', 1 ,'2011/8/3 17:06:44' union all
select 'ZA003', 1 ,'2011/8/3 17:07:25' union all
select 'ZA003', 1 ,'2011/8/3 17:08:06';with TampA as(select Row_number()over(order by c3) as num,* from  #tb)
,TampB as (select t1.num from TampA as t1 Left join TampA as t2
        on t1.num=t2.num+1 where t1.c2<>t2.c2 and t2.c2 is not null)
select (select count(1)+1 from TampB where TampA.num>=TampB.num) as 分组号,
c1,c2,c3
from TampA--分组号         c1                   c2          c3
------------- -------------------- ----------- -----------------------
--1           ZA003                1           2011-08-03 16:57:50.000
--1           ZA003                1           2011-08-03 16:58:31.000
--2           ZA003                2           2011-08-03 16:59:12.000
--2           ZA003                2           2011-08-03 16:59:53.000
--2           ZA003                2           2011-08-03 17:00:34.000
--2           ZA003                2           2011-08-03 17:01:15.000
--2           ZA003                2           2011-08-03 17:01:56.000
--2           ZA003                2           2011-08-03 17:02:38.000
--2           ZA003                2           2011-08-03 17:03:19.000
--2           ZA003                2           2011-08-03 17:03:19.000
--3           ZA003                1           2011-08-03 17:04:00.000
--3           ZA003                1           2011-08-03 17:04:41.000
--4           ZA003                2           2011-08-03 17:05:22.000
--5           ZA003                1           2011-08-03 17:06:03.000
--5           ZA003                1           2011-08-03 17:06:44.000
--5           ZA003                1           2011-08-03 17:07:25.000
--5           ZA003                1           2011-08-03 17:08:06.000

Answer 6

chtzhking哥提醒了弟..对..要分组的..哈哈..create table #tb
( c1 nvarchar(20),c2 int,c3 datetime)
insert #tb
select 'ZA003', 1 ,'2011/8/3 16:57:50' union all
select 'ZA003', 1 ,'2011/8/3 16:58:31' union all
select 'ZA003', 2 ,'2011/8/3 16:59:12' union all
select 'ZA003', 2 ,'2011/8/3 16:59:53' union all
select 'ZA003', 2 ,'2011/8/3 17:00:34' union all
select 'ZA003', 2 ,'2011/8/3 17:01:15' union all
select 'ZA003', 2 ,'2011/8/3 17:01:56' union all
select 'ZA003', 2 ,'2011/8/3 17:02:38' union all
select 'ZA003', 2 ,'2011/8/3 17:03:19' union all
select 'ZA003', 2 ,'2011/8/3 17:03:19' union all
select 'ZA003', 1 ,'2011/8/3 17:04:00' union all
select 'ZA003', 1 ,'2011/8/3 17:04:41' union all
select 'ZA003', 2 ,'2011/8/3 17:05:22' union all
select 'ZA003', 1 ,'2011/8/3 17:06:03' union all
select 'ZA003', 1 ,'2011/8/3 17:06:44' union all
select 'ZA003', 1 ,'2011/8/3 17:07:25' union all
select 'ZA003', 1 ,'2011/8/3 17:08:06' union allselect 'BZA003', 1 ,'2011/8/3 16:57:50' union all
select 'BZA003', 1 ,'2011/8/3 16:58:31' union all
select 'BZA003', 3 ,'2011/8/3 16:59:12' union all
select 'BZA003', 4 ,'2011/8/3 16:59:53' union all
select 'BZA003', 2 ,'2011/8/3 17:00:34' union all
select 'BZA003', 2 ,'2011/8/3 17:01:15' union all
select 'BZA003', 2 ,'2011/8/3 17:01:56' union all
select 'BZA003', 2 ,'2011/8/3 17:02:38' union all
select 'BZA003', 1 ,'2011/8/3 17:03:19' union all
select 'BZA003', 1 ,'2011/8/3 17:03:19' union all
select 'BZA003', 1 ,'2011/8/3 17:04:00' union all
select 'BZA003', 2 ,'2011/8/3 17:04:41' union all
select 'BZA003', 2 ,'2011/8/3 17:05:22' union all
select 'BZA003', 3 ,'2011/8/3 17:06:03' union all
select 'BZA003', 3 ,'2011/8/3 17:06:44' union all
select 'BZA003', 3 ,'2011/8/3 17:07:25' union all
select 'BZA003', 4 ,'2011/8/3 17:08:06';with TampA as(select Row_number()over(partition by c1 order by c3) as num,* from  #tb)
,TampB as (select t1.c1,t1.num from TampA as t1 Left join TampA as t2
        on t1.c1=t2.c1 and t1.num=t2.num+1 where t1.c2<>t2.c2 and t2.c2 is not null)
select (select count(1)+1 from TampB where TampA.c1=TampB.c1 and TampA.num>=TampB.num) as 分组号,
c1,c2,c3
from TampA--分组号         c1                   c2          c3
------------- -------------------- ----------- -----------------------
--1           BZA003               1           2011-08-03 16:57:50.000
--1           BZA003               1           2011-08-03 16:58:31.000
--2           BZA003               3           2011-08-03 16:59:12.000
--3           BZA003               4           2011-08-03 16:59:53.000
--4           BZA003               2           2011-08-03 17:00:34.000
--4           BZA003               2           2011-08-03 17:01:15.000
--4           BZA003               2           2011-08-03 17:01:56.000
--4           BZA003               2           2011-08-03 17:02:38.000
--5           BZA003               1           2011-08-03 17:03:19.000
--5           BZA003               1           2011-08-03 17:03:19.000
--5           BZA003               1           2011-08-03 17:04:00.000
--6           BZA003               2           2011-08-03 17:04:41.000
--6           BZA003               2           2011-08-03 17:05:22.000
--7           BZA003               3           2011-08-03 17:06:03.000
--7           BZA003               3           2011-08-03 17:06:44.000
--7           BZA003               3           2011-08-03 17:07:25.000
--8           BZA003               4           2011-08-03 17:08:06.000
--1           ZA003                1           2011-08-03 16:57:50.000
--1           ZA003                1           2011-08-03 16:58:31.000
--2           ZA003                2           2011-08-03 16:59:12.000
--2           ZA003                2           2011-08-03 16:59:53.000
--2           ZA003                2           2011-08-03 17:00:34.000
--2           ZA003                2           2011-08-03 17:01:15.000
--2           ZA003                2           2011-08-03 17:01:56.000
--2           ZA003                2           2011-08-03 17:02:38.000
--2           ZA003                2           2011-08-03 17:03:19.000
--2           ZA003                2           2011-08-03 17:03:19.000
--3           ZA003                1           2011-08-03 17:04:00.000
--3           ZA003                1           2011-08-03 17:04:41.000
--4           ZA003                2           2011-08-03 17:05:22.000
--5           ZA003                1           2011-08-03 17:06:03.000
--5           ZA003                1           2011-08-03 17:06:44.000
--5           ZA003                1           2011-08-03 17:07:25.000
--5           ZA003                1           2011-08-03 17:08:06.000

Answer 7

DECLARE @tb TABLE (c1 varchar(20),c2 int,c3 datetime)
insert into @tb(c1,c2,c3)
select 'ZA003', 1,'2011-8-3 16:57:50' union all
select 'ZA003', 1,'2011-8-3 16:58:31' union all
select 'ZA003', 2,'2011-8-3 16:59:12' union all
select 'ZA003', 2,'2011-8-3 16:59:53' union all
select 'ZA003', 2,'2011-8-3 17:00:34' union all
select 'ZA003', 2,'2011-8-3 17:01:15' union all
select 'ZA003', 2,'2011-8-3 17:01:56' union all
select 'ZA003', 2,'2011-8-3 17:02:38' union all
select 'ZA003', 2,'2011-8-3 17:03:19' union all
select 'ZA003', 2,'2011-8-3 17:03:19' union all
select 'ZA003', 1,'2011-8-3 17:04:00' union all
select 'ZA003', 1,'2011-8-3 17:04:41' union all
select 'ZA003', 2,'2011-8-3 17:05:22' union all
select 'ZA003', 1,'2011-8-3 17:06:03' union all
select 'ZA003', 1,'2011-8-3 17:06:44' union all
select 'ZA003', 1,'2011-8-3 17:07:25' union all
select 'ZA003', 1,'2011-8-3 17:08:06'--SQL:
;WITH cte AS
(
SELECT
rowno=ROW_NUMBER() OVER(ORDER BY c3),
groupno=ROW_NUMBER() OVER(PARTITION BY c1, c2 ORDER BY c3),
*
FROM @tb
),
cte_2 AS
(
SELECT
rowno2=ROW_NUMBER() OVER(ORDER BY MAX(ROWNO)),
c1,
c2, 
groupid=rowno-groupno
FROM cte
GROUP BY c1, c2, rowno-groupno
)
SELECT 
a.c1, a.c2, a.c3, b.rowno2
FROM (SELECT groupid=rowno-groupno, * FROM cte) a
inner join cte_2 b
on a.c1 = b.c1 AND a.c2 = b.c2 AND a.groupid = b.groupid
ORDER BY b.rowno2
/*
c1 c2 c3 rowno2
ZA003 1 2011-08-03 16:57:50.000 1
ZA003 1 2011-08-03 16:58:31.000 1
ZA003 2 2011-08-03 16:59:12.000 2
ZA003 2 2011-08-03 16:59:53.000 2
ZA003 2 2011-08-03 17:00:34.000 2
ZA003 2 2011-08-03 17:01:15.000 2
ZA003 2 2011-08-03 17:01:56.000 2
ZA003 2 2011-08-03 17:02:38.000 2
ZA003 2 2011-08-03 17:03:19.000 2
ZA003 2 2011-08-03 17:03:19.000 2
ZA003 1 2011-08-03 17:04:00.000 3
ZA003 1 2011-08-03 17:04:41.000 3
ZA003 2 2011-08-03 17:05:22.000 4
ZA003 1 2011-08-03 17:06:03.000 5
ZA003 1 2011-08-03 17:06:44.000 5
ZA003 1 2011-08-03 17:07:25.000 5
ZA003 1 2011-08-03 17:08:06.000 5
*/