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

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 '完成时间(支付完成)',
) 根据不同的时间字段,计算每个时间点(精确到小时)的预约,接单,完成加油,创建,修改,完成量,查询出来需要有连续的日期,时间点拜托各位大神急用运行速度最好要快点

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

DH5211314
DH5211314 好的 谢谢
6 个月之前 回复
weixin_45069503
ZJHZ_叶 回复: 连续日期 select date_add(str_to_date('20190501', '%Y%m%d'), interval aa*10+bb day) from (select 0 aa union select 1 union .. union select 9) a, (select 0 bb union select 1 union .. union select 9) b where aa*10+bb<datediff('20190524','20190501') 日期差不确定的话,不太好弄
8 个月之前 回复
weixin_45069503
ZJHZ_叶 回复DH5211314: left join 里大概可以这么写:SELECT time, sum(CASE type WHEN 'CREATE_TIME' THEN totle ELSE 0 END ) CREATE_TIME, sum(CASE type WHEN 'UPDATE_TIME' THEN totle ELSE 0 END ) UPDATE_TIME, sum(CASE type WHEN 'SNATCH_TIME' THEN totle ELSE 0 END ) SNATCH_TIME, sum(CASE type WHEN 'SCHEDULED_TIME' THEN totle ELSE 0 END ) SCHEDULED_TIME, sum(CASE type WHEN 'FINISH_TIME' THEN totle ELSE 0 END ) FINISH_TIME, sum(CASE type WHEN 'FINISH_PAY_TIME' THEN totle ELSE 0 END ) FINISH_PAY_TIME from ( select type,trunc(time) time,count(*) totle from ( select 'SCHEDULED_TIME' type,SCHEDULED_TIME time from table_test1 union all select 'SNATCH_TIME' type,SNATCH_TIME from table_test1 union all select 'FINISH_TIME' type,FINISH_TIME from table_test1 union all select 'CREATE_TIME' type,CREATE_TIME from table_test1 union all select 'UPDATE_TIME' type,UPDATE_TIME from table_test1 union all select 'FINISH_PAY_TIME' type,FINISH_PAY_TIME from table_test1 ) where trunc(time) between to_date('20190501','yyyymmdd') and
8 个月之前 回复
DH5211314
DH5211314 大佬,我用的是MySQL,把这个能改成MySQL的吗 谢谢 啦 大佬
8 个月之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问