主页 / dba / 问题 / 184469
Accepted
learningIT
Asked: 2017-08-28 23:59:56 +0800 CST

SQL 查询显示已购买所有 DVD 的客户的姓名

  • 772

这些是我创建的表并相应地插入了值：

CREATE TABLE Customer
(Customer_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 Customer_Name VARCHAR(30) NOT NULL
)

CREATE TABLE DVD
(DVD_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_Name VARCHAR(30)
)

CREATE TABLE DVD_Purchase
(DVD_Purchase_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_No INTEGER NOT NULL,
 Customer_No INTEGER NOT NULL
 )

INSERT INTO Customer (Customer_Name)
VALUES('Daman')
INSERT INTO Customer (Customer_Name)
VALUES('Saif')
INSERT INTO Customer (Customer_Name)
VALUES('Gurung')
INSERT INTO Customer (Customer_Name)
VALUES('Upendra')

INSERT INTO DVD (DVD_Name)
VALUES('Bleach')
INSERT INTO DVD (DVD_Name)
VALUES('Gintama')
INSERT INTO DVD (DVD_Name)
VALUES('Tokyo Ghoul')
INSERT INTO DVD (DVD_Name)
VALUES('Death Note')

INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,1)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,2)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (3,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,4)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,4)

为了显示购买了所有 DVD 的客户的姓名，这是我尝试过的查询：

SELECT Customer_Name
FROM Customer
WHERE 
EXISTS (SELECT Customer_Name,DVD_Name
FROM Customer,DVD,DVD_Purchase
WHERE Customer.Customer_No = DVD_Purchase.Customer_No AND DVD.DVD_No = DVD_Purchase.DVD_No)

但不幸的是，执行此查询后，它会显示所有客户名称。但我想显示购买了所有 DVD 的特定客户名称。

有人可以向我提供正确的查询吗？

sql-server relational-division

5 个回答

  1. Best Answer

    您可以尝试以下方法：

    select c.Customer_Name
from Customer c
join (  select Customer_No
        from DVD_Purchase
        group by Customer_No
        having count(distinct DVD_No) = (select count(*) from DVD)
        ) d on c.Customer_No = d.Customer_No

    说明：首先我们需要知道可用的 DVD 数量。

    select count(*) 
from DVD

    然后我们需要获取客户购买的不同DVD 的数量，并将其与我们之前获得的总数进行比较。

    select Customer_No
from DVD_Purchase
group by Customer_No
having count(distinct DVD_No) = <number of DVDs available>

    最后，我们只需要加入客户表来检索名称。

    • 3

  2. 作为对作为关系划分形式的irimias解决方案的补充，另一个变体是使用存在和双重否定。从本质上讲，一个人想要实现的是：

    FORALL x:p(x)

    然而，这在 SQL 中不受支持，但您可以使用等价：

    FORALL x:p(x) <=> NOT EXISTS x:NOT p(x)

    在你的情况下，这将是：customers, where it does not exist a DVD such that the customer did not purchase it:

    select c.Customer_Name
from Customer c
where not exists (
    select 1 from dvd d
    where not exists (
        select 1 from DVD_Purchase dp
        where dp.DVD_NO = d.DVD_NO
          and dp.Customer_No = c.Customer_No
    )
);

    根据我的经验，大多数人发现 irimias 解决方案更容易理解，所以你应该坚持下去。但是，查看其他解决方案可能会引起一些兴趣。

    作为一个练习，对于一个一致的数据库（在 op 的示例中没有声明引用完整性），这两个查询返回相同的结果，除了在一种特殊情况下，那是什么情况？

    • 1 
  3. declare @No_dvd as int
select @No_dvd = count(*) from dvd

select *
from customer AS c
    cross apply(select count(distinct dvd_no) as cd_dvd 
                 from DVD_Purchase AS DP 
                 where c.Customer_No = dp.Customer_No) AS ca
where
    ca.cd_dvd = @no_dvd -- (select count(*) from dvd)

    输出：

    Customer_No Customer_Name   cd_dvd
3   Gurung  4

    其背后的想法是计算每个客户的 dvd 数量并将其与所有 dvd 匹配。

    • 0

  4. 或者，您可以尝试使用窗口函数，如下所示：

    1. 找出每个客户购买的每张 dvd 的数量
    2. 总结一下，得到每个客户购买的不同 dvd 的总数

    3. 检查总数是否与 dvd 表中的不同行总数匹配

      select customer_no, customer_name, sum(rownum) count_distinct_dvds
from
    (
          select d.customer_no
               , c.customer_name
               , d.dvd_no
               , count(*) over(partition by d.customer_no, d.dvd_no) nr_dvds_bought
               , row_number() over(partition by d.customer_no, d.dvd_no order by d.customer_no, d.dvd_no) rownum
          from Customer c 
                     join DVD_Purchase d on c.Customer_No = d.customer_no 
       ) x
 group by customer_no, customer_name
 having sum(rownum) = (select count(*) from dvd) 
 order by customer_no, customer_name
    • 0 
  5. ;with CTE_test1 (customer_name, customer_no,ppo)
as (
select 
customer_name,
p.customer_no
, count(p.dvd_no) [ppo]
from dvd_purchase p
  inner join (select count(*)[test], dvd_no from dvd group by dvd_no) d on d.dvd_no = p.dvd_no
  inner join customer c on c.customer_no = p.customer_no
group by  p.customer_no ,c.customer_name)

select  customer_name from CTE_test1  where ppo = ( select count(*) from dvd)
    • -1

相关问题