求一条SQL语句！高分！

Question

一台设备，会不定期的启动和停运
现在我要按月统计该设备的启动次数和停运次数和运行分钟数（各个自然月要按凌晨零点划分界限），如果能做到按设备名称和启停人分组就更好了，请各位大拿指导，在线等！谢谢！
数据库为oracle，数据结构如下：设备名称 启停动作 日期时间          启停人
#2机 启 2009-8-30 0:15:00 张三
#2机 停 2009-8-31 6:35:00 李四
#1机 启 2009-9-1 3:13:00 张三
#1机 停 2009-9-1 20:22:00 李四
#2机 启 2009-9-3 0:18:00 张三
#2机 停 2009-9-4 23:43:00 李四
#1机 启 2009-9-5 4:25:00 张三
#1机 停 2009-9-6 2:35:00 李四

Answer 1

select 设备名称,启停人,trunc(日期时间, 'mm'), count(decode(启停动作, '启', 1,0)) "启次数"，停次数 count(decode(启停动作, '停', 1,0)) from t group by 设备名称,启停人,trunc(日期时间, 'mm');

Answer 2

掉了运行分钟数sum(decode(启停动作, '启', to_date('2000', 'yyyy')-日期时间, 日期时间-to_date('2000', 'yyyy')))*24*60 "运行分钟数"

Answer 3

select 设备名称,启停人,trunc(日期时间, 'mm'), sum(decode(启停动作, '启', 1,0)) "启次数", sum(decode(启停动作, '停', 1,0)) "停次数", sum(decode(启停动作, '启', to_date('2000', 'yyyy')-日期时间, 日期时间-to_date('2000', 'yyyy')))*24*60 "运行分钟数" from t group by 设备名称,启停人,trunc(日期时间, 'mm');

Answer 4

select to_char(日期时间,'YYYY-MM')日期,设备名称,启停人,
       count(case 启停动作 when 启 then 1 end)启动次数,
       count(case 启停动作 when 停 then 1 end)停止次数
from  tab1
group by to_char(日期时间,'YYYY-MM'),设备名称,启停人
order by 日期,设备名称,启停人

Answer 5

select to_char(日期时间,'YYYY-MM')日期,设备名称,启停人,
       count(case 启停动作 when '启' then 1 end)启动次数,
       count(case 启停动作 when '停' then 1 end)停止次数
from  tab1
group by to_char(日期时间,'YYYY-MM'),设备名称,启停人
order by 日期,设备名称,启停人

Answer 6

select 设备名称,to_char(日期时间,'YYYY-MM')日期,
       count(case 启停动作 when '启' then 1 end)启动次数,
       count(case 启停动作 when '停' then 1 end)停止次数,
       round(sum(运行时间)*24*60,2)||'分钟' 运行时间 from(
select a.*,
  
  case when 启停动作='停' then 日期时间-lag(日期时间,2)over(partition by 设备名称 order by 日期时间) end 运行时间
from tab1 a)
group by 设备名称,to_char(日期时间,'YYYY-MM')日期

Answer 7

更改下
select 设备名称,to_char(日期时间,'YYYY-MM')日期,
       count(case 启停动作 when '启' then 1 end)启动次数,
       count(case 启停动作 when '停' then 1 end)停止次数,
       round(sum(运行时间)*24*60,2)||'分钟' 运行时间 from(
select a.*,
  
  case when 启停动作='停' then 日期时间-lag(日期时间)over(partition by 设备名称 order by 日期时间) end 运行时间
from tab1 a)
group by 设备名称,to_char(日期时间,'YYYY-MM')

Answer 8

从当月0点开始算啊
select 设备名称,to_char(日期时间,'YYYY-MM')日期,
       count(case 启停动作 when '启' then 1 end)启动次数,
       count(case 启停动作 when '停' then 1 end)停止次数,
       round(sum(运行时间)*24*60,2)||'分钟' 运行时间 from(
select a.*,
  
  case when 启停动作='停' then 日期时间-
    case when trunc(lag(日期时间)over(partition by 设备名称 order by 日期时间),'MM')=trunc(日期时间)
     then lag(日期时间)over(partition by 设备名称 order by 日期时间)
     else trunc(日期时间,'MM')end end 运行时间
from tab1 a)
group by 设备名称,to_char(日期时间,'YYYY-MM')

