求sql语句,各位大侠帮忙,比较有意思

Question

select person 人, acttype 类型,  时长 = sum(datediff(minute,begintime,endtime))
from #temp
group by person , acttype 

Answer 1

create   table   #temp( 
person   int,--人 
begintime   datetime,--开始时间 
endtime   datetime,--结束时间 
acttype   varchar(50),--做事类型 
re   varchar(100)--备注 
) 
insert   into   #temp   values(1,'9/24/2007   8:00:00   PM','9/24/2007   8:20:00   PM','上网','浏览a网站') 
insert   into   #temp   values(1,'9/24/2007   8:30:00   PM','9/24/2007   8:50:00   PM','上网','浏览b网站') 
insert   into   #temp   values(1,'9/24/2007   8:35:00   PM','9/24/2007   8:40:00   PM','上网','浏览c网站') 
insert   into   #temp   values(1,'9/24/2007   8:45:00   PM','9/24/2007   8:55:00   PM','上网','浏览d网站') insert   into   #temp   values(2,'9/24/2007   8:00:00   PM','9/24/2007   8:30:00   PM','上网','浏览x网站') 
insert   into   #temp   values(2,'9/24/2007   8:30:00   PM','9/24/2007   9:00:00   PM','吃饭','手抓饭') insert   into   #temp   values(3,'9/24/2007   8:00:00   PM','9/24/2007   12:00:00   PM','打游戏','魔兽') select person 人, acttype 类型,  时长 = sum(datediff(minute,begintime,endtime))
from #temp
group by person , acttype 
order by person , acttype drop table #temp/*
人           类型                                                 时长
----------- -------------------------------------------------- -----------
1           上网                                                 55
2           吃饭                                                 30
2           上网                                                 30
3           打游戏                                                -480(4 行受影响)*/

Answer 2

select person ,acttype,sum(datediff(mi,begintime,endtime)) as L
from #temp
group by person,acttype
order by person

Answer 3

create table #temp( 
person int,--人 
begintime datetime,--开始时间 
endtime datetime,--结束时间 
acttype varchar(50),--做事类型 
re varchar(100)--备注 
) 
insert into #temp values(1,'9/24/2007 8:00:00','9/24/2007 8:20:00','上网','浏览a网站') 
insert into #temp values(1,'9/24/2007 8:30:00','9/24/2007 8:50:00','上网','浏览b网站') 
insert into #temp values(1,'9/24/2007 8:35:00','9/24/2007 8:40:00','上网','浏览c网站') 
insert into #temp values(1,'9/24/2007 8:45:00','9/24/2007 8:55:00','上网','浏览d网站') insert into #temp values(2,'9/24/2007 8:00:00','9/24/2007 8:30:00','上网','浏览x网站') 
insert into #temp values(2,'9/24/2007 8:30:00','9/24/2007 9:00:00','吃饭','手抓饭') insert into #temp values(3,'9/24/2007 8:00:00','9/24/2007 12:00:00','打游戏','魔兽') goselect person ,acttype,sum(datediff(mi,begintime,endtime)) as L
from #temp
group by person,acttype
order by person
drop table #temp
/*person      acttype                                            L           
----------- -------------------------------------------------- ----------- 
1           上网                                                 55
2           吃饭                                                 30
2           上网                                                 30
3           打游戏                                                240（所影响的行数为 4 行）（所影响的行数为 4 行）
*/

Answer 4

我把所有的PM去掉后显示才正确.
create table #temp( 
person int,--人 
begintime datetime,--开始时间 
endtime datetime,--结束时间 
acttype varchar(50),--做事类型 
re varchar(100)--备注 
) 
insert into #temp values(1,'9/24/2007 8:00:00','9/24/2007 8:20:00','上网','浏览a网站') 
insert into #temp values(1,'9/24/2007 8:30:00','9/24/2007 8:50:00','上网','浏览b网站') 
insert into #temp values(1,'9/24/2007 8:35:00','9/24/2007 8:40:00','上网','浏览c网站') 
insert into #temp values(1,'9/24/2007 8:45:00','9/24/2007 8:55:00','上网','浏览d网站') insert into #temp values(2,'9/24/2007 8:00:00','9/24/2007 8:30:00','上网','浏览x网站') 
insert into #temp values(2,'9/24/2007 8:30:00','9/24/2007 9:00:00','吃饭','手抓饭') insert into #temp values(3,'9/24/2007 8:00:00','9/24/2007 12:00:00','打游戏','魔兽') select person 人, acttype 类型, 时长 = sum(datediff(minute,begintime,endtime))
from #temp
group by person , acttype 
order by person , acttype drop table #temp/*
人           类型                                                 时长
----------- -------------------------------------------------- -----------
1           上网                                                 55
2           吃饭                                                 30
2           上网                                                 30
3           打游戏                                                240(4 行受影响)
*/

