首页 > 编程学习 > 实验5 SQL复杂业务查询

实验5 SQL复杂业务查询

发布时间:2022/11/17 20:30:17

第1关 查找购买个数超过20,重量小于50的商品,按照商品id升序排序

描述
如下有一张商品表(goods),字段依次为:商品id、商品名、商品质量

+------+------+--------+
|   id | name | weight |
+------+------+--------+
|    1 | A1   |    100 |
|    2 | A2   |     20 |
|    3 | B3   |     29 |
|    4 | T1   |     60 |
|    5 | G2   |     33 |
|    6 | C0   |     55 |
+------+------+--------+

还有一张交易表(trans),字段依次为:交易id、商品id、这个商品购买个数

+------+----------+-------+
|tran_id|       id | count |
+------+----------+-------+
|    1 |        3 |    10 |
|    2 |        1 |    44 |
|    3 |        6 |     9 |
|    4 |        1 |     2 |
|    5 |        2 |    65 |
|    6 |        5 |    23 |
|    7 |        3 |    20 |
|    8 |        2 |    16 |
|    9 |        4 |     5 |
|   10 |        1 |     3 |
+------+----------+-------+

查找购买个数超过20,重量小于50的商品,按照商品id升序排序,如:

+------+-------+
| id   | total |
+------+-------+
|    2 |    81 |
|    3 |    30 |
|    5 |    23 |
+------+-------+

示例1
输入:

CREATE TABLE goods (
  id int(11) NOT NULL,
  name varchar(10)  DEFAULT NULL,
  weight int(11) NOT NULL,
  PRIMARY KEY (id)
);
CREATE TABLE trans (
  tran_id int(11) NOT NULL,
  id int(11) NOT NULL,
  count int(11) NOT NULL,
  PRIMARY KEY (tran_id)
);
insert into goods values(1,'A1',100);
insert into goods values(2,'A2',20);
insert into goods values(3,'B3',29);
insert into goods values(4,'T1',60);
insert into goods values(5,'G2',33);
insert into goods values(6,'C0',55);
insert into trans values(1,3,10);
insert into trans values(2,1,44);
insert into trans values(3,6,9);
insert into trans values(4,1,2);
insert into trans values(5,2,65);
insert into trans values(6,5,23);
insert into trans values(7,3,20);
insert into trans values(8,2,16);
insert into trans values(9,4,5);
insert into trans values(10,1,3);

输出:

id    total
2    81
3    30
5    23

任务描述 假设音乐数据库里面现在有几张如下简化的数据表:
关注follow表,第一列是关注人的id,第二列是被关注人的id,这2列的id组成主键

+---------+-------------+
| user_id | follower_id |
+---------+-------------+
|       1 |           2 |
|       1 |           4 |
|       2 |           3 |
+---------+-------------+

这张表的第一行代表着用户id为1的关注着id为2的用户
这张表的第二行代表着用户id为1的关注着id为4的用户
这张表的第三行代表着用户id为2的关注着id为3的用户

个人的喜欢的音乐music_likes表,第一列是用户id,第二列是喜欢的音乐id,这2列的id组成主键

+---------+----------+
| user_id | music_id |
+---------+----------+
|       1 |       17 |
|       2 |       18 |
|       2 |       19 |
|       3 |       20 |
|       4 |       17 |
+---------+----------+

这张表的第一行代表着用户id为1的喜欢music_id为17的音乐 …
这张表的第五行代表着用户id为4的喜欢music_id为17的音乐

音乐music表,第一列是音乐id,第二列是音乐name,id是主键

+----+------------+
| id | music_name |
+----+------------+
| 17 | yueyawang  |
| 18 | kong       |
| 19 | MOM        |
| 20 | Sold Out   |
+----+------------+

请你编写一个MYSQL,查询向user_id = 1的用户,推荐其关注的人喜欢的音乐。不要推荐该用户已经喜欢的音乐,并且按music的music_name升序排列。你返回的结果中不应当包含重复项上面的查询结果如下:

