一个SQL笔试题

发表于

txn记录了不同店铺每天每个商品的销售记录。请用SQL把最近100天，至少有20天销售记录的商品ID找出来，并按销售天数倒序排序。
表结构如下：

字段 类型 注释
item_id integer 商品ID
sale_date datetime 销售日期
store_id varchar(20) 门店ID
quantity real 商品销售数量

创建表

1
2
3
4
5
6
7

CREATE TABLE txn (
  item_id   INTEGER,
  sale_date DATETIME,
  store_id  VARCHAR(20),
  quantity  REAL
);

添加数据

1
2
3
4
5
6
7
8
9
10
11
12
13
INSERT txn VALUES
  (1, '2018-03-03', '1', 200),
  (1, '2018-03-04', '2', 200),
  (1, '2018-03-05', '3', 200),
  (1, '2018-03-06', '4', 200),
  (1, '2018-03-07', '5', 200),
  (2, '2017-11-30', '1', 200),
  (2, '2017-12-01', '2', 200),
  (2, '2017-12-02', '3', 200),
  (2, '2018-03-03', '4', 200),
  (2, '2018-03-04', '5', 200),
  (2, '2018-03-05', '6', 200),
  (2, '2018-03-06', '7', 200);

20天数据过多，就用5天代替
datediff(now(), sale_date)返回当前日期和销售日期相差的天数
datediff(‘2018-03-10’,’2017-11-30’)函数计算两个日期差多少天，now()表示当前的datetime
datediff() 第一个日期比第二个日期大，值为正，反之为负。

1
2
SELECT now(); # 2018-03-10 17:34:58
SELECT datediff('2018-03-10','2017-11-30'); # 100

答案：

1
2
3
4
5
6
7
8
SELECT
  item_id,
  count(DISTINCT (sale_date)) AS sale_day
FROM txn
WHERE datediff(now(), sale_date) <= 100
GROUP BY item_id
HAVING sale_day >= 5
ORDER BY sale_day DESC;

答案