在视图SQL-Oracle中创建一个子查询

Question

我正在设置数据库视图。

我有一个桌子的人和一个由person_id关联的关系_company。我试图从每个person_id的关系中获取最小日期。

我需要使用子查询？

这是我到目前为止尝试过的。

CREATE OR REPLACE VIEW PEOPLE_ADDRESS
select
    p.id as ADDRESS_CODE,
    case p.type_
        when 1 then 'JURIDICAL'
        when 0 then 'NATURAL'
    end PERSON_TYPE,
    (select min(start_date) from relation_company where relation_company.person_id = p.id) as INITIAL_dATE
    P.ID as PERSON_CODE,
    p.ADDRESS_NAME as ADDRESS,
    p.POSTAL_CODE as POSTAL_CODE,
    case P.COUNTRY_ID
        when 68 then 'NO'
        else 'YES'
    end FOREIGN_INDICATOR
from
    PERSON p join relation_company r on relation_company = p.id ;

那是我的桌子：

ID TYPE_ ADDRESS_NAME          NAME             OTHER COLUMNS
1   0    AVENUE SMITH, 19      MICHAEL SCOTT     ....
2   1    OXFORD STREET , 119   COMPANY A          ....
3   1    CHESTHAM ROAD, 7      COMPANY B          ....

那是关系_company表：

ID      PERSON_ID       START_DATE
1           1           2016-12-12 00:00:00
2           1           1981-07-09 00:00:00
3           2           1991-10-10 00:00:00
4           2           1981-03-09 00:00:00
5           3           1984-07-05 00:00:00
6           3           1981-08-11 00:00:00
7           3           1987-07-09 00:00:00
8           3           2011-07-09 00:00:00
select min(START_DATE) from RELATION_COMPANY group by PERSON_ID;

结果应为：

ADDRESS_CODE  PERSON_TYPE    INITIAL_dATE                 other columns
1             NATURAL        1981-07-09 00:00:00             ....
2            JURIDICAL      1981-03-09 00:00:00               ....
3             JURIDICAL     1981-08-11 00:00:00

我想为每个person_id获取最小(start_date(，然后将其打印到视图到列pinitial_date。

我很难创建该查询。

Answer 1

您已经有了解决方案，此查询在选择语句中缺少" Person_ID"列。更改此查询

select min(START_DATE) from RELATION_COMPANY group by PERSON_ID;

对此。

select min(START_DATE),PERSON_ID from RELATION_COMPANY group by PERSON_ID;

希望这会有所帮助。

Answer 2

您不需要解决方案的子查询。仅在您的查询中添加一个组子句。

喜欢这个：

select
p.id as PERSON_ID,
case p.type_
    when '1' then 'JURIDICAL'
    when '0' then 'NATURAL'
end PERSON_TYPE,
min(start_date) as INITIAL_dATE,
p.ADDRESS_NAME as ADDRESS,
p.POSTAL_CODE as POSTAL_CODE,
case P.COUNTRY_ID
    when 68 then 'NO'
    else 'YES'
end FOREIGN_INDICATOR
from PERSON p join relation_company r on r.person_id = p.id 
group by p.id, type_, ADDRESS_NAME, POSTAL_CODE, P.COUNTRY_ID;