如何找到一天订单超过1个的客户

Question

我有一个查询，我用来查找在一天内发出2个或更多订单的客户。我有以下查询:

SELECT h.order_number, h.date,
t.customer_number, t.first_name, t.last_name, t.Address, t.city, t.state,
c.customer_number, c.first_name, c.last_name, c.address, c.city, c.state     
FROM Order_Table h
JOIN Customer c ON h.customer_number = c.customer_number   
JOIN OrderShipping s ON h.order_number = s.order_number    
JOIN Customer t ON s.customer_number = t.customer_number 
WHERE h.date > '2021/01/01' 
GROUP BY h.date, h.order_number,
c.customer_number, c.first, c.last, c.address, c.apartment, c.address2, c.city, c.state, c.zip,
t.customer_number, t.first, t.last, t.Address, t.apartment, t.Address2, t.city, t.state, t.zip 
HAVING COUNT(c.customer_number) > 1

我很难想出如何获得一个客户与多个c.p customer_number订单。

我还想要的订单，它是由c.customer_number订购，但发货到t.customer_number。这就是我添加订购和第二个客户表的原因。

提前感谢。

Answer 1

如果要统计特定客户编号的订单，请从group by子句中删除order_number:

SELECT h.date, t.customer_number, t.first_name, t.last_name, t.Address, t.city, 
t.state, c.customer_number, c.first_name, c.last_name, c.address, c.city, c.state
FROM Order_Table h
JOIN Customer c ON h.customer_number = c.customer_number
JOIN OrderShipping s ON h.order_number = s.order_number
JOIN Customer t ON s.customer_number = t.customer_number 
WHERE h.date > '2021/01/01' 
GROUP BY h.date, c.customer_number, c.first, c.last, c.address, c.apartment, 
c.address2, c.city, c.state, c.zip, t.customer_number, t.first, t.last, t.Address, 
t.apartment, t.Address2, t.city, t.state, t.zip 
HAVING COUNT(c.order_number) > 1

分享更多关于单个表结构和预期输出的细节，以获得更具体的答案。