oracle 分组排序取前几行，并把数据行转列

完成的SQL

SELECT ooefl003 as 成本中心,
max(case when RN=1 then sfaadocno else ' ' end) as 在制工单,
max(case when RN=1 then imaal001||':'||imaal003 else ' ' end) as 在制工单产品,
max(case when RN=1 then SFAA019 else null end) as 在制开工日期,
max(case when RN=1 then SFAA012-SFAA050 else null end) as 在制工单欠量,
max(case when RN=2 then sfaadocno else ' ' end ) as 下笔工单,
max(case when RN=2 then imaal001||':'||imaal003 else ' ' end) as 下笔工单产品,
max(case when RN=2 then SFAA019 else null end) as 下笔开工日期,
max(case when RN=2 then SFAA012-SFAA050 else null end) as 下笔工单欠量,
max(case when RN=1 then SFAA019 else null end)-max(case when RN=2 then SFAA019 else null end) AS 差额

FROM  (
	SELECT t1.sfaa068,sfaaent,sfaa010,SFAA019,SFAA050,SFAA012，
	t1.sfaadocno,
	ROW_NUMBER() OVER(PARTITION BY t1.sfaa068 ORDER BY t1.sfaa019) AS RN  
	FROM SFAA_T t1
	where  t1.sfaaent=8 and t1.sfaasite='据点'
		AND t1.SFAA050<t1.SFAA012
		AND t1.SFAASTUS='F'
		and t1.sfaa065=0
)  
left join OOEFL_T on ooeflent=sfaaent and ooefl001=sfaa068 and ooefl002='zh_CN'
left join IMAAL_T on imaalent=sfaaent and sfaa010=imaal001 and imaal002='zh_CN'
WHERE RN <= 2   
group by sfaa068,ooefl003

分组取数SQL

SELECT *  
FROM  (SELECT 分组的字段名, ROW_NUMBER() OVER(PARTITION BY 分组的字段名 ORDER BY 排序的字段名) AS RN   
       FROM 表名)  
WHERE RN <= 10   

分组取数后按照特定条件行值转成列

通过group by 与MAX(CASE WHEN * THEN * end ) 实现

SELECT ooefl003 as 成本中心,
max(case when RN=1 then sfaadocno else ' ' end) as 在制工单
FROM  (
	...
)  
group by ooefl003