我现在有一个表 T_Value(DAY_ID,METR,VALUE,ENAME,RESOURCE)
表中插入的内容如下
20110701 000012 0.23 S_FR resource1
20110701 000013 0.24 S_FR resource1
20110702 000012 0.23 S_FR1 resource1
20110702 000013 0.67 S_FR1 resource1
20110703 000012 0.78 S_FR2 resource2
20110703 000013 0.34 S_FR2 resource2
20110704 000012 0.09 S_FR3 resource3
20110704 000013 0.17 S_FR3 resource3
我现在遇到的问题是我想让表查询出来的时候显示如下格式：
20110701 S_FR resource1 0.23 0.24
20110701 S_FR1 resource2 0.23 0.67
20110701 S_FR2 resource3 0.78 0.34
20110701 S_FR3 resource4 0.09 0.17
我sql语句该怎么写呢，请教高手了
------解决方案--------------------
- SQL code
with table_a as( select '20110701' DAY_ID, '000012' METR, 0.23 VALUE, 'S_FR' ENAME, 'resource1' RESOURCE1 from dual union all select '20110701' DAY_ID, '000013' METR, 0.24 VALUE, 'S_FR' ENAME, 'resource1' RESOURCE1 from dual union all select '20110702' DAY_ID, '000012' METR, 0.23 VALUE, 'S_FR1' ENAME, 'resource1' RESOURCE1 from dual union all select '20110702' DAY_ID, '000013' METR, 0.67 VALUE, 'S_FR1' ENAME, 'resource1' RESOURCE1 from dual)select * from (select day_id,ename,RESOURCE1,value,lead(value) over(partition by day_id order by value) lead_valuefrom table_a ) twhere t.lead_value is not null
------解决方案--------------------