mysql - SQL初学者练习题

Question

给定两个表，orders (order_id, date, $, customer_id) 和 customers (ID, name)

这是我的方法，但我不确定它是否有效&我想知道是否有更快/更好的方法来解决这些问题:

1) 找出在 date 7/9/2018

至少下了一个 order 的 customers 的数量

Select count (distinct customer_id)
From
(
Select customer_id from orders a 
Left join customer b
On a.customer_id = b.ID 
Group by customer_id,date
Having date = 7/9/2018
) a

2) 找出在 7/9/2018 没有下订单的客户的数量

Select count (customer_id) from customer where customer_id not in
(
Select customer_id from orders a 
Left join customer b
On a.customer_id = b.ID 
Group by customer_id,date
Having date = 7/9/2018
) 

3) 在 7 月 1 日和 7 月 30 日之间找到销售额最高的日期

select date, max($)
from (
Select sum($),date from orders a 
Left join customer b
On a.customer_id = b.ID 
Group by date
Having date between 7/1 and 7/30
)

谢谢，

Answer 1

对于问题 1，有效的解决方案可能如下所示:

SELECT COUNT(DISTINCT customer_id) x
  FROM orders 
 WHERE date = '2018-09-07';  -- or is that '2018-07-09' ??

对于问题 2，有效的解决方案可能如下所示:

SELECT COUNT(*) x
  FROM customer c
  LEFT
  JOIN orders o
    ON o.customer_id = x.customer_id
   AND o.date = '2018-07-09'
 WHERE o.crder_id IS NULL;

假设没有关系，问题 3 的有效解决方案可能如下所示:

SELECT date 
     , COUNT(*) sales 
  FROM orders 
 WHERE date BETWEEN '2018-07-01' AND '2018-07-30' 
 GROUP 
    BY date 
 ORDER 
    BY sales DESC 
 LIMIT 1;