创建视图并联接2个表查询

Question

我在这件事上陷入了困境，所以希望有人能帮忙。

我需要创建一个视图表并将两个表连接在一起，但我只需要显示缺少数据的条目。

因此，我有一张表列出了我拥有的无人机，包括制造商、序列号和购买日期，还有一张表列明了维护数据，包括每个设备的维护类型。该表还包括与第一个表类似的数据。

我需要从第一个表中提取设备数据，并将其与维护表进行比较，然后显示未接受特定类型维护的无人机。

这就是我尝试过的

CREATE VIEW uncommissioned AS
SELECT DISTINCT 
drone.manufacturer AS manufacture, 
drone.serial, 
drone.type, 
drone.purchase_date, 
maintenance.type AS maintenance
FROM drone 
INNER JOIN maintenance ON drone.type = maintenance.type
WHERE maintenance.type = 'NULL';

但这是一张空桌子，有什么建议可以告诉我哪里出了问题吗？

Answer 1

我认为您想要一个反LEFT JOIN:

CREATE VIEW uncommissioned AS
SELECT DISTINCT 
d.manufacturer AS manufacture, 
d.serial, 
d.type, 
d.purchase_date, 
d.type AS maintenance
FROM drone d
LEFT JOIN maintenance m ON d.type = m.type
WHERE m.type IS NULL;

注：

• 要检查零，您需要IS NULL构造(您的代码检查乱丢字符串'NULL')：

• 如果要显示维护类型，则需要从drone表中获取该信息，而不是从maintenance表中获取

您也可以用NOT EXISTS条件和相关子查询来表达这一点：

CREATE VIEW uncommissioned AS
SELECT DISTINCT 
d.manufacturer AS manufacture, 
d.serial, 
d.type, 
d.purchase_date, 
d.type AS maintenance
FROM drone d
WHERE NOT EXISTS (SELECT 1 FROM maintenance m WHERE d.type = m.type)

Answer 2

IS NULL是正确的，right JOIN是因为在右表维护中为NULL
请尝试：SELECT DISTINCT drone.manufacturer AS manufacture, drone.serial, drone.type, drone.purchase_date, maintenance.type AS maintenance FROM drone RIGHT OUTER JOIN maintenance ON drone.type = maintenance.type WHERE maintenance.type IS NULL;

Answer 3

基于您的最后一条评论：

循序渐进，建立你想做的事情(我们稍后将结合这些设置)。。。。

1. 您正在查看所有无人机。因此，让我们从drones表中选择列，看看有什么。

   SELECT * FROM drones ;
   

   manufacturer|serial|type|purchase_date：------------------------|----：|----：|：------------------洛克希德·马丁|1|1|2020-01-01 00:00:00空中机器人系统|2|10|2020-02-01 00:00:00亚马逊|3|100|2020-03-01 00:00:00诺斯鲁普·格鲁曼|4|2|2020-04-01 00:00:00DJI|5|20|2020-05-01 00:00:00泰坦航空|6|200|2020-06-01 00:00:00

2. maintenance表中有什么？

   SELECT * FROM maintenance ;
   

   dronetype|mxtype--------：|：----------------1|更换螺旋桨1|委托100|委托2|委托20 |委托200 |已禁用

3. 这两张表是如何联系在一起的？让我们把它们钩在一起在CCD_ 11上具有CCD_。MOTE：我做了一个假设，并更改了maintenance.type，使其更能描述我认为这些关系应该如何。

   3.1.我们需要哪种JOIN？(举例说明：https://www.edureka.co/blog/sql-joins-types)。我只看INNER和OUTERJOINs。

   3.1.1INNER JOIN只会给我们在BOTH表中匹配的记录。

   SELECT d.manufacturer, d.serial, d.type, d.purchase_date, m.mxtype
   FROM drones d
   INNER JOIN maintenance m ON d.type = m.dronetype ;
   

   manufacturer|serial|type|purchase_date|mxtype：---------------|----：|----：|：---------------|：----------------洛克希德·马丁|1|1|2020-01-01 00:00:00|更换螺旋桨洛克希德·马丁|1|1|2020-01-01 00:00:00|已调试亚马逊|3|100|2020-03-01 00:00:00|已委托诺斯鲁普·格鲁曼|4|2|2020-04-01 00:00:00|已调试DJI|5|20|2020-05-01 00:00:00|委托Titan Aerospace |6|200|2020-06-01 00:00:00|降级

   这不是我们真正想要的。

   3.1.2OUTER JOIN将为我们提供其中一个表的记录以及另一个表中匹配的行。不匹配的行将为NULL。

   SELECT d.manufacturer, d.serial, d.type, d.purchase_date, m.mxtype
   FROM drones d
   LEFT OUTER JOIN maintenance m ON d.type = m.dronetype ;
   

   manufacturer|serial|type|purchase_date|mxtype：------------------|----：|----：|：------------------|：----------------洛克希德·马丁|1|1|2020-01-01 00:00:00|更换螺旋桨洛克希德·马丁|1|1|2020-01-01 00:00:00|已调试亚马逊|3|100|2020-03-01 00:00:00|已委托诺斯鲁普·格鲁曼|4|2|2020-04-01 00:00:00|已调试DJI|5|20|2020-05-01 00:00:00|委托泰坦航空|6|200|2020-06-01 00:00:00|已退役空中机器人系统|2|10|2020-02-01 00:00:00|空

   That shows us all of the `drones` with a `NULL` record for the non-matched one.
   

4. 但我们想在maintenance表格中显示尚未使用Commissioned的无人机。

   SELECT d.manufacturer, d.serial, d.type, d.purchase_date, m.mxtype
   FROM drones d
   LEFT OUTER JOIN maintenance m ON d.type = m.dronetype 
   WHERE m.mxtype <> 'commissioned' ;
   

   manufacturer|serial|type|purchase_date|mxtype：---------------|----：|----：|：---------------|：----------------洛克希德·马丁|1|1|2020-01-01 00:00:00|更换螺旋桨Titan Aerospace |6|200|2020-06-01 00:00:00|DECOMISIONED

   但现在我们错过了无与伦比的记录，我们正在显示我们不想要的记录(比如Lockheed Martin记录，它有两个maintenance记录，其中一个是Commissioned)。

5. 因此，让我们从drones表中选择在maintenance中没有Commissioned记录的记录。我们可以用NOT EXISTS来做到这一点。

   SELECT d.manufacturer, d.serial, d.type, d.purchase_date
   FROM drones d
   WHERE NOT EXISTS (
   SELECT 1 /* This can be anything. It's ignored. */
   FROM maintenance m 
   WHERE m.mxtype = 'commissioned' 
   AND m.dronetype = d.type
   ) ;
   

   manufacturer|serial|type|purchase_date：------------------------|----：|----：|：------------------空中机器人系统|2|10|2020-02-01 00:00:00泰坦航空|6|200|2020-06-01 00:00:00

   现在我们有两架无人机，它们在maintenance中没有Commissioned的记录。

   注意：在这个最终查询中，我们使用了一个相关的子查询来将maintenance表链接到drones表，并且我们在maintenance表的查询中包含了一个条件，以仅选择具有mxtype或commissioned的记录。并且WHERE NOT EXISTS将过滤drones表的查询以排除子查询中DO匹配的任何行

您可以在这里玩查询和数据：db<>小提琴