在SQL中构建嵌套查询

Question

我是SQL新手，在构建查询时遇到了问题：

我有三张表：

杂志店的顾客：

Customer(cid, name)

杂志

Magazine(mid, topic)

杂志的订阅：

Subscribes(cid, mid)

我如何构建一个查询来显示只订阅了一本杂志的客户，条件是订阅了该杂志的所有其他客户至少订阅了一本其他杂志？

我设法构建了一个查询(不确定是否绝对正确)，显示只订阅了一本杂志并停留在此处的客户：

select customer.cid, customer.name, count(subscribes.mid)
from subscribes, customer
where subscribes.cid=customer.cid 

group by customer.cid
having count(subscribes.mid)=1

Answer 1

如何构建一个查询，以显示仅订阅了一本杂志的客户，条件是订阅该杂志的所有其他客户至少订阅了一本其他杂志？

你的想法是对的，一步一步来。

客户只订阅一本杂志

 SELECT cid
 FROM subscribes
 GROUP BY cid
 HAVING COUNT(mid) = 1

订阅两本或更多杂志的客户

SELECT cid
FROM subscribes
GROUP BY cid 
HAVING COUNT(mid) > 1

客户和他们订阅的杂志数量

SELECT cid, count(mid) as s_count
FROM subscribes
GROUP BY cid

所以

WITH cust_and_count AS
(
  SELECT cid, count(mid) as s_count
  FROM subscribes
  GROUP BY cid
)
SELECT cust_and_count.cid, s1.mid
FROM cust_and_count -- with the where below this gives us all users subscribed to a single mag
-- Get the magazine of users that have one subscription
JOIN subscribes s1 on cust_and_count.cid = s1.cid
-- Join that magazine back to who subscribes exclude our starting guys
JOIN subscribes s2 on s1.mid = s2.mid and s2.cid != cust_and_count.cid
-- Join back to our counts and make sure it is greater than 1
JOIN cust_and_count c2 on s2.cid = c2.cid and c2.s_count > 1
WHERE cust_and_count.s_count = 1

请注意，表名通常不是复数--所有表都有行，通常按单个元素的名称命名，因此在本例中为customer、magazine、subscription。

Answer 2

下面的查询只选择订阅了1本杂志的所有客户：

SELECT *
FROM Subscribes
GROUP BY cid
HAVING COUNT(Subscribes.mid) = 1

此查询为具有1个订阅的杂志M选择C以外的所有客户：

SELECT 1
FROM Subscribes
WHERE mid = M
      AND cid != C
      AND EXISTS (SELECT 1
                  FROM Subscribes
                  WHERE cid = Subscribes.cid
                  AND COUNT(mid) <= 1)

我相信，这将会达到你在问题中提出的问题：

SELECT *
FROM Subscribes
GROUP BY cid
HAVING COUNT(Subscribes.mid) = 1 as SingleSubs
       AND NOT EXISTS (SELECT 1
                       FROM Subscribes
                       WHERE mid = SingleSubs.mid
                             AND cid != SingleSubs.cid
                             AND EXISTS (SELECT 1
                                         FROM Subscribes
                                         WHERE cid = Subscribes.cid
                                         AND COUNT(mid) <= 1))

解释：

SELECT (All cid, mid with 1 subscription) as SingleSubs
WHERE NOT EXISTS (All customers other than SingleSubs.cid with 1 subscription)

Answer 3

必须归功于@Hogan..。

小提琴：

http://sqlfiddle.com/#!6/860e7/4

代码：

;WITH cust_and_count AS
(
  SELECT cid, count(mid) as s_count
  FROM subs
  GROUP BY cid
)
SELECT distinct c1.cid
FROM cust_and_count c1
JOIN subs s1 on c1.cid = s1.cid
JOIN subs s2 on s1.mid = s2.mid and s2.cid != c1.cid and s1.mid >1
WHERE c1.s_count = 1