关于一张表内多个时间字段关联问题下面粘上要查询的表结构及查询说明如果没有方法的话请留言这问题无解

Question

CREATE TABLE table (
id int(11) NOT NULL AUTO_INCREMENT COMMENT '订单id',
scheduled_time datetime DEFAULT NULL COMMENT '预约时间',
snatch_time datetime DEFAULT NULL COMMENT '接单时间',
finish_time datetime DEFAULT NULL COMMENT '完成时间（加油完成）',
create_time datetime DEFAULT NULL COMMENT '创建时间',
update_time datetime DEFAULT NULL COMMENT '修改时间',
finish_pay_time datetime DEFAULT NULL COMMENT '完成时间（支付完成）',
) 根据不同的时间字段，计算每个时间点（精确到小时）的预约，接单，完成加油，创建，修改，完成量，查询出来需要有连续的日期，时间点拜托各位大神急用运行速度最好要快点

Answer 1

oracle ： 不知道是不是你想要的
select time,nvl(scheduled_time,0),nvl(snatch_time,0),nvl(finish_time,0),nvl(create_time,0),nvl(update_time,0),nvl(finish_pay_time,0)
from
(
select to_date('20190501','yyyymmdd')+rownum-1 time from dual connect by rownum<=to_date('20190524','yyyymmdd')-to_date('20190501','yyyymmdd')+1
)left join
(
select *
from (select trunc(shijian) shijian, leixing, count(*) totle
from (select leixing, shijian
from table_test1 unpivot(shijian for leixing in (scheduled_time, snatch_time, finish_time, create_time, update_time, finish_pay_time)))
where trunc(shijian) between to_date('20190501','yyyymmdd') and to_date('20190524','yyyymmdd')
group by leixing, trunc(shijian)) pivot(sum(totle) for leixing in ('SNATCH_TIME' SNATCH_TIME, 'CREATE_TIME' CREATE_TIME, 'SCHEDULED_TIME' SCHEDULED_TIME, 'FINISH_TIME' FINISH_TIME, 'FINISH_PAY_TIME' FINISH_PAY_TIME, 'UPDATE_TIME' UPDATE_TIME))
) on time=shijian

order by 1 desc