有一个表,要求是这样的
编号 抽样时间 抽样值
1 2007-03-04 9:10
2 2007-03-04 11:27
3 2007-03-04 13:34
4 2007-03-04 15:54
1 2007-03-04 17:01
2 2007-03-04 18:34
3 2007-03-04 21:07
4 2007-03-04 23:16
5 2007-03-05 00:34
1 2007-03-05 1:01
2 2007-03-05 3:45
3 2007-03-05 8:59
1 2007-03-05 9:01
就是把一天分成3段:1:01 -- 9:00;9:01 -- 17:00;17:01 -- 第二天1:00
在一个时间段里编号从1开始以时间排序.进入另一个时间段,编号重新从1开始以时间排序.而且一个时间段里抽样的次数不限制.还有个时间段要跨天
这种表怎么做???跪求!!!!
编号 抽样时间 抽样值
1 2007-03-04 9:10
2 2007-03-04 11:27
3 2007-03-04 13:34
4 2007-03-04 15:54
1 2007-03-04 17:01
2 2007-03-04 18:34
3 2007-03-04 21:07
4 2007-03-04 23:16
5 2007-03-05 00:34
1 2007-03-05 1:01
2 2007-03-05 3:45
3 2007-03-05 8:59
1 2007-03-05 9:01
就是把一天分成3段:1:01 -- 9:00;9:01 -- 17:00;17:01 -- 第二天1:00
在一个时间段里编号从1开始以时间排序.进入另一个时间段,编号重新从1开始以时间排序.而且一个时间段里抽样的次数不限制.还有个时间段要跨天
这种表怎么做???跪求!!!!
功能就是获得给定之间对应的时间段的开始时间。
比如给定 2007-03-04 11:27 就可以获得 2007-03-04 09:01然后再写一个查询排序就可以了。
SELECT ...
FROM ...
ORDER BY getStartTime(抽样时间),编号。
(20070201A,20070201B,20070201C)
序号 整型 (可以自动生成)
...用这两个作键,感觉会比较自然
检索排序啥的,就可以不用 抽样时间。免得变换啥的麻里麻烦的。
具体怎么实现?
获得开始时间怎么用?
create table # (抽样时间 datetime)
insert into # select '2007-03-04 9:10' union all
select '2007-03-04 11:27' union all
select '2007-03-04 13:34' union all
select '2007-03-04 15:54' union all
select '2007-03-04 17:01' union all
select '2007-03-04 18:34' union all
select '2007-03-04 21:07' union all
select '2007-03-04 23:16' union all
select '2007-03-05 00:34' union all
select '2007-03-05 1:01' union all
select '2007-03-05 3:45' union all
select '2007-03-05 8:59' union all
select '2007-03-05 9:01'
go--查询
select a=convert(varchar(10),dateadd(hour,-1,抽样时间),120)+(case when convert(varchar(8),dateadd(hour,-1,抽样时间),108) between '00:00:01' and '08:00:00' then '0'
when convert(varchar(8),dateadd(hour,-1,抽样时间),108) between '08:00:01' and '16:00:00' then '1'
when convert(varchar(8),dateadd(hour,-1,抽样时间),108) between '16:00:01' and '23:59:59' then '2' end),抽样时间
into ##
from #
goselect 编号=(select count(1)+1 from ## where a=t.a and 抽样时间<t.抽样时间),抽样时间
from ## t
go
--结果
1 2007-03-04 09:10:00.000
2 2007-03-04 11:27:00.000
3 2007-03-04 13:34:00.000
4 2007-03-04 15:54:00.000
1 2007-03-04 17:01:00.000
2 2007-03-04 18:34:00.000
3 2007-03-04 21:07:00.000
4 2007-03-04 23:16:00.000
5 2007-03-05 00:34:00.000
1 2007-03-05 01:01:00.000
2 2007-03-05 03:45:00.000
3 2007-03-05 08:59:00.000
1 2007-03-05 09:01:00.000
(
编号 INT,
抽样时间 DATETIME
)
INSERT INTO A
SELECT 1,CAST('2007-03-04 9:10' AS DATETIME) UNION ALL
SELECT 2,CAST('2007-03-04 11:27' AS DATETIME) UNION ALL
SELECT 3,CAST('2007-03-04 13:34' AS DATETIME) UNION ALL
SELECT 4,CAST('2007-03-04 15:54' AS DATETIME) UNION ALL
SELECT 1,CAST('2007-03-04 17:01' AS DATETIME) UNION ALL
SELECT 2,CAST('2007-03-04 18:34' AS DATETIME) UNION ALL
SELECT 3,CAST('2007-03-04 21:07' AS DATETIME) UNION ALL
SELECT 4,CAST('2007-03-04 23:16' AS DATETIME) UNION ALL
SELECT 5,CAST('2007-03-05 00:34' AS DATETIME) UNION ALL
SELECT 1,CAST('2007-03-05 1:01' AS DATETIME) UNION ALL
SELECT 2,CAST('2007-03-05 3:45' AS DATETIME) UNION ALL
SELECT 3,CAST('2007-03-05 8:59' AS DATETIME) UNION ALL
SELECT 1,CAST('2007-03-05 9:01' AS DATETIME)
GO
SELECT *,
CASE
WHEN DATEDIFF(minute,'1:01',RIGHT(CONVERT(CHAR(16),抽样时间,120),5))>=0 AND DATEDIFF(minute,'1:01',RIGHT(CONVERT(CHAR(16),抽样时间,120),5))<=479 THEN 1
WHEN DATEDIFF(minute,'9:01',RIGHT(CONVERT(CHAR(16),抽样时间,120),5))>=0 AND DATEDIFF(minute,'9:01',RIGHT(CONVERT(CHAR(16),抽样时间,120),5))<=479 THEN 2
WHEN DATEDIFF(minute,'17:01',RIGHT(CONVERT(CHAR(16),抽样时间,120),5))>=0 AND DATEDIFF(minute,'17:01',RIGHT(CONVERT(CHAR(16),抽样时间,120),5))<=418 OR
DATEDIFF(minute,'00:00',RIGHT(CONVERT(CHAR(16),抽样时间,120),5))>=0 AND DATEDIFF(minute,'00:00',RIGHT(CONVERT(CHAR(16),抽样时间,120),5))<=60 THEN 3
END AS ORD
INTO # FROM ASELECT A.编号,A.抽样时间,BH=(SELECT COUNT(1) FROM # B WHERE ORD=A.ORD AND 抽样时间<=A.抽样时间) FROM # A DROP TABLE #
--结果
编号 抽样时间 BH
----------- ----------------------- -----------
1 2007-03-04 09:10:00.000 1
2 2007-03-04 11:27:00.000 2
3 2007-03-04 13:34:00.000 3
4 2007-03-04 15:54:00.000 4
1 2007-03-04 17:01:00.000 1
2 2007-03-04 18:34:00.000 2
3 2007-03-04 21:07:00.000 3
4 2007-03-04 23:16:00.000 4
5 2007-03-05 00:34:00.000 5
1 2007-03-05 01:01:00.000 1
2 2007-03-05 03:45:00.000 2
3 2007-03-05 08:59:00.000 3
1 2007-03-05 09:01:00.000 5(13 行受影响)
CREATE TABLE A
(
编号 INT,
抽样时间 DATETIME
)
INSERT INTO A
SELECT 1,CAST('2007-03-04 9:10' AS DATETIME) UNION ALL
SELECT 2,CAST('2007-03-04 11:27' AS DATETIME) UNION ALL
SELECT 3,CAST('2007-03-04 13:34' AS DATETIME) UNION ALL
SELECT 4,CAST('2007-03-04 15:54' AS DATETIME) UNION ALL
SELECT 1,CAST('2007-03-04 17:01' AS DATETIME) UNION ALL
SELECT 2,CAST('2007-03-04 18:34' AS DATETIME) UNION ALL
SELECT 3,CAST('2007-03-04 21:07' AS DATETIME) UNION ALL
SELECT 4,CAST('2007-03-04 23:16' AS DATETIME) UNION ALL
SELECT 5,CAST('2007-03-05 00:34' AS DATETIME) UNION ALL
SELECT 1,CAST('2007-03-05 1:01' AS DATETIME) UNION ALL
SELECT 2,CAST('2007-03-05 3:45' AS DATETIME) UNION ALL
SELECT 3,CAST('2007-03-05 8:59' AS DATETIME) UNION ALL
SELECT 1,CAST('2007-03-05 9:01' AS DATETIME)
GO
SELECT *,
CASE
WHEN DATEDIFF(minute,'1:01',RIGHT(CONVERT(CHAR(16),抽样时间,120),5))>=0 AND DATEDIFF(minute,'1:01',RIGHT(CONVERT(CHAR(16),抽样时间,120),5))<=479 THEN 1
WHEN DATEDIFF(minute,'9:01',RIGHT(CONVERT(CHAR(16),抽样时间,120),5))>=0 AND DATEDIFF(minute,'9:01',RIGHT(CONVERT(CHAR(16),抽样时间,120),5))<=479 THEN 2
WHEN DATEDIFF(minute,'17:01',RIGHT(CONVERT(CHAR(16),抽样时间,120),5))>=0 AND DATEDIFF(minute,'17:01',RIGHT(CONVERT(CHAR(16),抽样时间,120),5))<=418 OR
DATEDIFF(minute,'00:00',RIGHT(CONVERT(CHAR(16),抽样时间,120),5))>=0 AND DATEDIFF(minute,'00:00',RIGHT(CONVERT(CHAR(16),抽样时间,120),5))<=60 THEN 3
END AS ORD
,
CASE
WHEN DATEDIFF(minute,'00:00',RIGHT(CONVERT(CHAR(16),抽样时间,120),5))>=0 AND DATEDIFF(minute,'00:00',RIGHT(CONVERT(CHAR(16),抽样时间,120),5))<=60 THEN CONVERT(CHAR(10),DATEADD(DAY,-1,抽样时间),120)
ELSE
CONVERT(CHAR(10),抽样时间,120)
END AS ODAY
INTO # FROM A
SELECT A.编号,A.抽样时间,BH=(SELECT COUNT(1) FROM # B WHERE ORD=A.ORD AND 抽样时间<=A.抽样时间 AND ODAY=A.ODAY) FROM # A
ORDER BY A.ODAY,A.抽样时间
DROP TABLE #
--结果
编号 抽样时间 BH
----------- ----------------------- -----------
1 2007-03-04 09:10:00.000 1
2 2007-03-04 11:27:00.000 2
3 2007-03-04 13:34:00.000 3
4 2007-03-04 15:54:00.000 4
1 2007-03-04 17:01:00.000 1
2 2007-03-04 18:34:00.000 2
3 2007-03-04 21:07:00.000 3
4 2007-03-04 23:16:00.000 4
5 2007-03-05 00:34:00.000 5
1 2007-03-05 01:01:00.000 1
2 2007-03-05 03:45:00.000 2
3 2007-03-05 08:59:00.000 3
1 2007-03-05 09:01:00.000 1(13 行受影响)
create table # (抽样时间 datetime)
insert into # select '2007-03-04 9:10' union all
select '2007-03-04 11:27' union all
select '2007-03-04 13:34' union all
select '2007-03-04 15:54' union all
select '2007-03-04 17:01' union all
select '2007-03-04 18:34' union all
select '2007-03-04 21:07' union all
select '2007-03-04 23:16' union all
select '2007-03-05 00:34' union all
select '2007-03-05 1:01' union all
select '2007-03-05 3:45' union all
select '2007-03-05 8:59' union all
select '2007-03-05 9:01'
select 抽样时间,
(select count(*) from # where
datepart(hh,dateadd(hh,-1,a.抽样时间))/8=datepart(hh,dateadd(hh,-1,抽样时间))/8
and convert(varchar(8),dateadd(hh,-1,a.抽样时间),112)=
convert(varchar(8),dateadd(hh,-1,抽样时间),112)
and 抽样时间<a.抽样时间)+1
as 抽样值 from # a order by 抽样时间
drop table #
所影响的行数为 13 行)抽样时间 抽样值
------------------------------------------------------ -----------
2007-03-04 09:10:00.000 1
2007-03-04 11:27:00.000 2
2007-03-04 13:34:00.000 3
2007-03-04 15:54:00.000 4
2007-03-04 17:01:00.000 1
2007-03-04 18:34:00.000 2
2007-03-04 21:07:00.000 3
2007-03-04 23:16:00.000 4
2007-03-05 00:34:00.000 5
2007-03-05 01:01:00.000 1
2007-03-05 03:45:00.000 2
2007-03-05 08:59:00.000 3
2007-03-05 09:01:00.000 1(所影响的行数为 13 行)
FROM [表名]
ORDER BY CASE
WHEN CONVERT(Varchar(8),抽样时间,108) BETWEEN '00:00:00' AND '01:00:00' THEN CONVERT(Varchar(10),DATEADD(dd,-1,抽样时间),120) + ' 17:01'
WHEN CONVERT(Varchar(8),抽样时间,108) BETWEEN '01:01:00' AND '09:00:00' THEN CONVERT(Varchar(10),抽样时间,120) + ' 01:01'
WHEN CONVERT(Varchar(8),抽样时间,108) BETWEEN '09:01:00' AND '17:00:00' THEN CONVERT(Varchar(10),抽样时间,120) + ' 09:01'
WHEN CONVERT(Varchar(8),抽样时间,108) BETWEEN '17:01:00' AND '23:59:59' THEN CONVERT(Varchar(10),抽样时间,120) + ' 17:01'
END , 编号
FROM [表名]
ORDER BY CASE
WHEN CONVERT(Varchar(5),抽样时间,108) BETWEEN '00:00' AND '01:00' THEN CONVERT(Varchar(10),DATEADD(dd,-1,抽样时间),120) + ' 17:01'
WHEN CONVERT(Varchar(5),抽样时间,108) BETWEEN '01:01' AND '09:00' THEN CONVERT(Varchar(10),抽样时间,120) + ' 01:01'
WHEN CONVERT(Varchar(5),抽样时间,108) BETWEEN '09:01' AND '17:00' THEN CONVERT(Varchar(10),抽样时间,120) + ' 09:01'
WHEN CONVERT(Varchar(5),抽样时间,108) BETWEEN '17:01' AND '23:59' THEN CONVERT(Varchar(10),抽样时间,120) + ' 17:01'
END , 编号因为有跨天的问题,实际上应该分四段,而不是三段。
drop table tb
gocreate table tb
(
编号 int,
抽样时间 datetime,
抽样值 int
)insert into tb(编号,抽样时间,抽样值) values(1,'2007-03-04 9:10' ,1)
insert into tb(编号,抽样时间,抽样值) values(2,'2007-03-04 11:27',2)
insert into tb(编号,抽样时间,抽样值) values(3,'2007-03-04 13:34',3)
insert into tb(编号,抽样时间,抽样值) values(4,'2007-03-04 15:54',4)
insert into tb(编号,抽样时间,抽样值) values(1,'2007-03-04 17:01',5)
insert into tb(编号,抽样时间,抽样值) values(2,'2007-03-04 18:34',6)
insert into tb(编号,抽样时间,抽样值) values(3,'2007-03-04 21:07',7)
insert into tb(编号,抽样时间,抽样值) values(4,'2007-03-04 23:16',8)
insert into tb(编号,抽样时间,抽样值) values(5,'2007-03-05 00:34',9)
insert into tb(编号,抽样时间,抽样值) values(1,'2007-03-05 1:01' ,10)
insert into tb(编号,抽样时间,抽样值) values(2,'2007-03-05 3:45' ,11)
insert into tb(编号,抽样时间,抽样值) values(3,'2007-03-05 8:59' ,12)
insert into tb(编号,抽样时间,抽样值) values(1,'2007-03-05 9:01' ,13) select 编号,
case when substring(convert(varchar(19),抽样时间,120),12,2) >= '01' and substring(convert(varchar(19),抽样时间,120),12,2) < '09' then '01-09'
when substring(convert(varchar(19),抽样时间,120),12,2) >= '09' and substring(convert(varchar(19),抽样时间,120),12,2) < '17' then '09-17'
when substring(convert(varchar(19),抽样时间,120),12,2) >= '17' and substring(convert(varchar(19),抽样时间,120),12,2) < '24' then '17-01'
when substring(convert(varchar(19),抽样时间,120),12,2) >= '00' and substring(convert(varchar(19),抽样时间,120),12,2) < '01' then '17-01'
end as 抽样时间,
抽样值
into test
from tbselect px=(select count(1) from test where 抽样时间=a.抽样时间 and 抽样值<a.抽样值)+1 , 编号,抽样时间,抽样值 from test a
order by 抽样时间,pxdrop table tb,test/*result
px 编号 抽样时间 抽样值
----------- ----------- ----- -----------
1 1 01-09 10
2 2 01-09 11
3 3 01-09 12
1 1 09-17 1
2 2 09-17 2
3 3 09-17 3
4 4 09-17 4
5 1 09-17 13
1 1 17-01 5
2 2 17-01 6
3 3 17-01 7
4 4 17-01 8
5 5 17-01 9(所影响的行数为 13 行)
*/
drop table tb
gocreate table tb
(
编号 int,
抽样时间 datetime,
抽样值 int
)insert into tb(编号,抽样时间,抽样值) values(1,'2007-03-04 9:10' ,1)
insert into tb(编号,抽样时间,抽样值) values(2,'2007-03-04 11:27',2)
insert into tb(编号,抽样时间,抽样值) values(3,'2007-03-04 13:34',3)
insert into tb(编号,抽样时间,抽样值) values(4,'2007-03-04 15:54',4)
insert into tb(编号,抽样时间,抽样值) values(1,'2007-03-04 17:01',5)
insert into tb(编号,抽样时间,抽样值) values(2,'2007-03-04 18:34',6)
insert into tb(编号,抽样时间,抽样值) values(3,'2007-03-04 21:07',7)
insert into tb(编号,抽样时间,抽样值) values(4,'2007-03-04 23:16',8)
insert into tb(编号,抽样时间,抽样值) values(5,'2007-03-05 00:34',9)
insert into tb(编号,抽样时间,抽样值) values(1,'2007-03-05 1:01' ,10)
insert into tb(编号,抽样时间,抽样值) values(2,'2007-03-05 3:45' ,11)
insert into tb(编号,抽样时间,抽样值) values(3,'2007-03-05 8:59' ,12)
insert into tb(编号,抽样时间,抽样值) values(1,'2007-03-05 9:01' ,13) select 编号,
case when substring(convert(varchar(19),抽样时间,120),12,8) >= '01:01:00' and substring(convert(varchar(19),抽样时间,120),12,2) < '09:01:00' then '01-09'
when substring(convert(varchar(19),抽样时间,120),12,8) >= '09:01:00' and substring(convert(varchar(19),抽样时间,120),12,2) < '17:01:00' then '09-17'
when substring(convert(varchar(19),抽样时间,120),12,8) >= '17:01:00' and substring(convert(varchar(19),抽样时间,120),12,2) < '24:00:00' then '17-01'
when substring(convert(varchar(19),抽样时间,120),12,8) >= '00:00:00' and substring(convert(varchar(19),抽样时间,120),12,2) < '01:00:00' then '17-01'
end as 抽样时间,
抽样值
into test
from tbselect px=(select count(1) from test where 抽样时间=a.抽样时间 and 抽样值<a.抽样值)+1 , 编号,抽样时间,抽样值 from test a
order by 抽样时间,pxdrop table tb,test/*result
px 编号 抽样时间 抽样值
----------- ----------- ----- -----------
1 1 01-09 10
2 2 01-09 11
3 3 01-09 12
1 1 09-17 1
2 2 09-17 2
3 3 09-17 3
4 4 09-17 4
5 1 09-17 13
1 1 17-01 5
2 2 17-01 6
3 3 17-01 7
4 4 17-01 8
5 5 17-01 9(所影响的行数为 13 行)
*/
FROM [表名]
ORDER BY CASE
WHEN CONVERT(Varchar(5),抽样时间,108) BETWEEN '00:00' AND '01:00' THEN CONVERT(Varchar(10),DATEADD(dd,-1,抽样时间),120) + ' 17:01'
WHEN CONVERT(Varchar(5),抽样时间,108) BETWEEN '01:01' AND '09:00' THEN CONVERT(Varchar(10),抽样时间,120) + ' 01:01'
WHEN CONVERT(Varchar(5),抽样时间,108) BETWEEN '09:01' AND '17:00' THEN CONVERT(Varchar(10),抽样时间,120) + ' 09:01'
WHEN CONVERT(Varchar(5),抽样时间,108) BETWEEN '17:01' AND '23:59' THEN CONVERT(Varchar(10),抽样时间,120) + ' 17:01'
END , 编号
能解释下吗?
WHEN CONVERT(Varchar(5),抽样时间,108) BETWEEN '00:00' AND '01:00' THEN CONVERT(Varchar(10),DATEADD(dd,-1,抽样时间),120) + ' 17:01'
WHEN CONVERT(Varchar(5),抽样时间,108) BETWEEN '01:01' AND '09:00' THEN CONVERT(Varchar(10),抽样时间,120) + ' 01:01'
WHEN CONVERT(Varchar(5),抽样时间,108) BETWEEN '09:01' AND '17:00' THEN CONVERT(Varchar(10),抽样时间,120) + ' 09:01'
WHEN CONVERT(Varchar(5),抽样时间,108) BETWEEN '17:01' AND '23:59' THEN CONVERT(Varchar(10),抽样时间,120) + ' 17:01'
END上面这个函数得到的就是抽样时间所在时间段的起始时间.首先将时间分四段,
'00:00'-'01:00'
'01:01'-'09:00'
'09:01'-'17:00'
'17:01'-'23:59'
使用条件语句
CASE
WITH ... THEN ...
END
然后取出抽样时间的小时和分钟按照 HH:MI 的格式,
CONVERT(Varchar(5),抽样时间,108)
判断是在那一个时间段.
CONVERT(Varchar(5),抽样时间,108) BETWEEN '17:01' AND '23:59'
然后取出抽样时间对应的日期(如果是第一个时间段的话,日期减一)
CONVERT(Varchar(10),抽样时间,120)
最后再加上开始的时间,因为都是固定的,直接写死'01:01' '09:01' '17:01'
+ ' 17:01'
就可以获得开始的时间,格式是:'2007-03-04 17:01'。获得开始时间后,就可以按照 ORDER BY 开始时间,编号 来排序了。
因为同一个时间段的开始时间都是相等的,所以就可以实现你的要求了。
WHERE CASE
WHEN CONVERT(Varchar(5),[抽样时间],108) BETWEEN '00:00' AND '01:00' THEN CONVERT(Varchar(10),DATEADD(dd,-1,[抽样时间]),120) + ' 17:01'
WHEN CONVERT(Varchar(5),[抽样时间],108) BETWEEN '01:01' AND '09:00' THEN CONVERT(Varchar(10),[抽样时间],120) + ' 01:01'
WHEN CONVERT(Varchar(5),[抽样时间],108) BETWEEN '09:01' AND '17:00' THEN CONVERT(Varchar(10),[抽样时间],120) + ' 09:01'
WHEN CONVERT(Varchar(5),[抽样时间],108) BETWEEN '17:01' AND '23:59' THEN CONVERT(Varchar(10),[抽样时间],120) + ' 17:01'
END
=
CASE
WHEN CONVERT(Varchar(5),'需要插入的时间值',108) BETWEEN '00:00' AND '01:00' THEN CONVERT(Varchar(10),DATEADD(dd,-1,'需要插入的时间值'),120) + ' 17:01'
WHEN CONVERT(Varchar(5),'需要插入的时间值',108) BETWEEN '01:01' AND '09:00' THEN CONVERT(Varchar(10),'需要插入的时间值',120) + ' 01:01'
WHEN CONVERT(Varchar(5),'需要插入的时间值',108) BETWEEN '09:01' AND '17:00' THEN CONVERT(Varchar(10),'需要插入的时间值',120) + ' 09:01'
WHEN CONVERT(Varchar(5),'需要插入的时间值',108) BETWEEN '17:01' AND '23:59' THEN CONVERT(Varchar(10),'需要插入的时间值',120) + ' 17:01'
END