版本:Oracle 8.0
数据表结构 WO,OPERATION,FUNCTION_,QTY
主键为:WO与OPERATION,现表中有数据如下
WO OPERATION FUNCTION_ QTY
24943-0002 10 ROUGHCUT 6027
24943-0002 30 PRINTING 6027
24943-0002 50 PUNCH 8078
现在要得到以下视图
WO OPERATION_1 FUNCTION_1 QTY_1 OPERATION_2 FUNCTION_2 QTY_2 OPERATION_3 FUNCTION_3 QTY_3
24943-0002 10 ROUGHCUT 6027 30 PRINTING 6027 50 PUNCH 8078不知道能不能用SQL实现,如果可以的话这个SQL怎么写???
数据表结构 WO,OPERATION,FUNCTION_,QTY
主键为:WO与OPERATION,现表中有数据如下
WO OPERATION FUNCTION_ QTY
24943-0002 10 ROUGHCUT 6027
24943-0002 30 PRINTING 6027
24943-0002 50 PUNCH 8078
现在要得到以下视图
WO OPERATION_1 FUNCTION_1 QTY_1 OPERATION_2 FUNCTION_2 QTY_2 OPERATION_3 FUNCTION_3 QTY_3
24943-0002 10 ROUGHCUT 6027 30 PRINTING 6027 50 PUNCH 8078不知道能不能用SQL实现,如果可以的话这个SQL怎么写???
select wo,max(decode(operation,10,operation))operation_1,
max(decode(operation,10,function))function_1,
max(decode(operation,10,QTY))QTY_1,
max(decode(operation,30,operation))operation_2,
max(decode(operation,30,function))function_2,
max(decode(operation,30,QTY))QTY_2,
max(decode(operation,50,operation))operation_3,
max(decode(operation,50,function))function_3,
max(decode(operation,50,QTY))QTY_3
from tt
group by wo如果不止3个而是更多的话,要按这个方式添加字段
如果不确定operation的值,可以嵌一个子查询添加一个分析函数来实现
如果不固定,嵌套一个子查询,用分析函数,形如
select wo,max(decode(dk,1,operation))operation_1,
max(decode(dk,1,function))function_1,
max(decode(dk,1,QTY))QTY_1,
max(decode(dk,2,operation))operation_2,
max(decode(dk,2,function))function_2,
max(decode(dk,2,QTY))QTY_2,
max(decode(dk,3,operation))operation_3,
max(decode(dk,3,function))function_3,
max(decode(dk,3,QTY))QTY_3
from (select tt.*,dense_rank()over(order by operation)dk from tt)
group by wo