Answer 5

create table #temp( 
person int,--人 
begintime datetime,--开始时间 
endtime datetime,--结束时间 
acttype varchar(50),--做事类型 
re varchar(100)--备注 
) 
insert into #temp values(1,'9/24/2007 8:00:00','9/24/2007 8:20:00','上网','浏览a网站') 
insert into #temp values(1,'9/24/2007 8:30:00','9/24/2007 8:50:00','上网','浏览b网站') 
insert into #temp values(1,'9/24/2007 8:35:00','9/24/2007 8:40:00','上网','浏览c网站') 
insert into #temp values(1,'9/24/2007 8:45:00','9/24/2007 8:55:00','上网','浏览d网站') insert into #temp values(2,'9/24/2007 8:00:00','9/24/2007 8:30:00','上网','浏览x网站') 
insert into #temp values(2,'9/24/2007 8:30:00','9/24/2007 9:00:00','吃饭','手抓饭') insert into #temp values(3,'9/24/2007 8:00:00','9/24/2007 12:00:00','打游戏','魔兽') goselect person ,acttype,ltrim(sum(datediff(mi,begintime,endtime)))+'M' as L
from #temp
group by person,acttype
order by person
drop table #temp
/*person      acttype                                            L             
----------- -------------------------------------------------- ------------- 
1           上网                                                 55M
2           吃饭                                                 30M
2           上网                                                 30M
3           打游戏                                                240M（所影响的行数为 4 行）
*/

Answer 6

select person as 人,acttype as 类型,
sum(datediff(mi,begintime,endtime))as 时间
 from #temp group by person,acttype
order by person

Answer 7

select person,acttype,sum(sj) from (select person,datediff(mi,begintime,endtime)sj,acttype from #temp)a
group by person,acttype order by personperson acttype (无列名)
1 上网 55
2 吃饭 30
2 上网 30
3 打游戏 -480

Answer 8

这个是一个标准的示例问题，书上有现成的 :) 先要分组滴 WITH StartTimes AS
(
  SELECT DISTINCT acttype, person, begintime 
  FROM #temp AS O
  WHERE NOT EXISTS
    (SELECT * FROM #temp AS I
     WHERE I.acttype = O.acttype
       AND I.person = O.person
       AND O.begintime > I.begintime
       AND O.begintime <= I.endtime)
),
EndTimes AS
(
  SELECT DISTINCT acttype, person, endtime 
  FROM #temp AS O
  WHERE NOT EXISTS
    (SELECT * FROM #temp AS I
     WHERE I.acttype = O.acttype
       AND I.person = O.person
       AND O.endtime >= I.begintime
       AND O.endtime < I.endtime)
),
SessionGroups AS
(
  SELECT acttype, person, begintime,    (SELECT MIN(endtime)
     FROM EndTimes AS EP
     WHERE EP.acttype = SP.acttype
       AND EP.person = SP.person
       AND endtime >= begintime) AS endtime
  FROM StartTimes AS SP
)
SELECT person,acttype,  begintime, endtime,datediff(mi,begintime,endtime) minutes
FROM SessionGroups
ORDER BY 1,2,3
结果： 
person acttype begintime endtime minutes 
1 上网 2007-09-24   20:00:00.000 2007-09-24   20:20:00.000 20 
1 上网 2007-09-24   20:30:00.000 2007-09-24   20:55:00.000 25 
2 吃饭 2007-09-24   20:30:00.000 2007-09-24   21:00:00.000 30 
2 上网 2007-09-24   20:00:00.000 2007-09-24   20:30:00.000 30 
3 打游戏 2007-09-24   20:00:00.000 2007-09-24   21:00:00.000 60 (5   行受影响)

