100分求一个数据库查询语句(ORACLE 11g)

[Crisitaaano]

一张表tabl1 如下：

epqname createtime endtime

设备1 2014.10.01 11:00:00 2014.10.01 13:22:00
设备2 2014.10.27 11:00:00 2014.10.27 14:10:00
设备3 2014.11.11 14:00:00 2014.11.11 15:00:00
设备4 2014.11.29 11:00:00 2014.11.29 16:00:00

我想查询出来结果，显示出，每个设备每天运转的小时数，不足1小时的，算1小时(1小时25分，1小时10分钟 都算2小时)
将日期作为列名，显示当天运转的时间。

epqname 2014.10.01 2014.10.27 2014.11.11 2014.11.29
设备1 3 0 0 0
设备2 0 4 0 0
设备3 0 0 1 0
设备4 0 0 0 5

[不写代码的钦]

如果你的createtime和endtime是date类型的话（如果不是要用to_date转化一下数据类型），借鉴下面代码。 select epqname, sum(case when to_char(t.createtime,'yyyy.mm.dd')='2014.10.01' then ceil((t.endtime-t.createtime)*24) else 0 end) "2014.10.01", sum(case when to_char(t.createtime,'yyyy.mm.dd')='2014.10.27' then ceil((t.endtime-t.createtime)*24) else 0 end) "2014.10.27", sum(case when to_char(t.createtime,'yyyy.mm.dd')='2014.11.11' then ceil((t.endtime-t.createtime)*24) else 0 end) "2014.11.11", sum(case when to_char(t.createtime,'yyyy.mm.dd')='2014.11.29' then ceil((t.endtime-t.createtime)*24) else 0 end) "2014.11.29" from tabl1 t group by epqname order by epqname;

[doingendlessly]

一个设备同一天可能有两条记录吗？比如设备1 从10点-12点运行，13点-15点又运行。

[tracy敬]

引用 3 楼 bw555 的回复:

先给你写一个不跨天的情况

with T AS (
SELECT epqname,TRUNC(createtime) D,CEIL((ENDTIME-createtime) *24) AS S
FROM tabl1)
SELECT epqname,
SUM(DECODE(D,TO_DATE('2014.10.01','YYYY.MM.DD'),S)) AS "2014.10.01",
SUM(DECODE(D,TO_DATE('2014.10.27','YYYY.MM.DD'),S)) AS "2014.10.27",
SUM(DECODE(D,TO_DATE('2014.11.11','YYYY.MM.DD'),S)) AS "2014.11.11",
SUM(DECODE(D,TO_DATE('2014.11.29','YYYY.MM.DD'),S)) AS "2014.11.29"
FROM T
GROUP BY epqname

赞

[Crisitaaano]

1. 一个设备，一天可能有多条记录的 设备的开始结束时间会本约束在一天内，就是，结束时间不会超过23：59分 如果是跨越天的情况，就会切割插入，例如 22：00到第二天07：00 会存储为 22：00-23：59和00：00-07：00，两段 2. 问题是，我要动态的时间列 直接 写死列名是不可以的把，是根据数据库的时间来的 数据不可能固定这几天的，每个月的，每天的，都是有的，日期列不定，查哪个个月的就显示哪个月的数据 这样的数据查询语句怎么写呢

[bw555]

一、不确定列直接查询是没办法实现的提供两个方案 1、动态拼接sql语句，然后生成视图，然后select * from 视图 2、动态拼接sql语句，利用游标，将结果循环打印输出 二、存在跨天的情况，需要进行拆分 用下面的语句的查询结果代替原表，未测试，你先执行下看看效果吧

select epqname,
greatest(trunc(createtime)+level-1,createtime) createtime,
least(trunc(createtime)+level,endtime) endtime
from tabl1
connect by trunc(createtime)+level-1<=trunc(endtime)
and rowid=prior rowid
and prior dbms_random.value is not null

[Crisitaaano]

不行啊，效果不一致啊，不是日期作为动态列啊

能达到
EQPNAME 2014.11.27
设备01 1
类似这样的效果，显示每天、每设备的运行小时数的

[Crisitaaano]

可以认为是当天结束的，因为，跨天的，会分开插入的，时间跨度最大是00：00 ---23：59

[bw555]

动态行转不定列的方法，在9#已经说明，采用视图实现的方式可以参考下面链接，大同小异 参考3#固定列数的sql写法，拼接sql语句，然后动态创建视图，然后select * from 视图 oracle行转列（动态行转不定列) 目前的oracle版本里没有任何可以直接的行转动态列方法，只能借助视图、游标之类的工具间接实现 如果从xml中select 可以借助pivot any的写法实现

[bw555]

都是当天开始当天结束吗？有没有跨天的，比如从昨天晚上10点干到今天上午8点 这种情况有没有复杂度可是差很多的

[sych888]

case ... when ...

[bw555]

先给你写一个不跨天的情况

with T AS (
SELECT epqname,TRUNC(createtime) D,CEIL((ENDTIME-createtime) *24) AS S
FROM tabl1)
SELECT epqname,
SUM(DECODE(D,TO_DATE('2014.10.01','YYYY.MM.DD'),S)) AS "2014.10.01",
SUM(DECODE(D,TO_DATE('2014.10.27','YYYY.MM.DD'),S)) AS "2014.10.27",
SUM(DECODE(D,TO_DATE('2014.11.11','YYYY.MM.DD'),S)) AS "2014.11.11",
SUM(DECODE(D,TO_DATE('2014.11.29','YYYY.MM.DD'),S)) AS "2014.11.29"
FROM T
GROUP BY epqname

[sych888]

ceil(to_number(endtime -createtime)*24)