嵌套表在Oracle中提供意外表

Question

这些天来我一直在用这个甲骨文。

create type virus_Statistic_t as object(
    vDate date,
    infection int,
    dead int,
    recovered int
)
/

create type virus_Statistic_tlb as table of virus_Statistic_t

create type countries_t as object(
    Province_or_State varchar2(50),
    Country_or_Region varchar2(100),
    Lat Number(10,6),
    Longt Number(10,6),
    virus virus_Statistic_tlb
)
/

create table countries of countries_t (
       primary key(Province_or_State, Country_or_Region)
) nested table virus store as virus_ntb;

INSERT INTO countries VALUES (
       countries_t('British Columbia', 'Canada', 49.2827, -123.1207, 
                 virus_Statistic_tlb(
                        virus_Statistic_t('22-JAN-20', 5, 0, 0), 
                        virus_Statistic_t('23-JAN-20', 10, 2, 5)
                 )
        )
);

INSERT INTO countries VALUES (
       countries_t('Queensland', 'Australia', -28.0167, 153.4, 
                 virus_Statistic_tlb(
                        virus_Statistic_t('22-JAN-20', 20, 0, 0), 
                        virus_Statistic_t('23-JAN-20', 10, 8, 10)
                 )
        )
);

select c.Province_or_State, c.Country_or_Region, c.Lat, c.Longt,  v.vDate, v.infection, v.dead, v.recovered 
from countries c, table(c.virus) v

，在我运行这个之后，它给了我这个表

PROVINCE_OR_STATE  COUNTRY_OR_REGION    LAT         LONGT    VDATE     INFECTION    DEAD    RECOVERED
British Columbia    Canada            49.2827   -123.1207   22-JAN-20   5            0      0
British Columbia    Canada            49.2827   -123.1207   23-JAN-20   10           2      5
Queensland          Australia        -28.0167    153.4      22-JAN-20   20           0      0
Queensland          Australia        -28.0167    153.4      23-JAN-20   10           8      10

，但我期望的表是

PROVINCE_OR_STATE  COUNTRY_OR_REGION    LAT         LONGT    VDATE     INFECTION    DEAD    RECOVERED
British Columbia    Canada            49.2827   -123.1207   22-JAN-20   5            0      0
                                                            23-JAN-20   10           2      5
Queensland          Australia        -28.0167    153.4      22-JAN-20   20           0      0
                                                            23-JAN-20   10           8      10

我应该对我的代码进行哪些更改？

您可以在这里中测试该脚本

Answer 1

当您用SQL*Plus标记标记它时，坏掉就是您所需要的。

这就是你现在拥有的：

SQL> select c.Province_or_State, c.Country_or_Region, c.Lat, c.Longt,
  2         v.vDate, v.infection, v.dead, v.recovered
  3  from countries c, table(c.virus) v;

PROVINCE_OR_STAT COUNTRY_OR_REGION           LAT      LONGT VDATE     INFECTION       DEAD  RECOVERED
---------------- -------------------- ---------- ---------- -------- ---------- ---------- ----------
British Columbia Canada                  49,2827  -123,1207 22.01.20          5          0          0
British Columbia Canada                  49,2827  -123,1207 23.01.20         10          2          5

休息：

SQL> break on province_or_state on country_or_region on lat on longt
SQL> select c.Province_or_State, c.Country_or_Region, c.Lat, c.Longt,
  2         v.vDate, v.infection, v.dead, v.recovered
  3  from countries c, table(c.virus) v;

PROVINCE_OR_STAT COUNTRY_OR_REGION           LAT      LONGT VDATE     INFECTION       DEAD  RECOVERED
---------------- -------------------- ---------- ---------- -------- ---------- ---------- ----------
British Columbia Canada                  49,2827  -123,1207 22.01.20          5          0          0
                                                            23.01.20         10          2          5

SQL>

其他(报告)工具，如Oracle reporting或Apex报表都有自己的中断功能。

插入另一行后，查询(实际上是中断)仍按预期工作：

SQL> /

PROVINCE_OR_STAT COUNTRY_OR_REGION           LAT      LONGT VDATE     INFECTION       DEAD  RECOVERED
---------------- -------------------- ---------- ---------- -------- ---------- ---------- ----------
British Columbia Canada                  49,2827  -123,1207 22.01.20          5          0          0
                                                            23.01.20         10          2          5
Queensland       Australia              -28,0167      153,4 22.01.20         20          0          0
                                                            23.01.20         10          8         10

SQL>

Answer 2

您正在将c.virus转换为表，它包含两个交叉连接到主表的记录。因此，您将得到两条记录(1条记录交叉连接2条记录=2条记录)

您可以按以下方式使用analytical function：

SELECT CASE WHEN RN = 1 THEN Province_or_State END AS Province_or_State,
       CASE WHEN RN = 1 THEN Country_or_Region END AS Country_or_Region,
       CASE WHEN RN = 1 THEN Lat END AS Lat,
       CASE WHEN RN = 1 THEN Longt END AS Longt,
       vDate, infection, dead, recovered 
  FROM
 (select c.Province_or_State, c.Country_or_Region, c.Lat, c.Longt,  v.vDate, v.infection, v.dead, v.recovered 
 ,ROW_NUMBER() OVER (PARTITION BY c.Province_or_State, c.Country_or_Region, c.Lat, c.Longt ORDER BY V.VDATE) AS RN,
 , DENSE_RANK() OVER (ORDER BY c.Province_or_State, c.Country_or_Region, c.Lat, c.Longt) AS DRN
  from countries c, table(c.virus) v)
 ORDER BY DRN, RN
/

请参阅db<>fiddle demo