使用从较少空值到无空值的列对行进行排序

人气:1,034 发布:2022-10-16 标签: sql oracle sql-order-by sql-null oracle19c

问题描述

大家好,我有下一个问题:

Hello guys i have the next problem:

有这个查询:

CREATE SEQUENCE SEQ_prices MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 2206 NOCACHE  ORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL;


CREATE TABLE PRICES_INFO ( 
     ID NUMBER(19,0) DEFAULT SEQ_prices.nextval  NOT NULL
    ,QUARTER VARCHAR2(256 CHAR)
    ,COD_T1 VARCHAR2(256 CHAR)
    ,COD_T2 VARCHAR2(256 CHAR)
    ,COD_T3 VARCHAR2(256 CHAR)
    ,COD_T4 NUMBER(19,0)
    ,COD_T5 VARCHAR2(256 CHAR)
    ,COD_T6 VARCHAR2(256 CHAR)
    ,COD_T7 NUMBER(19,0)
    ,COD_T8 VARCHAR2(256 CHAR)
    ,COD_T9 VARCHAR2(256 CHAR)
    ,PREC_T1    NUMBER(19,6)
    ,PREC_T2    NUMBER(19,6)
    ,PREC_T3    NUMBER(19,6)
    ,PREC_T4    NUMBER(19,6)
    ,PREC_T5    NUMBER(19,6)
    ,PREC_T6    NUMBER(19,6)
    ,PREC_T7    NUMBER(19,6)
    ,PREC_T8    NUMBER(19,6)
    ,PREC_T9    NUMBER(19,6)
);

还有那些插入:

insert into prices_info (quarter, cod_t1, cod_t2, cod_t3, cod_t4, cod_t5, cod_t6, cod_t7, cod_t8, cod_t9, prec_t1, prec_t2, prec_t3, prec_t4, prec_t5, prec_t6, prec_t7, prec_t8, prec_t9) values ('Q1', 'PEUY', null, null, 856, null, null, 711, 'TZRS', null, 98.1, null, 12.69, 83.09, 77.32, null, null, 13.3, null);
insert into prices_info (quarter, cod_t1, cod_t2, cod_t3, cod_t4, cod_t5, cod_t6, cod_t7, cod_t8, cod_t9, prec_t1, prec_t2, prec_t3, prec_t4, prec_t5, prec_t6, prec_t7, prec_t8, prec_t9) values ('Q1', 'XWIK', 'EZYI', 'GJJL', null, null, 'KQMH', 771, 'FENL', 'CUPQ', null, 75.19, 61.56, null, 72.09, null, null, 54.32, 66.32);
insert into prices_info (quarter, cod_t1, cod_t2, cod_t3, cod_t4, cod_t5, cod_t6, cod_t7, cod_t8, cod_t9, prec_t1, prec_t2, prec_t3, prec_t4, prec_t5, prec_t6, prec_t7, prec_t8, prec_t9) values ('Q1', null, 'QUCJ', 'PZEG', 511, 'KWVY', 'LUPZ', 725, null, null, null, 89.12, null, 32.15, 20.43, null, null, 48.21, 58.19);
insert into prices_info (quarter, cod_t1, cod_t2, cod_t3, cod_t4, cod_t5, cod_t6, cod_t7, cod_t8, cod_t9, prec_t1, prec_t2, prec_t3, prec_t4, prec_t5, prec_t6, prec_t7, prec_t8, prec_t9) values ('Q1', null, null, 'HDWM', null, null, null, 865, null, 'ZTQA', 98.16, null, null, 88.48, null, 12.68, null, null, 23.04);
insert into prices_info (quarter, cod_t1, cod_t2, cod_t3, cod_t4, cod_t5, cod_t6, cod_t7, cod_t8, cod_t9, prec_t1, prec_t2, prec_t3, prec_t4, prec_t5, prec_t6, prec_t7, prec_t8, prec_t9) values ('Q1', 'TVDB', null, 'LCVE', null, 'OEDD', null, null, 'JQTL', 'CWFH', null, 95.98, null, 56.02, 26.02, null, null, null, 53.9);
insert into prices_info (quarter, cod_t1, cod_t2, cod_t3, cod_t4, cod_t5, cod_t6, cod_t7, cod_t8, cod_t9, prec_t1, prec_t2, prec_t3, prec_t4, prec_t5, prec_t6, prec_t7, prec_t8, prec_t9) values ('Q1', 'QODQ', 'YCGT', null, 289, null, 'GSHJ', null, null, 'PXGZ', 44.8, 58.98, 83.21, 22.77, null, null, 98.47, 66.39, 41.09);
insert into prices_info (quarter, cod_t1, cod_t2, cod_t3, cod_t4, cod_t5, cod_t6, cod_t7, cod_t8, cod_t9, prec_t1, prec_t2, prec_t3, prec_t4, prec_t5, prec_t6, prec_t7, prec_t8, prec_t9) values ('Q1', 'UNJU', null, 'DQNN', null, 'CQSL', null, null, null, null, 31.43, null, 81.36, null, 37.27, 58.15, null, null, null);
insert into prices_info (quarter, cod_t1, cod_t2, cod_t3, cod_t4, cod_t5, cod_t6, cod_t7, cod_t8, cod_t9, prec_t1, prec_t2, prec_t3, prec_t4, prec_t5, prec_t6, prec_t7, prec_t8, prec_t9) values ('Q1', null, 'XDMW', 'QLVX', 995, 'UGWE', null, 593, 'QPJT', null, null, 26, 32.89, 60.81, null, null, null, null, null);
insert into prices_info (quarter, cod_t1, cod_t2, cod_t3, cod_t4, cod_t5, cod_t6, cod_t7, cod_t8, cod_t9, prec_t1, prec_t2, prec_t3, prec_t4, prec_t5, prec_t6, prec_t7, prec_t8, prec_t9) values ('Q1', 'ELCV', 'PGPF', null, 892, 'VNXH', null, 435, null, null, null, null, null, 48.17, 70.64, 95.88, 71.31, null, null);
insert into prices_info (quarter, cod_t1, cod_t2, cod_t3, cod_t4, cod_t5, cod_t6, cod_t7, cod_t8, cod_t9, prec_t1, prec_t2, prec_t3, prec_t4, prec_t5, prec_t6, prec_t7, prec_t8, prec_t9) values ('Q1', null, 'BKOD', 'HYBX', 839, 'KWGD', null, 229, null, 'NPRL', 65.32, null, 94.17, 20.97, 36.97, 46.27, null, null, 18.91);

