菜鸟求助!!!

炸吧杰 2018-03-26 04:16:45


怎么用oracle sql 语句将表一 根据时间 行专列 成表二的样子

菜鸟一枚。忘大神指导!!感激不尽!!
...全文
555 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
bigdata-sb 2018-06-26
  • 打赏
  • 举报
回复


with v_base as (
select '设备1' as xjd , '<1' as xjbz, date '2018-02-02' as dt from dual union all
select '设备1' as xjd , '<1' as xjbz, date '2018-02-03' as dt from dual union all
select '设备1' as xjd , '<1' as xjbz, date '2018-02-04' as dt from dual union all
select '设备1' as xjd , '<1' as xjbz, date '2018-02-05' as dt from dual union all
select '设备1' as xjd , '<1' as xjbz, date '2018-02-06' as dt from dual)
select
XJD,
max(xjbz) as xjbz,
max(case when rn = 1 then dt end) as "巡检时间(t1)",
max(case when rn = 2 then dt end) as "巡检时间(t2)",
max(case when rn = 3 then dt end) as "巡检时间(t3)",
max(case when rn = 4 then dt end) as "巡检时间(t4)",
max(case when rn = 5 then dt end) as "巡检时间(t5)"
from (select t.*, row_number() over(order by dt) as rn from v_base t) v1
group by XJD
bigdata-sb 2018-06-26
  • 打赏
  • 举报
回复
with v_base as (
select '设备1' as xjd , '<1' as xjbz, date '2018-02-02' as dt from dual union all
select '设备1' as xjd , '<1' as xjbz, date '2018-02-03' as dt from dual union all
select '设备1' as xjd , '<1' as xjbz, date '2018-02-04' as dt from dual union all
select '设备1' as xjd , '<1' as xjbz, date '2018-02-05' as dt from dual union all
select '设备1' as xjd , '<1' as xjbz, date '2018-02-06' as dt from dual)
select *
from (select t.*, row_number() over(order by dt) as rn from v_base t) v1
pivot(max(dt)
for rn in(1 as "巡检时间(t1)",
2 as "巡检时间(t2)",
3 as "巡检时间(t3)",
4 as "巡检时间(t4)",
5 as "巡检时间(t5)"))
qq_41888172 2018-03-27
  • 打赏
  • 举报
回复
select 巡检点,巡检标准,xx1 巡检时间,xx2 值 from t2 unpivot(xx2 FOR xx1 IN (T1,T2....Tn) ) group by 巡检点,巡检标准
liu志坚 2018-03-26
  • 打赏
  • 举报
回复
with t1 as (select '设备1' as "检查点", '-1' as "巡检标准", to_char(sysdate - 1, 'YYYYMMDD') as "巡检时间" from dual union all select '设备1' as "检查点", '-1' as "巡检标准", to_char(sysdate - 2, 'YYYYMMDD') as "巡检时间" from dual union all select '设备1' as "检查点", '-1' as "巡检标准", to_char(sysdate - 3, 'YYYYMMDD') as "巡检时间" from dual union all select '设备1' as "检查点", '-1' as "巡检标准", to_char(sysdate - 4, 'YYYYMMDD') as "巡检时间" from dual union all select '设备1' as "检查点", '-1' as "巡检标准", to_char(sysdate - 5, 'YYYYMMDD') as "巡检时间" from dual union all select '设备2' as "检查点", '-1' as "巡检标准", to_char(sysdate - 5, 'YYYYMMDD') as "巡检时间" from dual) select * from t1 pivot(avg("巡检时间") for "巡检时间" in('20180321' as "巡检时间t1", '20180322' as "巡检时间t2", '20180323' as "巡检时间t3", '20180324' as "巡检时间t4", '20180325' as "巡检时间t5"));
liu志坚 2018-03-26
  • 打赏
  • 举报
回复
可以用这个 pivot 参考:https://blog.csdn.net/seandba/article/details/72730657

17,078

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