+------------+
| music_name |
+------------+
| kong       |
| MOM        |
+------------+

示例1
输入:

CREATE TABLE follow (
user_id int(4) NOT NULL,
follower_id int(4) NOT NULL,
PRIMARY KEY (user_id,follower_id));

CREATE TABLE music_likes (
user_id int(4) NOT NULL,
music_id int(4) NOT NULL,
PRIMARY KEY (user_id,music_id));

CREATE TABLE music (
id int(4) NOT NULL,
music_name varchar(32) NOT NULL,
PRIMARY KEY (id));

INSERT INTO follow VALUES(1,2);
INSERT INTO follow VALUES(1,4);
INSERT INTO follow VALUES(2,3);

INSERT INTO music_likes VALUES(1,17);
INSERT INTO music_likes VALUES(2,18);
INSERT INTO music_likes VALUES(2,19);
INSERT INTO music_likes VALUES(3,20);
INSERT INTO music_likes VALUES(4,17);

INSERT INTO music VALUES(17,'yueyawang');
INSERT INTO music VALUES(18,'kong');
INSERT INTO music VALUES(19,'MOM');
INSERT INTO music VALUES(20,'Sold Out');

输出:

music_name
kong
MOM

任务描述
假设音乐数据库里面现在有几张如下简化的数据表:
关注follow表,第一列是关注人的id,第二列是被关注人的id,这2列的id组成主键

+---------+-------------+
| user_id | follower_id |
+---------+-------------+
|       1 |           2 |
|       1 |           4 |
|       2 |           3 |
+---------+-------------+

这张表的第一行代表着用户id为1的关注着id为2的用户
这张表的第二行代表着用户id为1的关注着id为4的用户
这张表的第三行代表着用户id为2的关注着id为3的用户

个人的喜欢的音乐music_likes表,第一列是用户id,第二列是喜欢的音乐id,这2列的id组成主键

+---------+----------+
| user_id | music_id |
+---------+----------+
|       1 |       17 |
|       2 |       18 |
|       2 |       19 |
|       3 |       20 |
|       4 |       17 |
+---------+----------+

这张表的第一行代表着用户id为1的喜欢music_id为17的音乐 …
这张表的第五行代表着用户id为4的喜欢music_id为17的音乐
音乐music表,第一列是音乐id,第二列是音乐name,id是主键

+----+------------+
| id | music_name |
+----+------------+
| 17 | yueyawang  |
| 18 | kong       |
| 19 | MOM        |
| 20 | Sold Out   |
+----+------------+

请你编写一个MYSQL,查询向follow表中user_id用户,推荐其关注的人喜欢的音乐。不要推荐该用户已经喜欢的音乐,并且先按follow的user_id升序排列,再按music的music_name升序排列。你返回的结果中不应当包含重复项上面的查询结果如下:

+------------+
| music_name |
+------------+
| kong       |
| MOM        |
+------------+

示例1
输入:

CREATE TABLE follow (
user_id int(4) NOT NULL,
follower_id int(4) NOT NULL,
PRIMARY KEY (user_id,follower_id));

CREATE TABLE music_likes (
user_id int(4) NOT NULL,
music_id int(4) NOT NULL,
PRIMARY KEY (user_id,music_id));

CREATE TABLE music (
id int(4) NOT NULL,
music_name varchar(32) NOT NULL,
PRIMARY KEY (id));

INSERT INTO follow VALUES(1,2);
INSERT INTO follow VALUES(1,4);
INSERT INTO follow VALUES(2,3);

INSERT INTO music_likes VALUES(1,17);
INSERT INTO music_likes VALUES(2,18);
INSERT INTO music_likes VALUES(2,19);
INSERT INTO music_likes VALUES(3,20);
INSERT INTO music_likes VALUES(4,17);

INSERT INTO music VALUES(17,'yueyawang');
INSERT INTO music VALUES(18,'kong');
INSERT INTO music VALUES(19,'MOM');
INSERT INTO music VALUES(20,'Sold Out');

