日期:2014-05-18  浏览次数:20548 次

关于几个SQL查询语句

id name status date
1 A 1 2010-1-3
2 A 2 2010-1-4
3 I 1 2010-1-3
4 I 2 2010-1-4
5 A 1 2011-1-3
6 B 1 2011-1-3
7 C 1 2011-1-3
8 D 1 2011-1-3
9 I 1 2011-1-3
10 I 2 2011-1-4
11 J 1 2011-1-4
12 A 2 2011-2-3
13 B 2 2011-2-3
14 E 1 2011-2-3
15 E 2 2011-2-3
16 F 1 2011-2-3
17 F 2 2011-2-3
18 E 1 2011-2-5
19 E 1 2011-2-6
20 G 1 2011-2-3
21 H 1 2011-2-3
22 J 2 2011-2-6
23 J 1 2011-2-7
24 G 2 2011-3-3


name表示灯,status表示开关状态(1表示开,2表示关)
进行如下查询:
查询时间设定:2011-2-1到2011-2-28之间
相对于这个时间查询新打开的灯
所有新打开的灯,结果如下:
14 E 1 2011-2-3
18 E 1 2011-2-5
16 F 1 2011-2-3
20 G 1 2011-2-3
21 H 1 2011-2-3
23 J 1 2011-2-7


新打开的灯中还开着的,结果如下:
20 G 1 2011-2-3
21 H 1 2011-2-3
23 J 1 2011-2-7


新打开的灯已经关了,结果如下:
14 E 1 2011-2-3
18 E 1 2011-2-5
16 F 1 2011-2-3

相对于这个时间曾经打开的灯(相当于存量)
曾经打开的所有灯,结果如下:(2010的A打开又关了,这样的灯不算)
5 A 1 2011-1-3
6 B 1 2011-1-3
7 C 1 2011-1-3
8 D 1 2011-1-3
11 J 1 2011-1-4

曾经打开的灯还开着的,结果如下:
7 C 1 2011-1-3
8 D 1 2011-1-3
11 J 1 2011-1-4

曾经打开的灯已经关了,结果如下:
5 A 1 2011-1-3
6 B 1 2011-1-3


想了两天,写出了几个查询,不过感觉写的很繁琐,而且效率很低,希望大家抽空帮忙看看,分不多,谢谢了

------解决方案--------------------
SQL code

use ForTest
go

if OBJECT_ID('tb_v1','u')is not null
    drop table dbo.tb_v1
go

create table dbo.tb_v1(
    ID int
    constraint pk_tb_v1 primary key(ID)
    ,Name nvarchar(50)
    ,[Status] int
    constraint chk_tb_v1_Status check([Status] in (1,2))
    ,[Date] datetime
)

insert into dbo.tb_v1 (ID,Name,[Status],[Date])
    select 1,N'A',1,N'2010-1-3'
    union all select 2,N'A',2,N'2010-1-4'
    union all select 3,N'I',1,N'2010-1-3'
    union all select 4,N'I',2,N'2010-1-4'
    union all select 5,N'A',1,N'2011-1-3'
    union all select 6,N'B',1,N'2011-1-3'
    union all select 7,N'C',1,N'2011-1-3'
    union all select 8,N'D',1,N'2011-1-3'
    union all select 9,N'I',1,N'2011-1-3'
    union all select 10,N'I',2,N'2011-1-4'
    union all select 11,N'J',1,N'2011-1-4'
    union all select 12,N'A',2,N'2011-2-3'
    union all select 13,N'B',2,N'2011-2-3'
    union all select 14,N'E',1,N'2011-2-3'
    union all select 15,N'E',2,N'2011-2-3'
    union all select 16,N'F',1,N'2011-2-3'
    union all select 17,N'F',2,N'2011-2-3'
    union all select 18,N'E',1,N'2011-2-5'
    union all select 19,N'E',2,N'2011-2-6'
    union all select 20,N'G',1,N'2011-2-3'
    union all select 21,N'H',1,N'2011-2-3'
    union all select 22,N'J',2,N'2011-2-6'
    union all select 23,N'J',1,N'2011-2-7'
    union all select 24,N'G',2,N'2011-3-3'
go

--select * from tb_v1

/*
1.
查询时间设定:2011-2-1到2011-2-28之间
相对于这个时间查询新打开的灯
所有新打开的灯,结果如下:
14 E 1 2011-2-3
18 E 1 2011-2-5
16 F 1 2011-2-3
20 G 1 2011-2-3
21 H 1 2011-2-3
23 J 1 2011-2-7
*/

;with cte_v1 as(
    select * from tb_v1
    where Date between N'2011-2-1' and N'2011-2-28'
)
    select * from cte_v1 
    where Status=1
    order by Name,ID
    
/*
2.
新打开的灯中还开着的,结果如下:
20 G 1 2011-2-3
21 H 1 2011-2-3
23 J 1 2011-2-7
*/

;with cte_v2 as (
    select * from tb_v1
    where Date between N'2011-2-1' and N'2011-2-28'
)
    select a.* from cte_v2 a
    where a.status=1 
        and not exists(
            select 1 from cte_v2
            where ID>a.ID
                and Name=