to:quanyi(长生天) select count(*) from telephone where pickkup>=你的开始时间 and pickdown<=你的结束时间 这个肯定是不行的 简单打个比方 pickup pickdown id 12:00 16:00 1 13:00 13:10 2 14:00 15:00 3 上面的语句把第2条和第3条都算进去了,但是第2条和第3条没有重复时段
select top 1 right('0'+rtrim(a.[hour],2)+':00--'right('0'+rtrim(a.[hour]+1,2)+':00' as 时段, a.num as 接通个数 from (select datepart(hh,pickkup) [hour], count(*) as num from telephone group by datepart(hh,pickkup)) a order by a.num desc
楼主 看下这个效果! 看看是不是你想要的declare @T table(pickup varchar(10),pickdown varchar(10),id int identity(1,1),number int) insert @T(pickup,pickdown) select '03:00' , '04:00' union all select '03:30' , '03:40' union all select '03:10' , '03:50' union all select '08:00' , '10:00' union all select '08:04' , '08:09' union all select '08:20' , '08:23' union all select '08:01' , '08:16' union all select '08:02' , '08:27' union all select '08:25' , '09:00' union all select '12:00' , '16:00' union all select '13:00' , '13:10' union all select '14:00' , '15:00' update @t set number=(select count(1) from @T where pickup <=k.pickup and pickdown>=k.pickdown) from @t kselect * from @t select top 1 * from @t order by number desc
楼上写错了,修正一下: ----------------------------------------------------------------------------------------------------------------------------- select top 1 right('0'+rtrim(a.[hour]),2)+':00--'+right('0'+rtrim(a.[hour]+1),2)+':00' as 时段, a.num as 接通个数 from (select datepart(hh,pickkup) [hour], count(*) as num from telephone group by datepart(hh,pickkup)) a order by a.num desc
我觉得SQL语句是这样写: select top10 from telephone where pickkup>开始时间 and pickkdown<结束时间。这样就可以了
--declare function CREATE FUNCTION DBO.CurrentCnt (@now AS INT) RETURNS INT AS BEGIN DECLARE @cnt AS INTSELECT @cnt=COUNT(*) FROM table1 WHERE @now BETWEEN pickup AND pickdownRETURN @cnt END--get result SELECT TOP 1 a.pickup, a.cnt AS upcnt, b.pickdown, dbo.CurrentCnt(b.pickdown) AS downcnt FROM ( SELECT TOP 1 pickup, dbo.CurrentCnt(pickup) AS cnt FROM talbe1 ORDER BY cnt DESC) a, table1 b WHERE b.pickdown>a.pickup ORDER BY downcnt DESC不适合用SQL来做。
用存储过程是可以的。--declare function CREATE FUNCTION dbo.CurrentCnt (@now AS INT) RETURNS INT AS BEGIN DECLARE @cnt AS INTSELECT @cnt=COUNT(*) FROM table1 WHERE @now BETWEEN pickup AND pickdownRETURN @cnt END--declare procedure CREATE PROCEDURE dbo.GetMaxPeriod AS DECLARE @begin_time int DECLARE @end_time int DECLARE @end_cnt int DECLARE @next_end_time int DECLARE @next_end_cnt intSELECT top 1 @begin_time=pickup FROM table1 ORDER BY dbo.CurrentCnt(pickup) DESCSELECT @end_time=MIN(pickdown) FROM table1 WHERE pickdown>@begin_timeSELECT @end_cnt=dbo.CurrentCnt(@end_time)SET @next_end_cnt=@end_cnt SET @next_end_time=@end_timeWHILE @next_end_cnt>=@end_cnt BEGIN SELECT @end_cnt=@next_end_cnt, @end_time=@next_end_time
SELECT @next_end_time=min(pickdown) FROM table1 WHERE pickdown>@next_end_time
SELECT @next_end_cnt=dbo.CurrentCnt(@next_end_time) ENDSELECT @begin_time AS pickup, @end_time AS pickdown--get result exec GetMaxPeriod
select count(*) from telephone
where pickkup>=你的开始时间 and pickdown<=你的结束时间
这个肯定是不行的
简单打个比方
pickup pickdown id
12:00 16:00 1
13:00 13:10 2
14:00 15:00 3
上面的语句把第2条和第3条都算进去了,但是第2条和第3条没有重复时段
top 1
right('0'+rtrim(a.[hour],2)+':00--'right('0'+rtrim(a.[hour]+1,2)+':00' as 时段,
a.num as 接通个数
from
(select
datepart(hh,pickkup) [hour],
count(*) as num
from
telephone
group by
datepart(hh,pickkup)) a
order by
a.num desc
insert @T(pickup,pickdown)
select '03:00' , '04:00' union all
select '03:30' , '03:40' union all
select '03:10' , '03:50' union all
select '08:00' , '10:00' union all
select '08:04' , '08:09' union all
select '08:20' , '08:23' union all
select '08:01' , '08:16' union all
select '08:02' , '08:27' union all
select '08:25' , '09:00' union all
select '12:00' , '16:00' union all
select '13:00' , '13:10' union all
select '14:00' , '15:00' update @t set number=(select count(1) from @T where pickup <=k.pickup and pickdown>=k.pickdown)
from @t kselect * from @t
select top 1 * from @t order by number desc
-----------------------------------------------------------------------------------------------------------------------------
select top 1
right('0'+rtrim(a.[hour]),2)+':00--'+right('0'+rtrim(a.[hour]+1),2)+':00' as 时段,
a.num as 接通个数
from
(select
datepart(hh,pickkup) [hour],
count(*) as num
from
telephone
group by
datepart(hh,pickkup)) a
order by
a.num desc
例如
pickup pickdown
8:00 10:00
8:01 8:16
8:02 8:27
8:04 8:09
8:05 8:10
8:06 8:45
查询结果应该是 : 时段 8:06 --- 8:09 通话峰值 6,即在8:06--8:09之间有6组同时通话.
select top10 from telephone where pickkup>开始时间 and pickkdown<结束时间。这样就可以了
CREATE FUNCTION DBO.CurrentCnt
(@now AS INT)
RETURNS INT
AS
BEGIN
DECLARE @cnt AS INTSELECT @cnt=COUNT(*)
FROM table1
WHERE @now BETWEEN pickup AND pickdownRETURN @cnt
END--get result
SELECT TOP 1 a.pickup, a.cnt AS upcnt,
b.pickdown, dbo.CurrentCnt(b.pickdown) AS downcnt
FROM (
SELECT TOP 1 pickup, dbo.CurrentCnt(pickup) AS cnt
FROM talbe1
ORDER BY cnt DESC) a, table1 b
WHERE b.pickdown>a.pickup
ORDER BY downcnt DESC不适合用SQL来做。
CREATE FUNCTION dbo.CurrentCnt
(@now AS INT)
RETURNS INT
AS
BEGIN
DECLARE @cnt AS INTSELECT @cnt=COUNT(*)
FROM table1
WHERE @now BETWEEN pickup AND pickdownRETURN @cnt
END--declare procedure
CREATE PROCEDURE dbo.GetMaxPeriod
AS
DECLARE @begin_time int
DECLARE @end_time int
DECLARE @end_cnt int
DECLARE @next_end_time int
DECLARE @next_end_cnt intSELECT top 1 @begin_time=pickup
FROM table1
ORDER BY dbo.CurrentCnt(pickup) DESCSELECT @end_time=MIN(pickdown)
FROM table1
WHERE pickdown>@begin_timeSELECT @end_cnt=dbo.CurrentCnt(@end_time)SET @next_end_cnt=@end_cnt
SET @next_end_time=@end_timeWHILE @next_end_cnt>=@end_cnt
BEGIN
SELECT @end_cnt=@next_end_cnt, @end_time=@next_end_time
SELECT @next_end_time=min(pickdown)
FROM table1
WHERE pickdown>@next_end_time
SELECT @next_end_cnt=dbo.CurrentCnt(@next_end_time)
ENDSELECT @begin_time AS pickup, @end_time AS pickdown--get result
exec GetMaxPeriod