如何在sql中显示带有3个联接的输出？

Question

SQL>从Art_Object中选择*；

    ID YEAR                      TITLE                     DES                       A_NAME

     1 1890                      Old Man                   An old man in the dark    van Gogh
     2 1894                      Cat                       White cat in black canvas van Gogh
     3 1853                      Monalisa                  Smiling woman             Leonardo
     4 1888                      The meeting               Two men talking           Picaso
     5 2017                      The crimson stone         Group of characters       Omar

SQL>从绘画中选择*；

    ID PAINT_TYPE                     MATERIAL                       STYLE

     2 Oil Painting                   Oil                            Cubism
     3 Satin                          Paint                          Expressionism

SQL>从雕塑中选择*；

    ID MATERIAL                           HEIGHT     WEIGHT STYLE

     4 Mud                                   172        180 Cubism

SQL>从他人中选择*；

    ID TYPE                           STYLE

     1 3D painting                    Realist
     5 Digital Painting               Manga

SQL>

问题是(博物馆里最古老的艺术品是什么类型的？)所以我想我需要加入这4张表，并在这里显示最小年份，即"int“，在这里显示(paint_type，来自绘画或材料，来自雕塑，或者来自其他)，在输出中显示(类型)和(年份)

Answer 1

以下是解决这一问题的传统方法：

select ao.*,
       (case when exists (select 1 from painting p where p.id = ao.id) then 'painting'
             when exists (select 1 from sculpture s where s.id = ao.id) then 'sculpture'
             when exists (select 1 from other o where o.id = ao.id) then 'other'
        end) as art_type             
from art_object ao
order by year, id
fetch first one row only;

并不是所有的数据库都支持fetch first；然而，所有的数据库都有某种方法来实现这一点。

此外，如果绑定了多个对象，则只返回一个最古老的对象。

编辑：

在Oracle预12中，您可以这样做：

select ao.*,
       (case when exists (select 1 from painting p where p.id = ao.id) then 'painting'
             when exists (select 1 from sculpture s where s.id = ao.id) then 'sculpture'
             when exists (select 1 from other o where o.id = ao.id) then 'other'
        end) as art_type             
from (select ao.*, rownum as seqnum
      from art_object ao
      order by ao.year desc
     ) ao
where seqnum = 1;

Answer 2

你说得对，就像这样：

SELECT
  o1.A_Name,
  o1.Year,
  p.Paint_type,
  s.MATERIAL, s.HEIGHT, s.WEIGHTSTYLE,
  o.TYPE,o.STYLE
FROM Art_Object as o1
INNER JOIN
(
   SELECT ID, MIN(Year) AS Oldest
   FROM Art_Object 
   GROUP BY ID
) AS o2 ON o1.ID = o2.ID AND o1.Year = o2.Oldest
INNER JOIN painting as p ON o1.ID = p.ID
INNER JOIN sculpture AS s ON o1.ID = s.ID
INNER JOIN other as o ON o1.ID = o.ID