我有表test,结构及数据如下:
id rqsj
200907 2009/8/2 2:00:00
200907 2009/8/2 8:00:00
200907 2009/8/2 20:00:00
200907 2009/8/3 2:00:00
200907 2009/8/3 8:00:00
200907 2009/8/3 14:00:00
200907 2009/8/3 20:00:00
200907 2009/8/3 23:00:00
200907 2009/8/4 1:00:00
200907 2009/8/4 2:00:00
200907 2009/8/4 3:00:00
200907 2009/8/4 4:00:00
200907 2009/8/4 5:00:00
200907 2009/8/4 6:00:00
200907 2009/8/4 7:00:00
200907 2009/8/4 8:00:00
200907 2009/8/4 9:00:00
想实现如下功能,在查询的时候能添加一列status用于标识,
标识规则:当rqsj为某一天的第一个数据时,将status标识为1,当天其他数据表示为0
以上已在http://topic.csdn.net/u/20090923/14/90b1beeb-ffd0-41b5-8eb7-1c0a36c8e3dd.html?seed=1783998784&r=60005525#r_60005525该贴中实现;(不过要外加一个order by rqsj的时间排序)
现在想再在上面加一个逻辑,就是两个标识了1之间的数据(通过rqsj排序了的),每隔2条再做一个标识,status中表示2,(数据不足3条时不做该状态处理)
还有就是该组时间的最后一条也标识1
处理后的格式:
id rqsj status
200907 2009/8/2 2:00:00 1
200907 2009/8/2 8:00:00 0
200907 2009/8/2 20:00:00 0
200907 2009/8/3 2:00:00 1
200907 2009/8/3 8:00:00 0
200907 2009/8/3 14:00:00 0
200907 2009/8/3 20:00:00 2
200907 2009/8/3 23:00:00 0
200907 2009/8/4 1:00:00 1
200907 2009/8/4 2:00:00 0
200907 2009/8/4 3:00:00 0
200907 2009/8/4 4:00:00 2
200907 2009/8/4 5:00:00 0
200907 2009/8/4 6:00:00 0
200907 2009/8/4 7:00:00 2
200907 2009/8/4 8:00:00 0
200907 2009/8/4 9:00:00 1
希望高手们继续帮忙,谢谢了
id rqsj
200907 2009/8/2 2:00:00
200907 2009/8/2 8:00:00
200907 2009/8/2 20:00:00
200907 2009/8/3 2:00:00
200907 2009/8/3 8:00:00
200907 2009/8/3 14:00:00
200907 2009/8/3 20:00:00
200907 2009/8/3 23:00:00
200907 2009/8/4 1:00:00
200907 2009/8/4 2:00:00
200907 2009/8/4 3:00:00
200907 2009/8/4 4:00:00
200907 2009/8/4 5:00:00
200907 2009/8/4 6:00:00
200907 2009/8/4 7:00:00
200907 2009/8/4 8:00:00
200907 2009/8/4 9:00:00
想实现如下功能,在查询的时候能添加一列status用于标识,
标识规则:当rqsj为某一天的第一个数据时,将status标识为1,当天其他数据表示为0
以上已在http://topic.csdn.net/u/20090923/14/90b1beeb-ffd0-41b5-8eb7-1c0a36c8e3dd.html?seed=1783998784&r=60005525#r_60005525该贴中实现;(不过要外加一个order by rqsj的时间排序)
现在想再在上面加一个逻辑,就是两个标识了1之间的数据(通过rqsj排序了的),每隔2条再做一个标识,status中表示2,(数据不足3条时不做该状态处理)
还有就是该组时间的最后一条也标识1
处理后的格式:
id rqsj status
200907 2009/8/2 2:00:00 1
200907 2009/8/2 8:00:00 0
200907 2009/8/2 20:00:00 0
200907 2009/8/3 2:00:00 1
200907 2009/8/3 8:00:00 0
200907 2009/8/3 14:00:00 0
200907 2009/8/3 20:00:00 2
200907 2009/8/3 23:00:00 0
200907 2009/8/4 1:00:00 1
200907 2009/8/4 2:00:00 0
200907 2009/8/4 3:00:00 0
200907 2009/8/4 4:00:00 2
200907 2009/8/4 5:00:00 0
200907 2009/8/4 6:00:00 0
200907 2009/8/4 7:00:00 2
200907 2009/8/4 8:00:00 0
200907 2009/8/4 9:00:00 1
希望高手们继续帮忙,谢谢了
这个需求看错了,不好意思,上面不对.
insert into tb values('2009/8/2 2:00:00')
insert into tb values('2009/8/2 8:00:00')
insert into tb values('2009/8/2 20:00:00')
insert into tb values('2009/8/3 2:00:00')
insert into tb values('2009/8/3 8:00:00')
insert into tb values('2009/8/3 14:00:00')
insert into tb values('2009/8/3 20:00:00')
insert into tb values('2009/8/3 23:00:00')
insert into tb values('2009/8/4 1:00:00')
insert into tb values('2009/8/4 2:00:00')
insert into tb values('2009/8/4 3:00:00')
insert into tb values('2009/8/4 4:00:00')
insert into tb values('2009/8/4 5:00:00')
insert into tb values('2009/8/4 6:00:00')
insert into tb values('2009/8/4 7:00:00')
insert into tb values('2009/8/4 8:00:00')
insert into tb values('2009/8/4 9:00:00')
goselect rq ,
case when px = 1 then 1
when (px - 1) % 3 = 0 then 2
else 0
end status
from
(
select * , px = (select count(1) from tb where convert(varchar(10),rq,120) = convert(varchar(10),t.rq,120) and rq < t.rq) + 1 from tb t
) mdrop table tb/*
rq status
------------------------------------------------------ -----------
2009-08-02 02:00:00.000 1
2009-08-02 08:00:00.000 0
2009-08-02 20:00:00.000 0
2009-08-03 02:00:00.000 1
2009-08-03 08:00:00.000 0
2009-08-03 14:00:00.000 0
2009-08-03 20:00:00.000 2
2009-08-03 23:00:00.000 0
2009-08-04 01:00:00.000 1
2009-08-04 02:00:00.000 0
2009-08-04 03:00:00.000 0
2009-08-04 04:00:00.000 2
2009-08-04 05:00:00.000 0
2009-08-04 06:00:00.000 0
2009-08-04 07:00:00.000 2
2009-08-04 08:00:00.000 0
2009-08-04 09:00:00.000 0(所影响的行数为 17 行)*/
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================
set nocount on
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( a datetime)
go
insert into tb values('2009/8/2 2:00:00')
insert into tb values('2009/8/2 8:00:00')
insert into tb values('2009/8/2 20:00:00')
insert into tb values('2009/8/3 2:00:00')
insert into tb values('2009/8/3 8:00:00')
insert into tb values('2009/8/3 14:00:00')
insert into tb values('2009/8/3 20:00:00')
insert into tb values('2009/8/3 23:00:00')
insert into tb values('2009/8/4 1:00:00')
insert into tb values('2009/8/4 2:00:00')
insert into tb values('2009/8/4 3:00:00')
insert into tb values('2009/8/4 4:00:00')
insert into tb values('2009/8/4 5:00:00')
insert into tb values('2009/8/4 6:00:00')
insert into tb values('2009/8/4 7:00:00')
insert into tb values('2009/8/4 8:00:00')
insert into tb values('2009/8/4 9:00:00')
go select identity(int,1,1) as id,case when ID%3=1 then 1 else 0 end as 编号,a into #k
from (select * ,ID=(select COUNT(*) from tb where DATEDIFF(DAY,h.a,a)=0 and h.a>=a ) from tb h) k
--drop table #k
update a
set 编号=(select COUNT(*) from #k where DATEDIFF(DAY,a,a.a)=0 and ID<=a.id and 编号=1)
from #k a
where 编号=1
select 编号,a from #K
/*
编号 a
----------- -----------------------
1 2009-08-02 02:00:00.000
0 2009-08-02 08:00:00.000
0 2009-08-02 20:00:00.000
1 2009-08-03 02:00:00.000
0 2009-08-03 08:00:00.000
0 2009-08-03 14:00:00.000
2 2009-08-03 20:00:00.000
0 2009-08-03 23:00:00.000
1 2009-08-04 01:00:00.000
0 2009-08-04 02:00:00.000
0 2009-08-04 03:00:00.000
2 2009-08-04 04:00:00.000
0 2009-08-04 05:00:00.000
0 2009-08-04 06:00:00.000
3 2009-08-04 07:00:00.000
0 2009-08-04 08:00:00.000
0 2009-08-04 09:00:00.000
*/
insert into tb values('2009/8/2 2:00:00')
insert into tb values('2009/8/2 8:00:00')
insert into tb values('2009/8/2 20:00:00')
insert into tb values('2009/8/3 2:00:00')
insert into tb values('2009/8/3 8:00:00')
insert into tb values('2009/8/3 14:00:00')
insert into tb values('2009/8/3 20:00:00')
insert into tb values('2009/8/3 23:00:00')
insert into tb values('2009/8/4 1:00:00')
insert into tb values('2009/8/4 2:00:00')
insert into tb values('2009/8/4 3:00:00')
insert into tb values('2009/8/4 4:00:00')
insert into tb values('2009/8/4 5:00:00')
insert into tb values('2009/8/4 6:00:00')
insert into tb values('2009/8/4 7:00:00')
insert into tb values('2009/8/4 8:00:00')
insert into tb values('2009/8/4 9:00:00')
go select rq ,
case when px = 1 then 1
when rq = (select max(rq) from tb where convert(varchar(10),rq,120) = convert(varchar(10),m.rq,120) ) then 1
when (px - 1) % 3 = 0 then 2
else 0
end status
from
(
select * , px = (select count(1) from tb where convert(varchar(10),rq,120) = convert(varchar(10),t.rq,120) and rq < t.rq) + 1 from tb t
) m
drop table tb /*
rq status
------------------------------------------------------ -----------
2009-08-02 02:00:00.000 1
2009-08-02 08:00:00.000 0
2009-08-02 20:00:00.000 1
2009-08-03 02:00:00.000 1
2009-08-03 08:00:00.000 0
2009-08-03 14:00:00.000 0
2009-08-03 20:00:00.000 2
2009-08-03 23:00:00.000 1
2009-08-04 01:00:00.000 1
2009-08-04 02:00:00.000 0
2009-08-04 03:00:00.000 0
2009-08-04 04:00:00.000 2
2009-08-04 05:00:00.000 0
2009-08-04 06:00:00.000 0
2009-08-04 07:00:00.000 2
2009-08-04 08:00:00.000 0
2009-08-04 09:00:00.000 1(所影响的行数为 17 行)*/
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================
set nocount on
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( a datetime)
go
insert into tb values('2009/8/2 2:00:00')
insert into tb values('2009/8/2 8:00:00')
insert into tb values('2009/8/2 20:00:00')
insert into tb values('2009/8/3 2:00:00')
insert into tb values('2009/8/3 8:00:00')
insert into tb values('2009/8/3 14:00:00')
insert into tb values('2009/8/3 20:00:00')
insert into tb values('2009/8/3 23:00:00')
insert into tb values('2009/8/4 1:00:00')
insert into tb values('2009/8/4 2:00:00')
insert into tb values('2009/8/4 3:00:00')
insert into tb values('2009/8/4 4:00:00')
insert into tb values('2009/8/4 5:00:00')
insert into tb values('2009/8/4 6:00:00')
insert into tb values('2009/8/4 7:00:00')
insert into tb values('2009/8/4 8:00:00')
insert into tb values('2009/8/4 9:00:00')
go select identity(int,1,1) as id,case when ID%3=1 then 1 else 0 end as 编号,a into #p
from (select * ,ID=(select COUNT(*) from tb where DATEDIFF(DAY,h.a,a)=0 and h.a>=a ) from tb h) k
--drop table #p select * from #p
select ID,
case when ID<>(select MAX(ID) from #p ) and 编号=1 and not exists(select * from #p where DATEDIFF(DAY,h.a,a)=0 and ID<h.id ) then 1
when ID<>(select MAX(ID) from #p ) and 编号=1 and exists(select * from #p where DATEDIFF(DAY,h.a,a)=0 and ID<h.id ) then 2
when ID<>(select MAX(ID) from #p ) and 编号=0 then 0
when ID=(select MAX(ID) from #p ) then 1
end as 编号, a
from #p h
/*
ID 编号 a
----------- ----------- -----------------------
1 1 2009-08-02 02:00:00.000
2 0 2009-08-02 08:00:00.000
3 0 2009-08-02 20:00:00.000
4 1 2009-08-03 02:00:00.000
5 0 2009-08-03 08:00:00.000
6 0 2009-08-03 14:00:00.000
7 2 2009-08-03 20:00:00.000
8 0 2009-08-03 23:00:00.000
9 1 2009-08-04 01:00:00.000
10 0 2009-08-04 02:00:00.000
11 0 2009-08-04 03:00:00.000
12 2 2009-08-04 04:00:00.000
13 0 2009-08-04 05:00:00.000
14 0 2009-08-04 06:00:00.000
15 2 2009-08-04 07:00:00.000
16 0 2009-08-04 08:00:00.000
17 1 2009-08-04 09:00:00.000*/
select * , px = (select count(1) from tb where convert(varchar(10),rq,120) = convert(varchar(10),t.rq,120) and rq < t.rq) + 1 from tb t 放进临时表,然后对临时表进行操作.