在同一张表上进行连接的SQL计数查询

Question

如果答案显而易见，我提前道歉。我已经搜索过但是没有找到解决方案。

我有一个订单表，包含两列：CustID和ProductBrand。 ProductBrand是已订购物品的制造商，例如Sony，HP，Dell等。

我想要得到订购了至少10个Sony产品但是订购了小于5个HP产品的客户列表。

我可以使用以下内容完成这个任务吗？

SELECT o1.CustID
FROM Orders o1 HAVING COUNT(o1.ProductBrand="Sony") >= 10
INNER JOIN Orders o2 ON o1.CustID = o2.CustID
HAVING COUNT(o2.ProductBrand="HP") < 5;

- Point

---

你的查询结果是什么？ - Tharif

我在描述中提供的查询实际上被SQL解析器拒绝了。 我只是提供它来说明我试图做什么的一些细节。 如果我没有表述清楚，很抱歉。 - Point

Answer 1

你可以使用聚合函数而无需进行自连接来完成此操作：

使用聚合函数即可，不需要进行自连接：

select o.custid
from orders o
group by o.custid
having sum(case when o.ProductBrand = 'Sony' then 1 else 0 end) >= 10 and
       sum(case when o.ProductBrand = 'HP' then 1 else 0 end) < 5;

在MySQL中，您可以通过删除case来简化此过程：

select o.custid
from orders o
group by o.custid
having sum(o.ProductBrand = 'Sony') >= 10 and
       sum(o.ProductBrand = 'HP') < 5;

- Gordon Linoff

---

成功了！非常感谢。现在看起来很简单和明显，但如果没有提示，我就不会知道那个方法。感谢您分享您的知识。 - Point

Answer 2

设置 fiddle

CREATE TABLE ORDERS (cust INT, brand VARCHAR(100))

INSERT INTO ORDERS VALUES (1, 'Sony')
INSERT INTO ORDERS VALUES (1, 'Sony')
INSERT INTO ORDERS VALUES (1, 'Sony')
INSERT INTO ORDERS VALUES (1, 'Hp')
INSERT INTO ORDERS VALUES (1, 'Hp')
INSERT INTO ORDERS VALUES (2, 'Sony')
INSERT INTO ORDERS VALUES (2, 'Sony')
INSERT INTO ORDERS VALUES (2, 'Hp')
INSERT INTO ORDERS VALUES (2, 'Hp')
INSERT INTO ORDERS VALUES (2, 'Hp')
INSERT INTO ORDERS VALUES (2, 'Hp')
INSERT INTO ORDERS VALUES (3, 'Sony')
INSERT INTO ORDERS VALUES (3, 'Sony')
INSERT INTO ORDERS VALUES (3, 'Sony')
INSERT INTO ORDERS VALUES (3, 'Hp')
INSERT INTO ORDERS VALUES (3, 'Hp')

查询

方法1

select 
    sony.custId 
from 
    (select cust as custId, Brand, count(*) as count 
     from orders 
     where Brand = 'Sony' 
     group by cust, brand) Sony 
         inner join 
     (select cust as custId, Brand, count(*) as count 
      from orders 
      where Brand = 'Hp' 
      group by cust, brand) Hp 
          on sony.custId = hp.CustId 
Where 
    sony.count > 2 and 
    hp.count < 5

这对你理解关系很有帮助。这是另一种方法

第二种方法

select cust  
from orders 
group by cust, brand
having  count(case brand when 'Sony' then 'x' end) > 2 and 
        count(case brand when 'Hp' then 'x' end) < 5

时间大致相同 - 当然会根据数据量、索引等而有所不同。使用Sql fiddle检查执行计划。第一种方法似乎有更多的事情要做。但这并不意味着在生产中使用时会更糟。

- T.S.

---

多么有用的工具！我一直在寻找这样的东西——发现Fiddle本身就值得这篇文章。通过您的第一种方法，我现在知道内部连接语法应该是什么了——我想肯定有一些方法可以指定单独的查询，然后使用计数比较将它们连接起来，但是找不到类似于我的问题的示例。T.S.，感谢您慷慨地花时间提供解决方案——希望能够像您为我所做的那样为其他人做出贡献。 - Point