Answer 9

统计当然就简单了，呵呵WITH StartTimes AS
(
  SELECT DISTINCT acttype, person, begintime
  FROM #temp AS O
  WHERE NOT EXISTS
    (SELECT * FROM #temp AS I
     WHERE I.acttype = O.acttype
       AND I.person = O.person
       AND O.begintime > I.begintime
       AND O.begintime <= I.endtime)
),
EndTimes AS
(
  SELECT DISTINCT acttype, person, endtime
  FROM #temp AS O
  WHERE NOT EXISTS
    (SELECT * FROM #temp AS I
     WHERE I.acttype = O.acttype
       AND I.person = O.person
       AND O.endtime >= I.begintime
       AND O.endtime < I.endtime)
),
SessionGroups AS
(
  SELECT acttype, person, begintime,    (SELECT MIN(endtime)
     FROM EndTimes AS EP
     WHERE EP.acttype = SP.acttype
       AND EP.person = SP.person
       AND endtime >= begintime) AS endtime
  FROM StartTimes AS SP
)
SELECT person,acttype,sum(datediff(mi,begintime,endtime)) minutes
FROM SessionGroups
GROUP BY person,acttype
ORDER BY 1,2
结果：
person acttype minutes
1 上网 45
2 吃饭 30
2 上网 30
3 打游戏 60(4 行受影响)

Answer 10

