如何将秩()前2的结果改进为唯一的列

Question

下面是为在自己的列中显示结果1和2而编写的SQL 9(i)代码。有更有效的方法来写这个吗？

select 
  sc1.COIL as COIL1
, sc1.DEFECT as DEFECT1
, sc2.DEFECT as DEFECT2
FROM
      (select
            COIL, DEFECT
            , RANK() OVER(PARTITION BY COIL ORDER BY WEIGHT DESC) RNK
            from NOVELIS.F406, NOVELIS.F408 where   F406_DEFECT_CODE = F408_REJECT_CODE
            GROUP BY COIL, DEFECT
        )sc1
    , (select
            COIL, DEFECT
            , RANK() OVER(PARTITION BY COIL ORDER BY WEIGHT DESC) RNK
            from NOVELIS.F406, NOVELIS.F408 where   F406_DEFECT_CODE = F408_REJECT_CODE
            GROUP BY COIL, DEFECT
        )sc2

WHERE 
sc1.RNK = 1 
and sc2.RNK = 2 
and sc1.COIL = sc2.COIL 

Answer 1

可以使用条件聚合并使用1派生表。

SELECT    sc1.COIL AS COIL1,
          MAX(CASE WHEN RNK = 1 THEN sc1.DEFECT END) AS DEFECT1,
          MAX(CASE WHEN RNK = 2 THEN sc1.DEFECT END) AS DEFECT2
FROM
(
    SELECT COIL,
           DEFECT,
           RANK() OVER(PARTITION BY COIL ORDER BY WEIGHT DESC) RNK
    FROM   NOVELIS.F406
           --practice using joins
           INNER JOIN NOVELIS.F408 ON F406_DEFECT_CODE = F408_REJECT_CODE
    --not sure you need the group by here
) sc1
GROUP BY sc1.COIL

Answer 2

为避免两次阅读表格，只需阅读一次，并将结果转出来：-

select 
coil,
max (case when rnk=1 then defect else null end) defect1,
max (case when rnk=2 then defect else null end) defect2
FROM (
select 
COIL, DEFECT, 
RANK() OVER(PARTITION BY COIL ORDER BY WEIGHT DESC) RNK
from NOVELIS.F406
inner join NOVELIS.F408 on F406_DEFECT_CODE = F408_REJECT_CODE
) sc
WHERE 
sc.RNK <= 2 

group by coil