我有表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
想实现如下功能,在查询的时候能添加一列status用于标识,
标识规则:当rqsj为某一天的第一个数据时,将status标识为1,当天其他数据表示为0
处理后的格式:
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 0
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 0
200907 2009/8/4 5:00:00 0
哪位高手能帮帮忙,谢谢
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
想实现如下功能,在查询的时候能添加一列status用于标识,
标识规则:当rqsj为某一天的第一个数据时,将status标识为1,当天其他数据表示为0
处理后的格式:
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 0
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 0
200907 2009/8/4 5:00:00 0
哪位高手能帮帮忙,谢谢
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-09-23 14:07:31
-------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (id int,rqsj datetime)
INSERT INTO @T
SELECT 200907,'2009/8/2 2:00:00' UNION ALL
SELECT 200907,'2009/8/2 8:00:00' UNION ALL
SELECT 200907,'2009/8/2 20:00:00' UNION ALL
SELECT 200907,'2009/8/3 2:00:00' UNION ALL
SELECT 200907,'2009/8/3 8:00:00' UNION ALL
SELECT 200907,'2009/8/3 14:00:00' UNION ALL
SELECT 200907,'2009/8/3 20:00:00' UNION ALL
SELECT 200907,'2009/8/3 23:00:00' UNION ALL
SELECT 200907,'2009/8/4 1:00:00' UNION ALL
SELECT 200907,'2009/8/4 2:00:00' UNION ALL
SELECT 200907,'2009/8/4 3:00:00' UNION ALL
SELECT 200907,'2009/8/4 4:00:00' UNION ALL
SELECT 200907,'2009/8/4 5:00:00'--SQL查询如下:SELECT
*,status=CASE WHEN EXISTS(SELECT * FROM @T
WHERE id = A.id AND CONVERT(VARCHAR(10),rqsj,120)=CONVERT(VARCHAR(10),A.rqsj,120) AND rqsj < A.rqsj)
THEN 0 ELSE 1 END
FROM @T AS A/*
id rqsj status
----------- ----------------------- -----------
200907 2009-08-02 02:00:00.000 1
200907 2009-08-02 08:00:00.000 0
200907 2009-08-02 20:00:00.000 0
200907 2009-08-03 02:00:00.000 1
200907 2009-08-03 08:00:00.000 0
200907 2009-08-03 14:00:00.000 0
200907 2009-08-03 20:00:00.000 0
200907 2009-08-03 23:00:00.000 0
200907 2009-08-04 01:00:00.000 1
200907 2009-08-04 02:00:00.000 0
200907 2009-08-04 03:00:00.000 0
200907 2009-08-04 04:00:00.000 0
200907 2009-08-04 05:00:00.000 0(13 行受影响)
*/
insert into @test select 200907,'2009/8/2 02:00:00'
insert into @test select 200907,'2009/8/2 08:00:00'
insert into @test select 200907,'2009/8/2 20:00:00'
insert into @test select 200907,'2009/8/3 02:00:00'
insert into @test select 200907,'2009/8/3 08:00:00'
insert into @test select 200907,'2009/8/3 14:00:00'
insert into @test select 200907,'2009/8/3 20:00:00'
insert into @test select 200907,'2009/8/3 23:00:00'
insert into @test select 200907,'2009/8/4 01:00:00'
insert into @test select 200907,'2009/8/4 02:00:00'
insert into @test select 200907,'2009/8/4 03:00:00'
insert into @test select 200907,'2009/8/4 04:00:00'
insert into @test select 200907,'2009/8/4 05:00:00' select
t.*,(case when
exists(select 1 from @test where datediff(dd,rqsj,t.rqsj)=0 and rqsj<t.rqsj) then 0
else 1
end) as status
from
@test t/*
id rqsj status
----------- ------------------------------------------------------ -----------
200907 2009-08-02 02:00:00.000 1
200907 2009-08-02 08:00:00.000 0
200907 2009-08-02 20:00:00.000 0
200907 2009-08-03 02:00:00.000 1
200907 2009-08-03 08:00:00.000 0
200907 2009-08-03 14:00:00.000 0
200907 2009-08-03 20:00:00.000 0
200907 2009-08-03 23:00:00.000 0
200907 2009-08-04 01:00:00.000 1
200907 2009-08-04 02:00:00.000 0
200907 2009-08-04 03:00:00.000 0
200907 2009-08-04 04:00:00.000 0
200907 2009-08-04 05:00:00.000 0
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-23 14:09:17
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[rqsj] datetime)
insert [tb]
select 200907,'2009/8/2 2:00:00' union all
select 200907,'2009/8/2 8:00:00' union all
select 200907,'2009/8/2 20:00:00' union all
select 200907,'2009/8/3 2:00:00' union all
select 200907,'2009/8/3 8:00:00' union all
select 200907,'2009/8/3 14:00:00' union all
select 200907,'2009/8/3 20:00:00' union all
select 200907,'2009/8/3 23:00:00' union all
select 200907,'2009/8/4 1:00:00' union all
select 200907,'2009/8/4 2:00:00' union all
select 200907,'2009/8/4 3:00:00' union all
select 200907,'2009/8/4 4:00:00' union all
select 200907,'2009/8/4 5:00:00'
--------------开始查询--------------------------
select
t.*,(case when exists(select 1 from tb where datediff(dd,rqsj,t.rqsj)=0 and rqsj<t.rqsj) then 0 else 1 end) as status
from
tb t
----------------结果----------------------------
/* id rqsj status
----------- ----------------------- -----------
200907 2009-08-02 02:00:00.000 1
200907 2009-08-02 08:00:00.000 0
200907 2009-08-02 20:00:00.000 0
200907 2009-08-03 02:00:00.000 1
200907 2009-08-03 08:00:00.000 0
200907 2009-08-03 14:00:00.000 0
200907 2009-08-03 20:00:00.000 0
200907 2009-08-03 23:00:00.000 0
200907 2009-08-04 01:00:00.000 1
200907 2009-08-04 02:00:00.000 0
200907 2009-08-04 03:00:00.000 0
200907 2009-08-04 04:00:00.000 0
200907 2009-08-04 05:00:00.000 0(13 行受影响)
*/
status=case when not exists(select * from test where DATEDIFF(DAY,rqsj,r.rqsj)=0 and DATEDIFF(HOUR,rqsj,r.rqsj)>0)
then 1 else 0 end
from test r
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-23 14:09:17
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[rqsj] datetime)
insert [tb]
select 200907,'2009/8/2 2:00:00' union all
select 200907,'2009/8/2 8:00:00' union all
select 200907,'2009/8/2 20:00:00' union all
select 200907,'2009/8/3 2:00:00' union all
select 200907,'2009/8/3 8:00:00' union all
select 200907,'2009/8/3 14:00:00' union all
select 200907,'2009/8/3 20:00:00' union all
select 200907,'2009/8/3 23:00:00' union all
select 200907,'2009/8/4 1:00:00' union all
select 200907,'2009/8/4 2:00:00' union all
select 200907,'2009/8/4 3:00:00' union all
select 200907,'2009/8/4 4:00:00' union all
select 200907,'2009/8/4 5:00:00'
--------------开始查询--------------------------
select
t.*,(case when exists(select 1 from tb where datediff(dd,rqsj,t.rqsj)=0 and rqsj<t.rqsj and id=t.id) then 0 else 1 end) as status
from
tb t
----------------结果----------------------------
/* id rqsj status
----------- ----------------------- -----------
200907 2009-08-02 02:00:00.000 1
200907 2009-08-02 08:00:00.000 0
200907 2009-08-02 20:00:00.000 0
200907 2009-08-03 02:00:00.000 1
200907 2009-08-03 08:00:00.000 0
200907 2009-08-03 14:00:00.000 0
200907 2009-08-03 20:00:00.000 0
200907 2009-08-03 23:00:00.000 0
200907 2009-08-04 01:00:00.000 1
200907 2009-08-04 02:00:00.000 0
200907 2009-08-04 03:00:00.000 0
200907 2009-08-04 04:00:00.000 0
200907 2009-08-04 05:00:00.000 0(13 行受影响)
*/
INSERT INTO @TB
SELECT 200907,'2009/8/2 2:00:00' UNION ALL
SELECT 200907,'2009/8/2 8:00:00' UNION ALL
SELECT 200907,'2009/8/2 20:00:00' UNION ALL
SELECT 200907,'2009/8/3 2:00:00' UNION ALL
SELECT 200907,'2009/8/3 8:00:00' UNION ALL
SELECT 200907,'2009/8/3 14:00:00' UNION ALL
SELECT 200907,'2009/8/3 20:00:00' UNION ALL
SELECT 200907,'2009/8/3 23:00:00' UNION ALL
SELECT 200907,'2009/8/4 1:00:00' UNION ALL
SELECT 200907,'2009/8/4 2:00:00' UNION ALL
SELECT 200907,'2009/8/4 3:00:00' UNION ALL
SELECT 200907,'2009/8/4 4:00:00' UNION ALL
SELECT 200907,'2009/8/4 5:00:00'select *,
status=case when not exists(select * from @TB where DATEDIFF(DAY,rqsj,r.rqsj)=0 and DATEDIFF(HOUR,rqsj,r.rqsj)>0)
then 1 else 0 end
from @TB r
/*
id rqsj status
----------- ----------------------- -----------
200907 2009-08-02 02:00:00.000 1
200907 2009-08-02 08:00:00.000 0
200907 2009-08-02 20:00:00.000 0
200907 2009-08-03 02:00:00.000 1
200907 2009-08-03 08:00:00.000 0
200907 2009-08-03 14:00:00.000 0
200907 2009-08-03 20:00:00.000 0
200907 2009-08-03 23:00:00.000 0
200907 2009-08-04 01:00:00.000 1
200907 2009-08-04 02:00:00.000 0
200907 2009-08-04 03:00:00.000 0
200907 2009-08-04 04:00:00.000 0
200907 2009-08-04 05:00:00.000 0
*/
create table test(id varchar(6), rqsj datetime)
insert test select
'200907', '2009/8/2 2:00:00' union all select
'200907', '2009/8/2 8:00:00' union all select
'200907', '2009/8/2 20:00:00' union all select
'200907', '2009/8/3 2:00:00' union all select
'200907', '2009/8/3 8:00:00' union all select
'200907', '2009/8/3 14:00:00' union all select
'200907', '2009/8/3 20:00:00' union all select
'200907', '2009/8/3 23:00:00' union all select
'200907', '2009/8/4 1:00:00' union all select
'200907', '2009/8/4 2:00:00' union all select
'200907', '2009/8/4 3:00:00' union all select
'200907', '2009/8/4 4:00:00' union all select
'200907', '2009/8/4 5:00:00' select *,
status=(
select case when count(1)=0 then 1 else 0 end
from test
where id=t.id and datediff(day,rqsj,t.rqsj)=0 and rqsj<t.rqsj
)
from test t
id rqsj status
------ ----------------------- -----------
200907 2009-08-02 02:00:00.000 1
200907 2009-08-02 08:00:00.000 0
200907 2009-08-02 20:00:00.000 0
200907 2009-08-03 02:00:00.000 1
200907 2009-08-03 08:00:00.000 0
200907 2009-08-03 14:00:00.000 0
200907 2009-08-03 20:00:00.000 0
200907 2009-08-03 23:00:00.000 0
200907 2009-08-04 01:00:00.000 1
200907 2009-08-04 02:00:00.000 0
200907 2009-08-04 03:00:00.000 0
200907 2009-08-04 04:00:00.000 0
200907 2009-08-04 05:00:00.000 0(13 行受影响)drop table test
谢谢各位了