mysql 查询实战3-题目

        看了mysql 查询实战2-解答，里面有个新知识，异或的解决交换座位的处理，接着看实战3的题目

11、查询每⽉产品交易与退款情况

        目标： 查询每⽉产品交易（交易总额，交易数）与退款情况（退款总额，退款数）

1，建表

create table transactions(
  id int not null auto_increment,
  product_id int,
  state enum('success','proceding'),
  amount int default 0,
  trans_date date,
  primary key(id)
);


create table refund(trans_id int,trans_date date);

2，插入数据

insert into transactions values
(1,1001,'success',1000,'2020-01-02'),
(2,1002,'proceding',2000,'2020-01-03'),
(3,1001,'success',3000,'2020-01-10'),
(4,1001,'success',4000,'2020-02-13'),
(5,1002,'success',5000,'2020-02-15');


insert into refund values
(2,'2020-02-29'),
(1,'2020-02-10'),
(5,'2020-03-30');

12、查询活动产品的平均价格

1，建表

create table activity(
  id int not null auto_increment,
  product_id int,
  price int default 0,
  start_date date,
  end_date date,
  primary key(id)
);

create table sold(
  product_id int,
  num int,
  purchase_date date
);

2，插入数据

insert into activity values
(1,1001,100,'2020-01-15','2020-01-20'),
(2,1001,200,'2020-02-02','2020-02-15'),
(3,1002,300,'2020-01-15','2020-01-20'),
(4,1002,400,'2020-02-02','2020-02-15');


insert into sold values
(1001,30,'2020-01-19'),
(1001,10,'2020-01-19'),
(1001,20,'2020-02-10'),
(1002,40,'2020-01-20'),
(1002,60,'2020-02-11');

13、统计部⻔每⽉收⼊概况

        每月，就是1到12月的收入情况

1，建表

create table department_income(
  department_id int,
  income int,
  month char(3)
);

2，插入数据

insert into department_income(department_id,income,month)values
(1,20,'Jan'),
(2,20,'Jan'),
(2,20,'Feb'),
(1,20,'Mar'),
(3,20,'Apr'),
(1,20,'May'),
(2,20,'Jun'),
(1,20,'Jul'),
(2,20,'Aug'),
(3,20,'Sep'),
(1,20,'Oct'),
(2,20,'Nov'),
(1,20,'Dec'),
(1,30,'Jan'),
(2,40,'Feb'),
(1,50,'Mar'),
(3,60,'Apr'),
(1,70,'May'),
(2,80,'Jun'),
(1,90,'Jul'),
(2,80,'Aug'),
(3,70,'Sep'),
(1,60,'Oct'),
(2,50,'Nov'),
(1,30,'Dec');

14、统计课程新学员数量

        统计最近指定天数，比如180天内容，每天课程的新学员的数量

1，建表

DROP TABLE IF EXISTS learning_records;
create table learning_records(
  user_id int,
  course_id int,
  created_at date
);

2，插入数据

insert into learning_records values
(1, 3, '2020-02-01'),
(1, 3, '2020-02-03'),
(1, 3, '2020-02-05'),
(2, 3, '2020-04-04'),
(2, 3, '2020-04-11'),
(2, 3, '2020-05-20'),
(3, 3, '2020-02-02'),
(3, 3, '2020-03-01'),
(4, 3, '2020-03-01'),
(4, 3, '2019-03-15'),
(5, 3, '2020-04-04'),
(5, 3, '2020-04-06'),
(1, 10, '2019-11-01'),
(2, 10, '2020-03-01'),
(3, 10, '2020-05-01');

15、平均销售额：部⻔与公司对⽐

        目标： 计算每月部门和公司的收入比较

1，建表

create table income(
  id int not null auto_increment,
  sale_id int,
  income int,
  created_at date,
  primary key(id)
);

create table sales_person(
  id int not null auto_increment,
  department_id int,
  primary key(id)
);

2，插入数据

insert into sales_person values
(1,1),
(2,2),
(3,2);
 
insert into income values
(1, 1, 9000, '2020-04-03'),
(2, 2, 8000, '2020-04-02'),
(3, 3, 5000, '2020-04-01'),
(4, 1, 10000, '2020-05-03'),
(5, 2, 9000, '2020-05-02'),
(6, 3, 4000, '2020-05-01'),
(7, 1, 11000, '2020-03-03'),
(8, 2, 600, '2020-03-02'),
(9, 3, 15000, '2020-03-01'),
(10, 1, 17000, '2020-04-06'),
(11, 2, 5000, '2020-04-08'),
(12, 3, 7000, '2020-04-09');

总结：

        这次的题目，更不好理解一些。新学员的那个情况，会比较常见一些，比如多久后再登录。

        上一篇： 《mysql 查询实战2-解答》

        下一篇： 《》