34,576
社区成员
发帖
与我相关
我的任务
分享
---------------------------------
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-07-15 07:29:44
---------------------------------
IF OBJECT_ID('[D_Status]') IS NOT NULL
DROP TABLE [D_Status]
go
CREATE TABLE [D_Status] (ID INT,日期时间 DATETIME,区域 INT,类型 INT,编号 INT,用户 INT,状态 int)
INSERT INTO [D_Status]
SELECT 1,'2009-07-01 08:17:06.577',1,2,1,1,1 UNION ALL
SELECT 2,'2009-07-01 08:17:54.153',1,2,1,1,1 UNION ALL
SELECT 3,'2009-07-01 08:17:58.450',1,2,1,1,1 UNION ALL
SELECT 4,'2009-07-01 08:17:59.653',1,2,1,1,1 UNION ALL
SELECT 5,'2009-07-01 08:18:01.577',1,2,1,1,1 UNION ALL
SELECT 6,'2009-07-01 08:18:03.530',1,2,1,1,1 UNION ALL
SELECT 7,'2009-07-01 10:44:44.437',1,2,2,2,2 UNION ALL
SELECT 8,'2009-07-01 10:44:57.700',1,2,2,2,2 UNION ALL
SELECT 9,'2009-07-01 10:45:10.937',1,2,2,1,2 UNION ALL
SELECT 10,'2009-07-01 10:45:24.233',1,2,2,1,2 UNION ALL
SELECT 11,'2009-07-01 10:45:37.437',1,2,2,3,2 UNION ALL
SELECT 12,'2009-07-01 10:45:50.623',1,2,2,4,2 UNION ALL
SELECT 13,'2009-07-01 10:46:03.937',1,2,2,2,2 UNION ALL
SELECT 14,'2009-07-01 10:46:17.263',1,2,2,2,2 UNION ALL
SELECT 15,'2009-07-01 10:46:30.437',1,2,2,2,2 UNION ALL
SELECT 16,'2009-07-01 10:46:43.577',1,2,2,4,2
select * from [D_Status]
---------------------------------
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-07-15 07:30:07
---------------------------------
IF OBJECT_ID('[D_data]') IS NOT NULL
DROP TABLE [D_data]
go
CREATE TABLE [D_data] (ID INT,日期时间 DATETIME,区域 INT,类型 INT,编号 INT,用户 INT,用电量 INT,用水量 INT)
INSERT INTO [D_data]
SELECT 1,'2009-07-01 08:15:21.357',1,2,1,1,1,1 UNION ALL
SELECT 2,'2009-07-01 08:16:03.483',1,2,1,2,1,2 UNION ALL
SELECT 3,'2009-07-01 08:16:59.420',1,2,1,1,2,1 UNION ALL
SELECT 4,'2009-07-01 08:17:53.623',1,2,1,1,3,2 UNION ALL
SELECT 5,'2009-07-01 08:17:58.247',1,2,1,1,4,2 UNION ALL
SELECT 6,'2009-07-01 08:17:59.450',1,2,1,1,5,3 UNION ALL
SELECT 7,'2009-07-01 08:18:01.357',1,2,1,1,1,4 UNION ALL
SELECT 8,'2009-07-01 08:18:02.780',1,2,2,2,3,6 UNION ALL
SELECT 9,'2009-07-01 10:44:50.840',1,2,2,3,1,0 UNION ALL
SELECT 10,'2009-07-01 10:45:03.950',1,2,1,1,6,4 UNION ALL
SELECT 11,'2009-07-01 10:45:17.293',1,2,3,5,4,0 UNION ALL
SELECT 12,'2009-07-01 10:45:30.577',1,2,1,5,5,0 UNION ALL
SELECT 13,'2009-07-01 10:45:43.827',1,2,1,4,6,0 UNION ALL
SELECT 14,'2009-07-01 10:45:57.030',1,2,2,7,7,0 UNION ALL
SELECT 15,'2009-07-01 10:46:10.250',1,2,1,4,8,0 UNION ALL
SELECT 16,'2009-07-01 10:46:23.873',1,2,3,9,9,0 UNION ALL
SELECT 17,'2009-07-01 10:46:36.653',1,2,2,1,6,4 UNION ALL
SELECT 18,'2009-07-01 10:46:53.500',1,2,3,2,4,8
select * from [D_data]
;
with
wang
as(
select 区域,类型,用户,开始时间=min(case when 状态=1 then 日期时间 end),终止时间=min(case when 状态=2 then 日期时间 end) from d_status t group by 区域,类型,用户
),
wang1
as(
select top 2 t.* from wang s ,d_data t where s.区域=t.区域 and s.类型=t.类型 and s.用户=t.用户
and 开始时间 is not null
and (日期时间 between 开始时间 and 终止时间)
)
select * from wang1 order by 日期时间 desc
ID 日期时间 区域 类型 编号 用户 用电量 用水量
10 2009-07-01 10:45:03.950 1 2 1 1 6 4
7 2009-07-01 08:18:01.357 1 2 1 1 1 4
select a.字段-b.字段 from 表1 a full join 表1 b on a.id=b.id-1