时间戳

首页 > 解决方案 > Oracle SQL 查询问题

sql - Oracle SQL 查询问题

问题描述

这是我的示例数据结构和示例数据。我在这里要完成的是不向具有“订阅者”类型的订阅者记录的客户显示。您将在数据集中看到 Eli Manning 有两条订阅记录。一个是“所有者”类型，另一个是“订阅者”类型。所以，他不应该出现在我的结果中，因为有一个“订阅者”记录实例。Odell Beckham Jr. 只有一个“所有者”类型的记录，所以他应该展示。

我曾尝试使用此查询，但结果返回客户 Saquan Barkley。您将看到该客户在订阅者表中有一条“订阅者”记录，因此我的 sql 没有按预期工作。任何帮助将非常感激。

我的查询：

select distinct
    a.customer_id,
    a.fst_name,
    a.last_name,
    a.email,
    b.subscription_type
from
    customers a,
    subscriptions b
where
    a.customer_id <> (select customer_id from subscriptions
                      where subscription_type <> 'SUBSCRIBER')
    AND b.subscription_type <> 'SUBSCRIBER'
order by customer_id asc;

表格和数据：

DROP TABLE CUSTOMERS;
DROP TABLE SUBSCRIPTIONS;

CREATE TABLE "CUSTOMERS" 
   (    "FST_NAME" VARCHAR2(50 BYTE), 
    "LAST_NAME" VARCHAR2(100 BYTE), 
    "CUSTOMER_ID" NUMBER NOT NULL ENABLE, 
    "EMAIL" VARCHAR2(150 BYTE), 
     CONSTRAINT "CUSTOMERS_PK" PRIMARY KEY ("CUSTOMER_ID"));

  CREATE TABLE "SUBSCRIPTIONS" 
   (    "ID" NUMBER NOT NULL ENABLE, 
    "CUSTOMER_ID" NUMBER NOT NULL ENABLE, 
    "SUBSCRIPTION_TYPE" VARCHAR2(20 BYTE), 
    "SERIAL_NUMBER" VARCHAR2(50 BYTE), 
     CONSTRAINT "SUBSCRIPTIONS_PK" PRIMARY KEY ("ID")); 

INSERT INTO customers (fst_name, last_name, customer_id, EMAIL)
VALUES ('Eli', 'Manning', '1', 'emannning@giants.com');
INSERT INTO customers (fst_name, last_name, customer_id, EMAIL)
VALUES ('Odell', 'Beckham Jr.', '2', 'beckham@giants.com');
INSERT INTO customers (fst_name, last_name, customer_id, EMAIL)
VALUES ('Saquan', 'Barkley', '3', 'sbarkley@giants.com');
INSERT INTO customers (fst_name, last_name, customer_id, EMAIL)
VALUES ('Evan', 'Engram', '4', 'eEngram@giants.com');
INSERT INTO customers (fst_name, last_name, customer_id, EMAIL)
VALUES ('Nate', 'Solder', '5', 'nsolder@giants.com');
INSERT INTO customers (fst_name, last_name, customer_id, EMAIL)
VALUES ('Patrick', 'Omameh', '6', 'pomameh@giants.com');
INSERT INTO subscriptions (id, customer_id, subscription_type, serial_number)
VALUES ('1', '1', 'SUBSCRIBER', 'ASDF1234556');
INSERT INTO subscriptions (id, customer_id, subscription_type, serial_number)
VALUES ('2', '1', 'OWNER', 'ASDF1234556');
INSERT INTO subscriptions (id, customer_id, subscription_type, serial_number)
VALUES ('3', '2', 'OWNER', 'ASDF987657');
INSERT INTO subscriptions (id, customer_id, subscription_type, serial_number)
VALUES ('4', '3', 'SUBSCRIBER', 'ASDF11223344');
COMMIT;

标签： sqloracle

解决方案

试试下面的代码：

  SELECT a.customer_id, a.fst_name, a.last_name, a.email, b.subscription_type
    FROM customers a 
    LEFT JOIN subscriptions b ON (b.customer_id = a.customer_id)
   WHERE a.customer_id NOT IN (SELECT customer_id
                                 FROM subscriptions
                                WHERE subscription_type = 'SUBSCRIBER')
ORDER BY a.customer_id ASC;

除了将您的代码更改为包含NOT IN而不是<>（这包括更改您的子查询条件以包括所有在其类型中具有类型的客户'SUBSCRIBER'），我还将您的语法切换为使用显式JOIN。DISTINCT除此之外，如果您正确加入表格，则不需要。

推荐阅读