输出:

user_id    music_name
1    kong
1    MOM
2    Sold Out

任务描述
现有3张业务表,详见如下:
在这里插入图片描述
需要输出结果如下,没有支付的日期不需要显示,请写出对应的MYSQL
在这里插入图片描述
示例1
输入:

CREATE TABLE new_user(
  user_id int(11) NOT NULL,
  is_new int(11) NOT NULL,
  PRIMARY KEY (user_id)
);
CREATE TABLE user_pay(
  user_id int(11) NOT NULL,
  pay_money int(11) NOT NULL,
  dt char(20) NOT NULL
);
CREATE TABLE login_record(
  user_id int(11) NOT NULL,
  login_time char(20) NOT NULL,
  dt char(20) NOT NULL
);
insert into new_user values(1,0);
insert into new_user values(2,1);
insert into new_user values(3,1);
insert into new_user values(4,0);
insert into new_user values(5,0);
insert into new_user values(6,1);
insert into new_user values(7,0);
insert into new_user values(8,1);
insert into user_pay values(1,30,'2021-11-10');
insert into user_pay values(1,100,'2021-11-10');
insert into user_pay values(2,500,'2021-11-11');
insert into user_pay values(2,200,'2021-11-12');
insert into user_pay values(3,1000,'2021-11-10');
insert into user_pay values(4,800,'2021-11-12');
insert into user_pay values(6,1200,'2021-11-10');
insert into user_pay values(6,700,'2021-11-14');
insert into login_record values(1,'8:00','2021-11-10');
insert into login_record values(1,'12:00','2021-11-10');
insert into login_record values(1,'13:00','2021-11-10');
insert into login_record values(2,'16:00','2021-11-10');
insert into login_record values(2,'12:35','2021-11-11');
insert into login_record values(2,'18:34','2021-11-12');
insert into login_record values(3,'20:00','2021-11-10');
insert into login_record values(4,'21:00','2021-11-12');
insert into login_record values(5,'21:00','2021-11-13');
insert into login_record values(6,'22:30','2021-11-10');
insert into login_record values(6,'13:30','2021-11-14');

输出:

dt    dau    dau_new    total_pay    total_pay_new
2021-11-10    4    3    2330    2200
2021-11-11    1    1    500    500
2021-11-12    2    1    1000    200
2021-11-14    1    1    700    700

任务描述
本关任务:
查询某网站每个日期新用户的次日留存率。

相关知识
某网站每天有很多人登录,请你统计一下该网站每个日期新用户的次日留存率。
有一个登录(login)记录表,简况如下:

在这里插入图片描述

第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了该网站,因为是第1次登录,所以是新用户。
第4行表示user_id为2的用户在2020-10-13使用了客户端id为2的设备登录了该网站,因为是第2次登录,所以是老用户。
最后1行表示user_id为4的用户在2020-10-15使用了客户端id为1的设备登录了该网站,因为是第2次登录,所以是老用户。
请你写出一个sql语句查询每个日期新用户的次日留存率,结果保留小数点后面3位数(3位之后的四舍五入),并且查询结果按照日期升序排序,上面的例子查询结果如下:

在这里插入图片描述

查询结果表明:
2020-10-12登录了3个(user_id为2,3,1)新用户,2020-10-13,只有2个(id为2,1)登录,故2020-10-12新用户次日留存率为2/3=0.667;

2020-10-13没有新用户登录,输出0.000;

2020-10-14登录了1个(user_id为4)新用户,2020-10-15,user_id为4的用户登录,故2020-10-14新用户次日留存率为1/1=1.000;

2020-10-15没有新用户登录,输出0.000;

注意:
1、本题的新用户不是真正的新用户定义,是本题给出的新用户定义;
2、MYSQL里计算日期t2与日期t1差的函数为:datediff(t2,t1);
3、MYSQL对某字段total四舍五入保留3位的函数为:round(total,3);
4、MYSQL对把某字段total为空的值置为0的函数为:ifnull(total,0)。

