你怎么能写这个查询？

Question

我有两张桌子。 表的结构如下。

TRAILERS_INVENTORY

• TRAILER_TYPE VARCHAR2(100)
• TRAILER_LENGTH INT
• TRAILER_WIDTH INT
• YEAR_OF_MANUFACTURE INT

NEW_INVENTORY

• NEW_INVENTORY_TYPE_ID INT、
• TRAILER_TYPE VARCHAR2(100)、
• TRAILER_LENGTH INT、
• TRAILER_WIDTH INT、
• UFACTURE INT

我想列出所有没有 NEW_INVENTORY_TYPE_ID 的预告片。我使用的是Oracle 9.2。

一种方法是使用集合运算，类似于

SELECT TRAILER_TYPE
 FROM TRAILERS_INVENTORY 
WHERE TRAILER_TYPE NOT IN (SELECT TRAILER_TYPE, 
                                  TRAILER_LENGTH, 
                                  TRAILER_WIDTH, 
                                  YEAR_OF_MANUFACTURE 
                             FROM TRAILERS_INVENTORY 
                           INTERSECT 
                           SELECT TRAILER_TYPE, 
                                  TRAILER_LENGTH, 
                                  TRAILER_WIDTH, 
                                  YEAR_OF_MANUFACTURE 
                             FROM NEW_INVENTORY);

还有其他可用的方法吗？

Answer 1

我看到几个选项

SELECT
    TRAILER_TYPE
FROM
    TRAILERS_INVENTORY
WHERE
    TRAILER_TYPE NOT IN (SELECT TRAILER_TYPE FROM NEW_INVENTORY)

或者：

SELECT
    TRAILER_TYPE
FROM
    TRAILERS_INVENTORY
LEFT JOIN
    NEW_INVENTORY
    ON (NEW_INVENTORY.TRAILER_TYPE = TRAILERS_INVENTORY.TRAILER_TYPE)
WHERE
    NEW_INVENTORY.NEW_INVENTORY_TYPE_ID IS NULL

Answer 2

那么，问题是如何在不使用“set”操作的情况下执行此查询？

请问为啥不用套装呢？

您在 SQL 中所做的一切都基于集合，但如果您想要一个更慢、不太优雅的解决方案，您可以将集合大小减少到 1 并使用 PL/SQL 循环遍历 TRAILERS_INVENTORY。

declare
    v_exists varchar2(1 char);
begin
    for item in (select distinct trailer_type from trailers_inventory) loop
        begin
            select 'Y' into v_exists from new_inventory
            where trailer_type = item.trailer_type;
        exception
            when no_data_found then
                v_exists := null;
        end;
        if v_exists is null then
            dbms_output.put_line(item.trailer_type || ' not in ' || ' NEW_INVENTORY');
        end if;
    end loop;
end;
/

哎呀！这就是为什么集合操作是最佳选择。

:)

Answer 3

怎么样：

SELECT 
TRAILER_TYPE, 
TRAILER_LENGTH, 
TRAILER_WIDTH, 
YEAR_OF_MANUFACTURE 
FROM TRAILERS_INVENTORY 
MINUS
SELECT 
TRAILER_TYPE, 
TRAILER_LENGTH, 
TRAILER_WIDTH, 
YEAR_OF_MANUFACTURE 
FROM NEW_INVENTORY