Answer 9

count(case 启停动作when 启then1end)
等价于
sum(decode(启停动作, ‘启’,1,0))好像如果要算启动时间，不能用启停人来分组了，只能用设备了。

Answer 10

OK
这个应该可以了
select 设备名称,to_char(日期时间,'YYYY-MM') 日期,
  round((nvl(sum(运行时间1),0)+nvl(sum(运行时间2),0))*24*60)||'分钟' 运行时间 from(
   select 设备名称,日期时间,
      decode( 启停动作,'停' , 日期时间-case when trunc(lg,'MM')=trunc(日期时间,'MM') then lg else trunc(日期时间,'MM') end) 运行时间1,
      decode( 启停动作,'启' , case when trunc(ld,'MM')<>trunc(日期时间,'MM') then trunc(ld,'MM')-日期时间  end) 运行时间2
      from(
   select a.*,
      lag(日期时间)over(partition by 设备名称 order by 日期时间)lg,
      lead(日期时间)over(partition by 设备名称 order by 日期时间)ld
   from tab1 a))
group by 设备名称,to_char(日期时间,'YYYY-MM')
order by 设备名称,日期;

Answer 11

楼上正确。create table device
( 设备名称 varchar2(10),
  启停动作 char(2),
  日期时间 date,
  启停人   varchar2(20)); alter session set nls_date_format='yyyy-mm-dd h24:mi:ss';insert into device values('#2机','启','2009-8-30 0:15:00','张三');
insert into device values('#2机','停','2009-8-31 6:35:00','李四');
insert into device values('#1机','启','2009-9-1 3:13:00','张三');
insert into device values('#1机','停','2009-9-1 20:22:00','李四');
insert into device values('#2机','启','2009-9-3 0:18:00','张三');
insert into device values('#2机','停','2009-9-4 23:43:00','李四');
insert into device values('#1机','启','2009-9-5 4:25:00','张三');
insert into device values('#1机','停','2009-9-6 2:35:00','李四');
SQL> select *from device;设备名称   启 日期时间            启停人
---------- -- ------------------- -------------------
#2机       启 2009-08-30 00:15:00 张三
#2机       停 2009-08-31 06:35:00 李四
#1机       启 2009-09-01 03:13:00 张三
#1机       停 2009-09-01 20:22:00 李四
#2机       启 2009-09-03 00:18:00 张三
#2机       停 2009-09-04 23:43:00 李四
#1机       启 2009-09-05 04:25:00 张三
#1机       停 2009-09-06 02:35:00 李四
已选择8行。SQL> select 设备名称,to_char(日期时间,'YYYY-MM') 日期,
  2    round((nvl(sum(运行时间1),0)+nvl(sum(运行时间2),0))*24*60)||'分钟' 运行时间,
  3    count(运行时间1) 停次数,count(*)-count(运行时间1) 启动次数 from(
  4     select 设备名称,日期时间,
  5        decode( 启停动作,'停' , 日期时间-case when trunc(lg,'MM')=trunc(日期时间,'MM') then lg else trunc(日期时间,'MM') end) 运行时间1,
  6        decode( 启停动作,'启' , case when trunc(ld,'MM')<>trunc(日期时间,'MM') then trunc(ld,'MM')-日期时间  end) 运行时间2
  7        from(
  8     select a.*,
  9        lag(日期时间)over(partition by 设备名称 order by 日期时间)lg,
 10        lead(日期时间)over(partition by 设备名称 order by 日期时间)ld
 11     from device a))
 12  group by 设备名称,to_char(日期时间,'YYYY-MM')
 13  order by 设备名称,日期;设备名称   日期    运行时间                                         停次数   启动次数
---------- ------- -------------------------------------------- ---------- ----------
#1机       2009-09 2359分钟                                              2          2
#2机       2009-08 1820分钟                                              1          1
#2机       2009-09 2845分钟                                              1          1