示例1
输入:

drop table if exists login;
CREATE TABLE login (
id int(4) NOT NULL,
user_id int(4) NOT NULL,
client_id int(4) NOT NULL,
date date NOT NULL,
PRIMARY KEY (id));

INSERT INTO login VALUES
(1,2,1,'2020-10-12'),
(2,3,2,'2020-10-12'),
(3,1,2,'2020-10-12'),
(4,2,2,'2020-10-13'),
(5,1,2,'2020-10-13'),
(6,3,1,'2020-10-14'),
(7,4,1,'2020-10-14'),
(8,4,1,'2020-10-15');

输出:

date    p
2020-10-12    0.667
2020-10-13    0.000
2020-10-14    1.000
2020-10-15    0.000

任务描述
有的同学会购买网上训练课程来学习,某教育网站对购买记录会产生订单存到数据库,有一个订单信息表(order_info),表内容如下:

在这里插入图片描述

第1行表示user_id为557336的用户在2021-10-10的时候使用了client_id为1的客户端下了C++课程的订单,但是状态为没有购买成功。第2行为购买成功。

查询满足以下条件的用户:
在2021-10-15以后,如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程,那么输出这个用户的user_id,以及满足该条件的第一次购买成功的日期first_buy_date,以及满足前面条件的第二次购买成功的日期second_buy_date,以及购买成功的次数cnt,并且输出结果按照user_id升序排序。

函数提示
1、row_number() over(partition by某字段 order by 某字段):窗口函数按某字段分组,生成从1开始的顺序编号。
2、count(*) over(partition by 某字段):求分组后的总数。

示例1
输入:

drop table if exists order_info;
CREATE TABLE order_info(
id int(4) NOT NULL,
user_id int(4) NOT NULL,
product_name char(20) NOT NULL,
status char(20) NOT NULL,
client_id int(4) NOT NULL,
date date NOT NULL,
PRIMARY KEY (id));

INSERT INTO order_info VALUES
(1,557336,'C++','no_completed',1,'2021-10-10'),
(2,230173543,'Python','completed',2,'2021-10-12'),
(3,230173543,'Python','completed',2,'2021-10-15'),
(4,557336,'Java','completed',1,'2021-10-15'),
(5,230173543,'Java','no_completed',2,'2021-10-16'),
(6,230173543,'Java','completed',2,'2021-10-20'),
(7,230173543,'C++','completed',2,'2021-10-21'),
(8,557336,'Java','completed',1,'2021-10-21'),
(9,557336,'离散数学','completed',1,'2021-10-22'),
(10,230173543,'离散数学','completed',2,'2021-10-22'),
(11,663466,'离散数学','completed',3,'2021-10-22'),
(12,663466,'C++','completed',3,'2021-10-22'),
(13,663466,'Python','no_completed',3,'2021-10-23'),
(14,663466,'Python','completed',3,'2021-10-24'),
(15,663466,'C++','completed',3,'2021-10-25'),
(16,8912311,'Java','completed',1,'2021-10-25'),
(17,3345600,'高等数学','completed',1,'2021-10-27'),
(18,3345600,'数学分析','completed',1,'2021-10-28'),
(19,3345600,'数据库系统','completed',1,'2021-10-28');

输出:

user_id    first_buy_date    second_buy_date    cnt
663466    2021-10-22    2021-10-24    3
230173543    2021-10-20    2021-10-21    2

任务描述
查询各工程号最大的按零件号合计供应量以及该零件号,并先按工程号升序,再按零件号升序排序。

相关知识
供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成,标识某供应商
供应某种零件 给某工程项目的数量为QTY。
SPJ表如下图:

在这里插入图片描述

现已构建SPJ表,结构信息如下:

在这里插入图片描述

Copyright © 2010-2022 dgrt.cn 版权所有 |关于我们| 联系方式