时间戳

首页 > 解决方案 > SQL初学者练习题

mysql - SQL初学者练习题

问题描述

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

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

1) 找出在 2018 年 7 月 9customers日至少制作了一个order的date人数

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) 找出2018 年 7 月 9 日customers未参加比赛的order人数

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 日date之间销售额最高的

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
)

谢谢，

标签： mysqlsql

解决方案

对于问题 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;

推荐阅读