SQL 查询

Question

这是数据库表存储的数据格式
[img]http://dl.iteye.com/upload/attachment/0070/9804/b5cdcc94-a84e-30e8-84bc-84506f6cda32.jpg[/img]
能不能通过一条语句查询得到如下的数据格式。
他们说这种形式是查询不出来的
[img]http://dl.iteye.com/upload/attachment/0070/9866/d19a0754-ea0c-3d22-82f9-670ff806d6ff.jpg[/img]
那这种形式的能否查询出来不，SQL语句该怎么写呢！基于SQLServer或者oracle都可以
[img]http://dl.iteye.com/upload/attachment/0070/9864/0c2eea66-760e-3d08-afd6-a9c570738d7c.jpg[/img]

Answer 1

这是oracle实现：
[code="java"]
with test as(
select 1 A,2 B,4 C from dual
union all
select 1,2,5 from dual
union all
select 1,3,6 from dual
union all
select 1,3,7 from dual
)
select
decode(lag(a,1) over(partition by a order by b,c),a,NULL,a) new_a,
decode(lag(b,1) over(partition by b order by c),b,NULL,b) new_b,
c
from test
order by a,b
[/code]