create   table   #temp( 
person   int,--人 
begintime   datetime,--开始时间 
endtime   datetime,--结束时间 
acttype   varchar(50),--做事类型 
re   varchar(100)--备注 
) 
insert   into   #temp   values(1,'9/24/2007   8:00:00   PM','9/24/2007   8:20:00   PM','上网','浏览a网站') 
insert   into   #temp   values(1,'9/24/2007   8:30:00   PM','9/24/2007   8:50:00   PM','上网','浏览b网站') 
insert   into   #temp   values(1,'9/24/2007   8:35:00   PM','9/24/2007   8:40:00   PM','上网','浏览c网站') 
insert   into   #temp   values(1,'9/24/2007   8:45:00   PM','9/24/2007   8:55:00   PM','上网','浏览d网站') insert   into   #temp   values(2,'9/24/2007   8:00:00   PM','9/24/2007   8:30:00   PM','上网','浏览x网站') 
insert   into   #temp   values(2,'9/24/2007   8:30:00   PM','9/24/2007   9:00:00   PM','吃饭','手抓饭') insert   into   #temp   values(3,'9/24/2007   8:00:00   PM','9/24/2007   12:00:00   PM','打游戏','魔兽') 
--------------------------------------------------------
红色部分应该为：
'9/25/2007   0:00:00  AM'这样才对吧。哪见过有时间是下午12点的。晕select person 人,acttype 类型,sum(时间差)时长 from
(select *,时间差=datediff(mi,begintime,endtime) from #temp)a
group by person,acttype order by person

Answer 11

不一样的，select cast('9/24/2007   12:00:00   PM' as datetime)
select cast('9/25/2007   0:00:00  AM' as datetime)
-----------------------
2007-09-24 12:00:00.000(1 行受影响)
-----------------------
2007-09-25 00:00:00.000(1 行受影响)

Answer 12

是各自的开始时间，不是 “上网时间”这个我是在 Inside SQL Server 2005 微软出版社的内幕系列中看到的

Answer 13

Grouping Overlaps Another request involving overlaps is to combine overlapping sessions with the same application and user into a single session. The next problem we'll look at requires you to collapse all overlapping sessions for each application and user into one session group, returning the application, user, start time, and end time of the session group. The purpose of such a request is to determine the amount of time a user was connected to an application, regardless of the number of simultaneous active sessions the user had. This solution to this problem would be especially helpful to service providers that allow multiple sessions at no extra charge.You might want to tackle the problem in steps: identify starting times of session groups, identify ending times of session groups, and then match each ending time to its corresponding starting time.To isolate starting times of session groups, you first need to come up with a logical way of identifying them. A start time S starts a group if no session (for the same app and usr) starts before S and continues until S or later. With this definition of a session group start time, if you have multiple identical start times, you will get them all. By applying DISTINCT, you will get only one occurrence of each unique start time. Here's the query that translates this logic to T-SQL, returning the output shown in 

Answer 14

In SQL Server 2000, you can use derived tables instead of CTEs. I find the solution using CTEs of 2005 much clearer.

Answer 15

create table #(id int,s int,e int)
insert into # select 1,1,2
insert into # select 2,1,3
insert into # select 3,4,5
insert into # select 4,4,6
DECLARE       cur    
CURSOR       FOR       select id,s,e from #
declare @id int,@s int,@e int,@nid int,@ns int,@ne int
open       cur
FETCH       NEXT       FROM       cur    
INTO       @id,@s,@e
WHILE       @@FETCH_STATUS       =       0
begin   
FETCH       NEXT       FROM       cur    
INTO      @nid,@ns,@ne
if ((@s>=@ns and @s<=@ne) or (@e>=@ns and @e<=@ne)
or (@ns>=@s and @ns<=@e) or (@ne>=@s and @ne<=@e))update # set 
s=case when @s>@ns then @ns else @s end,
e=case when @e>@ne then @e else @ne end
where id=@id or id=@nid
FETCH       NEXT       FROM       cur    
INTO       @id,@s,@e
end
CLOSE       cur
DEALLOCATE       cur  select * from #id s e
1 1 3
2 1 3
3 4 6
4 4 6随便写写，楼主看看

Answer 16

rubiki:好像凡是我回的贴都没人结贴，有点   没意思哈，呵呵，不过也无所谓了，反正是玩嘛haha ,我来结贴啦,可以实现我的要求.不知道大数据量效率如何呢,我过会试下

Answer 17

declare      @temp table( 
person   int,--人 
begintime   datetime,--开始时间 
endtime   datetime,--结束时间 
acttype   varchar(50),--做事类型 
re   varchar(100)--备注 
) 
insert   into   @temp   values(1,'9/24/2007   8:00:00   PM','9/24/2007   8:20:00   PM','上网','浏览a网站') 
insert   into   @temp   values(1,'9/24/2007   8:30:00   PM','9/24/2007   8:50:00   PM','上网','浏览b网站') 
insert   into   @temp   values(1,'9/24/2007   8:35:00   PM','9/24/2007   8:40:00   PM','上网','浏览c网站') 
insert   into   @temp   values(1,'9/24/2007   8:45:00   PM','9/24/2007   8:55:00   PM','上网','浏览d网站') insert   into   @temp   values(2,'9/24/2007   8:00:00   PM','9/24/2007   8:30:00   PM','上网','浏览x网站') 
insert   into   @temp   values(2,'9/24/2007   8:30:00   PM','9/24/2007   9:00:00   PM','吃饭','手抓饭') insert   into   @temp   values(3,'9/24/2007   8:00:00   PM','9/24/2007   12:00:00   PM','打游戏','魔兽') declare @p int,@a varchar(50),@b datetime,@e datetime
declare @p1 int,@a1 varchar(50),@maxt datetime,@s int
set @p1=0
set @a1 = ''
set @maxt= convert(datetime,'1999-01-01')declare @t table(p int,a varchar(50),t int)
insert @t select distinct person,acttype,0 from @temp 
DECLARE  cur CURSOR  FOR select person,acttype,begintime,endtime from @temp  order by person,acttype,begintime,endtime
open cur
FETCH  cur INTO  @p,@a,@b,@e
WHILE  @@FETCH_STATUS =  0
begin   
 if @p = @p1  and @a = @a1
 begin
   if @e > @maxt 
   begin 
     if @b < @maxt set @b = @maxt
     set @s = datediff(mi,@b,@e)
     set @maxt = @e
   end
   else set @s = 0
 end
 else
 begin
   set @p1 = @p
   set @a1 = @a
   set @s = datediff(mi,@b,@e)
   set @maxt = @e
 end
 update @t set t = t+ @s where p=@p1 and a = @a1
 FETCH  NEXT  FROM   cur  INTO   @p,@a,@b,@e
end
CLOSE       cur
DEALLOCATE       cur  
select * from @t
结果
1 上网 45
2 吃饭 30
2 上网 30
3 打游戏 -480最后一条记录时间不对

Answer 18

SELECT person,acttype,
sum(DATEDIFF(n,begintime,endtime)) as pertime
FROM #temp
GROUP BY person,acttype
order by person