关于几个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
想了两天，写出了几个查询，不过感觉写的很繁琐，而且效率很低，希望大家抽空帮忙看看，分不多，谢谢了
[解决办法]
use ForTestgoif OBJECT_ID('tb_v1','u')is not null drop table dbo.tb_v1gocreate 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-318 E 1 2011-2-516 F 1 2011-2-320 G 1 2011-2-321 H 1 2011-2-323 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-321 H 1 2011-2-323 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=a.Name and Status=2 ) order by a.Name,a.ID /*3.新打开的灯已经关了，结果如下：14 E 1 2011-2-318 E 1 2011-2-516 F 1 2011-2-3*/;with cte_v3 as ( select * from tb_v1 where Date between N'2011-2-1' and N'2011-2-28') select a.* from cte_v3 a where a.Status=1 and exists( select 1 from cte_v3 where ID>a.ID and Name=a.Name and Status=2 )order by a.Name,a.ID /*4.相对于这个时间曾经打开的灯（相当于存量）曾经打开的所有灯，结果如下：（2010的A打开又关了，这样的灯不算）5 A 1 2011-1-36 B 1 2011-1-37 C 1 2011-1-38 D 1 2011-1-311 J 1 2011-1-4*/;with cte_v4 as ( select * from tb_v1 where Date < N'2011-2-1') select * from cte_v4 a where a.Status=1 and not exists( select 1 from cte_v4 where ID>a.ID and Name=a.Name and Status=2 ) /*5.曾经打开的灯还开着的，结果如下：7 C 1 2011-1-38 D 1 2011-1-3*/;with cte_v5 as ( select * from tb_v1 where Date < N'2011-2-1') select a.* from cte_v5 a where a.Status=1 and not exists( select 1 from tb_v1 where ID>a.ID and a.Name=Name and Status=2 )/*6.曾经打开的灯已经关了，结果如下：5 A 1 2011-1-36 B 1 2011-1-311 J 1 2011-1-4*/;with cte_v6 as ( select * from tb_v1 where Date < N'2011-2-1') select a.* from cte_v6 a where a.Status=1 and not exists( select 1 from cte_v6 where ID>a.ID and a.Name=Name and Status=2 ) and exists( select 1 from tb_v1 where ID>a.ID and a.Name=Name and Status=2 )