具有参考键

Question

两个表的SQL查询我有两个表：

Discounts(disid primary key) 

Cust(custid primary key, disid ref discount(disid)) 

现在我需要一个查询来获取custid让所有disid（优惠券），并且客户可以包含相同disid不止一次。

Answer 1

select custid, count(distinct disid) from cust 
group by custid 
having count(*) = (select count(*) from discounts) 

Answer 2

SELECT COUNT(DISTINCT D.disid) FROM CUST C 
INNER JOIN DISCOUNTS D ON D.disid=C.disid GROUP BY D.disid 

Answer 3

尝试这两种解决方案：

SELECT a.custid, COUNT(a.disid) totalCoupon 
FROM cust a 
      INNER JOIN discounts b 
       ON b.disid = a.disid 
GROUP BY a.custid 

或

SELECT a.custid, COUNT(a.disid) totalCoupon 
FROM cust a 
      INNER JOIN discounts b 
       ON b.disid = a.disid 
GROUP BY a.custid 
HAVING COUNT(a.disid) > 1 -- customers having the same (but more than 1) 
          -- CouponID will only be shown here