我需要对这些行进行排序才能看到这个:

i need to order those rows in order to see this:

我想使用光标来查看结果,但我需要知道标题的名称(因为如果您在每次交互时看到标题更改的图像)以及该标题中包含的信息和其余信息(所有列 PREC_XXX)

I was thinking use cursor to see the result, but i need the to know the name of the header (because if you see the image on every interaction the header change) and also the information that is contained on that header and the rest of the information (all columns PREC_XXX)

说实话,我不知道如何开始,有人可以帮我吗?

And to say the true, i don't have a idea how to start, can somebody help me with this?

最好的问候

推荐答案

您可以使用横向连接和聚合.这应该有效:

You can use a lateral join and aggregate. This should work:

select id, quarter,
       max(case when seqnum_cod = 1 then cod end) as cod_1,
       max(case when seqnum_cod = 2 then cod end) as cod_2,
       . . .,
       max(case when seqnum_prec = 1 then cod end) as prec_1,
       max(case when seqnum_prec = 2 then cod end) as prec_2,
       . . .,
from (select pi.*,
             row_number() over (partition by pi.id
                                order by (case when cod is not null then ord end) nulls last
                               ) as seqnum_cod,
             row_number() over (partition by pi.id
                                order by (case when prec is not null then ord end) nulls last
                               ) as seqnum_prec,
      from prices_info pi cross join lateral
           (select pi.cod_t1 as cod, prec_t1 as prec, 1 as ord from dual union all
            select pi.cod_t2 as cod, prec_t2, 2 as ord from dual union all
            . . . 
           ) t
     ) t
group by id, quarter;

314