sql - SQL关于如何列出单年下单超过30单的客户

Question

select CustomerKey, FirstName, LastName
from DimCustomer
where CustomerKey in
    (select distinct CustomerKey
     from FactInternetSales
     where year(OrderDate) = 2005 or year(OrderDate) = 2006
        or year(OrderDate) = 2007 or year(OrderDate) = 2008);

我应该在哪里添加“超过 30 个订单”部分？

标签： sql

Answer 1

通过使用连接和聚合过滤器尝试如下

select DimCustomer.CustomerKey,
 DimCustomer.FirstName, DimCustomer.LastName,year(FactInternetSales.OrderDate),count(*)
from DimCustomer join FactInternetSales
 on DimCustomer.CustomerKey=FactInternetSales.CustomerKey
group by CustomerKey, FirstName, LastName,year(FactInternetSales.OrderDate)
having count(*)>=30