Oracle SQL查询问题

Question

这是我的样本数据结构和样本数据。我在这里要做的是不向那些拥有一种类型的“订阅者”的订阅者记录的客户展示。您将在数据集中看到Eli有两个订阅记录。一种是“所有者”类型，另一种是“用户”类型。因此，他不应该出现在我的结果中，因为有一个“订阅者”记录实例。小奥德尔·贝克汉姆。只有一张“主人”的记录，所以他应该显示出来。

我曾经尝试使用这个查询，但结果返回客户萨泉巴克利。您将看到这个客户在订户表中有一个“订阅者”记录，所以我的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;

Answer 1

您所拥有的查询接近您想要的查询，而不是使用<> try not in，子查询可能会返回多个行，并且您不能将<>应用于它。

例如：

select distinct
    a.customer_id,
    a.fst_name,
    a.last_name,
    a.email,
    b.subscription_type
from
    customers a
    inner join subscriptions b on a.customer_id = b.customer_id
where b.subscription_type <> 'SUBSCRIBER' 
and a.customer_id not in
(
  select customer_id from subscriptions where subscription_type = 'SUBSCRIBER'
)
order by customer_id asc;

Answer 2

尝试以下代码：

  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而不是<> (后者包括将子查询条件更改为包含 all customer，的类型中有类型'SUBSCRIBER' )之外，我还切换了您的语法以使用显式JOIN。此外，如果正确地连接表，则不需要DISTINCT。

Answer 3

你是说像这样的事？

    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 = b.customer_id 
   and a.customer_id not in (select customer_id from subscriptions
    where subscription_type = 'SUBSCRIBER')
 order by customer_id asc