Txixi的博客

题107：根据下面两表编写一个 SQL 查询，来查找这些顾客的 ID ，以及他们只光顾不交易的次数。其中：Visits表：visit_id 是主键，该表包含有关光临过购物中心的顾客的信息。Transactions表：transaction_id 是主键，此表包含 visit_id 期间进行的交易的信息。解题思路：左连接两表，再筛选t表中是null的，最后根据顾客的id分组计数即可。select v.customer_id ,count(v.visit_id) count_no_trans

题106：根据下面两个表写一个 SQL 查询来报告, 每个仓库的存货量是多少立方英尺。其中：Warehouse表：name和 product_id 是该表主键，该表的行包含了每个仓库的所有商品信息；Products表：product_id 是该表主键，该表的行包含了每件商品以英尺为单位的尺寸(宽度, 长度和高度)信息。解题思路：（1）分组；（2）计算每个商品的立方英尺；（3）求和；（5）内连接即可。select name WAREHOUSE_NAME,sum(p.VOLUME_1*

题105：根据下表写一个查询语句来按月统计金额（invoice）大于 $20 的唯一 订单数 和唯一 顾客数 。其中：order_id 是 Orders 表的主键，这张表包含顾客(customer_id)所下订单的信息。解题思路：（1）日期格式化（2）按月份分组（3）注意顾客要去重select date_format(order_date, '%Y-%m') month, count(order_id) order_count, count(distinct cu

题104：根据下面两表写一条 SQL 语句，查询：user_id 用户 IDuser_name 用户名credit 完成交易后的余额credit_limit_breached 检查是否透支 （“Yes” 或 “No”）其中：Users表：user_id 是主键，表中的每一列包含每一个用户当前的额度信息；Transactions表：trans_id 是主键，表中的每一列包含银行的交易信息，ID 为 paid_by 的用户给 ID 为 paid_to 的用户转账。解题思路：（1）分

题103：根据下表写一个SQL 语句, 找到每件商品的最新订单(可能有多个)，返回的结果以 product_name 升序排列, 如果有排序相同, 再以 product_id 升序排列， 如果还有排序相同, 再以 order_id 升序排列。其中：Orders表：order_id 是该表主键，该表包含消费者customer_id产生的订单，不会有商品被相同的用户在一天内下单超过一次；Products表：product_id 是该表主键，该表包含所有商品的信息。解题思路：（1）首先红区每个产

题102：根据下表写一个 SQL 语句报告每个月的销售情况：product_name 是小写字母且不包含前后空格；sale_date 格式为 (‘YYYY-MM’) ；total 是产品在本月销售的次数；要求：返回结果以 product_name 升序 排列，如果有排名相同，再以 sale_date 升序 排列。其中：sale_id 是该表主键，该表的每一行包含了产品的名称及其销售日期。解题思路：（1）用lower（）转化成小写字母（2）用trim（）去掉空格（3）用date_fo

题101：根据下表写一条 SQL 语句，查询患有 I 类糖尿病的患者 ID （patient_id）、患者姓名（patient_name）以及其患有的所有疾病代码（conditions）。I 类糖尿病的代码总是包含前缀 DIAB1 。其中：patient_id （患者 ID）是该表的主键，‘conditions’ （疾病）包含 0 个或以上的疾病代码，以空格分隔，这个表包含医院中患者的信息。解题思路：双反斜杠\表示转义；一个点表示匹配\n字符外的所有字符*+号属于贪婪匹配；？属于非贪婪匹配

题99：根据下表写一个 SQL 语句, 报告在 2020 年 6 月份播放的儿童适宜电影的去重电影名。其中：TVProgram表：program_date, content_id是主键，该表包含电视上的节目信息，content_id 是电视一些频道上的节目的 id；Content表：content_id 是该表主键，Kids_content 是枚举类型, 取值为(‘Y’, ‘N’), 其中: ‘Y’ 表示儿童适宜内容, 而’N’表示儿童不宜内容；content_type 表示内容的类型, 比如

题100：根据下表写一条 SQL 语句，查询拥有有效邮箱的用户。其中有效的邮箱包含符合下列条件的前缀名和域名：前缀名是包含字母（大写或小写）、数字、下划线 ‘_’、句点 ‘.’ 和/或横杠 ‘-’ 的字符串。前缀名必须以字母开头，域名是 ‘@leetcode.com’ 。解题思路：REGEXP 就是 regular expression 正则表达式 的意思：^ 表示以后面的字符为开头；[] 表示括号内任意字符；−-− 表示连续；∗*∗ 表示重复前面任意字符任意次数；\ 用来转义后面的特

题98：根据下表编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称，每个日期的销售产品名称应按词典序排列，返回按 sell_date 排序的结果表。其中：此表没有主键，它可能包含重复项，此表的每一行都包含产品名称和在市场上销售的日期。解题思路：（1）根据sell_date分组；（2）根据sell_date排序；（3）用分组拼接函数 group_concat()将每个日期的销售的产品拼接；（4）注意去重。select sell_date ,count(distinct pr

题97：根据下表写一条查询 SQL 来查找每个员工的税后工，按任意顺序返回结果，税后工资结果取整。其中：每个公司的税率计算依照以下规则如果这个公司员工最高工资不到 1000 ，税率为 0%；如果这个公司员工最高工资在 1000 到 10000 之间，税率为 24%，如果这个公司员工最高工资大于 10000 ，税率为 49%。解题思路：这题思路很简单就是用（case when max（）over（partition by））找到每个公司最高工资然后做判断再算税后工资即可。select

题96：根据下表写一个 SQL 语句，报告由表中任意两点可以形成的所有 边与坐标轴平行且面积不为零的矩形。结果表中的每一行包含三列 (p1, p2, area) 如下:p1 和 p2 是矩形两个对角的 id，矩形的面积由列 area 表示要求：按照面积 area 大小降序排列；如果面积相同的话, 则按照 p1 升序排序；若仍相同，则按 p2 升序排列。解题思路：用自连接即可（1）abs(a.x_value - b.x_value) * abs(a.y_value - b.y_value)计算两个

题95：根据下表写一个 SQL 查询, 报告每一天 苹果 和 桔子 销售的数目的差异，返回的结果表, 按照格式为 (‘YYYY-MM-DD’) 的 sale_date 排序。其中：sale_date,fruit)是主键，该表包含了每一天中"苹果" 和 "桔子"的销售情况。解题思路：sum内嵌套case when就把苹果和桔子的差异求出，用group by来对日期分组即可。select sale_date , sum( case when fruit = 'apples'

题94：根据下面两个表写一个 SQL 查询, 以计算表 Expressions 中的布尔表达式。其中：Variables 表：name 是主键，该表包含了存储的变量及其对应的值；Expressions 表：eft_operand, operator, right_operand)是主键，该表包含了需要计算的布尔表达式，operator 是枚举类型, 取值于(’<’, ‘>’, ‘=’)left_operand 和 right_operand 的值保证存在于 Variables 表

题93：根据下表写一个SQL查询来报告（访问时长区间，会话总数），结果可用任何顺序呈现。其中：决定统计访问时长区间分别为 “[0-5>”, “[5-10>”, “[10-15>” 和 “15 or more” （单位：分钟）的会话数量，并以此绘制柱状图。其中：session_id 是该表主键，duration 是用户访问应用的时间, 以秒为单位。解题思路：用union将每个区间连接，统计每个区间的个数即可。select '[0-5>' bin,count(duratio

题92：根据下面两表写一个 SQL, 找到 Queries 表中每一次查询的净现值。其中：NPV表：id, year 是该表主键，该表有每一笔存货的年份, id 和对应净现值的信息；Queries表：id, year 是该表主键，该表有每一次查询所对应存货的 id 和年份的信息。解题思路：此题需要注意的是Queries 表中有些在NPV 表中没有匹配的npv为null，用ifnull判断，连接两表即可。select q.id,q.year,ifnull(npv,0) npv from

题91：根据下面两表写一个 SQL 语句，找出在 所有 测验中都处于中游的学生 (student_id, student_name)，不要返回从来没有参加过测验的学生。返回结果表按照 student_id 排序。其中：成绩处于中游的学生是指至少参加了一次测验, 且得分既不是最高分也不是最低分的学生。其中：Student表：student_id 是该表主键，student_name 学生名字.Exam 表：exam_id, student_id是该表主键，学生 student_id 在测验 e

题90：根据下面两表写一段 SQL , 报告每个用户的旅行距离。返回的结果表单，以 travelled_distance 降序排列 ，如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列 。其中：Users表：id 是该表单主键，name 是用户名字；Rides 表：id 是该表单主键，user_id 是本次行程的用户的 id, 而该用户此次行程距离为 distance 。解题思路：（1）利用ifnull（）函数将没有旅行距离的旅行者返回为0（2）用sum()计算旅行

题89：根据下面两表编写SQL 查询来报告购买了产品 A 和产品 B 却没有购买产品 C 的顾客的 ID 和姓名（ customer_id 和 customer_name ），我们将基于此结果为他们推荐产品 C ，您返回的查询结果需要按照 customer_id 排序。其中：Customers表：customer_id 是主键，customer_name 是顾客的名称；Orders表：order_id 是主键，customer_id 是购买了名为 “product_name” 产品顾客的id。

题88：根据下面两个表请写出查询语句，查询出每一个球员赢得大满贯比赛的次数，结果不包含没有赢得比赛的球员的ID 。其中：Players表：player_id 是这个表的主键，这个表的每一行给出一个网球运动员的 ID 和 姓名；Championships表：year 是这个表的主键，该表的每一行都包含在每场大满贯网球比赛中赢得比赛的球员的 ID。解题思路：（1）先用union all行转列（2）连接（1）和Players表（3）根据player_id进行分组（4）查询想要的字段sel

题87：根据下表编写一个SQL查询来报告每支股票的资本损益，股票的资本损益是一次或多次买卖股票后的全部收益或损失。其中：stock_name, day是主键，operation 列使用的是一种枚举类型，包括：(‘Sell’,‘Buy’)，此表的每一行代表了名为 stock_name 的某支股票在 operation_day 这一天的操作价格，保证股票的每次’Sell’操作前，都有相应的’Buy’操作。解题思路：（1）通过group by实现对组数据处理（2）利用sum进行数据统计（3）通过ca

题86：根据下面两个表写一段SQL查询来展示每位用户的 唯一标识码（unique ID ）；如果某位员工没有唯一标识码，使用 null 填充即可。其中：Employees表：id 是主键，这张表的每一行分别代表了某公司其中一位员工的名字和 ID ；EmployeeUNI表：id, unique_id 是主键，这张表的每一行包含了该公司某位员工的 ID 和他的唯一标识码（unique ID）。解题思路：（1）要用外连，确保所有的名字都出现在结果中；（2）注意NULL的情况：IFNULL()

题85：根据下表写一条SQL查询展示每一位用户最近第二次的活动，如果用户仅有一次活动，返回该活动。其中：该表不包含主键，该表包含每个用户在一段时间内进行的活动的信息，名为 username 的用户在 startDate 到 endDate 日内有一次活动。解题思路：用两个窗口函数，计算第二个活动，如果只有一个也输出case即可。select u.username,u.activity,u.startDate,u.endDatefrom( select *, rank() over

题84：根据下面三个表为每张发票 invoice_id 编写一个SQL查询以查找以下内容：customer_name：与发票相关的顾客名称；price：发票的价格；contacts_cnt：该顾客的联系人数量；trusted_contacts_cnt：可信联系人的数量：既是该顾客的联系人又是商店顾客的联系人数量（即：可信联系人的电子邮件存在于客户表中）；将查询的结果按照 invoice_id 排序。其中：Customers表：customer_id 是这张表的主键，此表的每一行包含

题83：根据下面两表写一条 SQL 语句以查询那些所在院系不存在的学生的 id 和姓名。其中：Departments表：id 是主键，该表包含一所大学每个院系的 id 信息；Students表：id 是主键，该表包含一所大学每个学生的 id 和他/她就读的院系信息。解题思路：外连接+子查询select s.id,s.name from Students sleft join Departments d on s.department_id = d.id--连接两表where d.

题82：根据下表写一个 SQL 语句，要求获取在 2020 年 2 月份下单的数量不少于 100 的产品的名字和数目。其中：Products表：product_id 是该表主键，该表包含该公司产品的数据；Orders表：该表无主键，可能包含重复行，product_id 是表单 Products 的外键，unit 是在日期 order_date 内下单产品的数目。解题思路：（1）根据p.product_id分组；（2）判断日期在2020年2月；（3）连接两表即可。select p.pr

题81：根据下表写一条SQL语句来查询每一条广告的 ctr ， ctr 要保留两位小数。结果需要按 ctr 降序、按 ad_id 升序 进行排序。其中：ad_id, user_id 是主键，该表的每一行包含一条广告的 ID(ad_id)，用户的 ID(user_id) 和用户对广告采取的行为 (action)action 列是一个枚举类型 (‘Clicked’, ‘Viewed’, ‘Ignored’) 。解题思路：（1）根据ad_id 分组（2）用sum（）做计算（3）用ifnull（）

题80：根据下表写一条SQL语句查询每种性别在每一天的总分，并按性别和日期对查询结果排序。其中：(gender, day)是主键，一场比赛是在女队和男队之间举行的该表的每一行表示一个名叫 (player_name) 性别为 (gender) 的参赛者在某一天获得了 (score_points) 的分数，如果参赛者是女性，那么 gender 列为 ‘F’，如果参赛者是男性，那么 gender 列为 ‘M’。解题思路：利用窗口函数：over (partition by<用来分组的列名> o

题79：根据下表编写一个 SQL 查询，以求得每个员工所在团队的总人数；查询结果中的顺序无特定要求。其中：employee_id 字段是这张表的主键，表中的每一行都包含每个员工的 ID 和他们所属的团队。解题思路：除了用group by 还可以用自连接。（1）字段的获取：employee_id 可以直接查询，team_size 通过自连接获取；（2）自连接通过team_id 连接select employee_id ,( select count(* ) from Emplo

题78：根据下表写一段 SQL 来找到表中每个国家在 2019 年 11 月的天气类型。天气类型的定义如下：当 weather_state 的平均值小于或等于15返回 Cold，当 weather_state 的平均值大于或等于 25 返回 Hot，否则返回 Warm。返回顺序任意。其中：Countries表：country_id 是主键，该表的每行有 country_id 和 country_name 两列；Weather表：country_id和day 是该表的复合主键，该表的每一行记录了某

题77：根据下表编写一个 SQL 查询得到 Logs 表中的连续区间的开始数字和结束数字，将查询表按照 start_id 排序。解题思路：先将连续的数组分组，后找到每组首、尾利用ROW NUMBER()即可，（1）从表中查出所有的log_id，并且用row_number(order by log_id asc )排列出每个数的行号reference，查询的结果为t表；（2）根据t表的reference进行分组，查询每组的reference对应的最大和最小数，然后按照log_id升序排列。sel

题76：根据下表：写一段 SQL 语句，查询出每个学生参加每一门科目测试的次数，结果按 student_id 和 subject_name 排序。其中：Students表：主键为 student_id（学生ID），该表内的每一行都记录有学校一名学生的信息；Subjects表：主键为 subject_name（科目名称），每一行记录学校的一门科目名称；Examinations表：无主键，可能存在重复行；学生表里的一个学生修读科目表里的每一门科目，而这张考试表的每一行记录就表示学生表里的某个学生参

题75：根据下表用 SQL 查询出所有直接或间接向公司 CEO 汇报工作的职工的 employee_id ，由于公司规模较小，经理之间的间接关系不超过 3 个经理；可以以任何顺序返回无重复项的结果。其中：employee_id 是上表的主键。这个表中每一行中，employee_id 表示职工的 ID，employee_name 表示职工的名字，manager_id 表示该职工汇报工作的直线经理。这个公司 CEO 是 employee_id = 1 的人。解题思路：根据题目的规定经理之间的间接关系不

题74：根据下面两个表写一段 SQL 向user_id = 1 的用户，推荐其朋友们喜欢的页面。不要推荐该用户已经喜欢的页面，返回的结果中不应当包含重复项。其中：Friendship表：这张表的主键是 user1_id, user2_id，这张表的每一行代表着 user1_id 和 user2_id 之间存在着朋友关系。Likes表：这张表的主键是 user_id, page_id，这张表的每一行代表着 user_id 喜欢 page_id。解题思路：（1）首先找出user_id = 1

题73：根据下面两个表编写SQL查询以查找每种产品的平均售价，其中average_price 应该四舍五入到小数点后两位。其中：Prices表：(product_id，start_date，end_date) 是 Prices 表的主键；Prices 表的每一行表示的是某个产品在一段时期内的价格；每个产品的对应时间段是不会重叠的，这也意味着同一个产品的价格时段不会出现交叉。UnitsSold表：UnitsSold 表没有主键，它可能包含重复项；nitsSold 表的每一行表示的是每种产品的出售日

题72：根据下表编写一个 SQL 查询，以查找每个月和每个国家/地区的信息，已批准交易的数量及其总金额、退单的数量及其总金额。注意：在您的查询中，只需显示给定月份和国家，忽略所有为零的行。其中：Transactions 表：id 是这个表的主键，该表包含有关传入事务的信息，状态列是类型为 [approved（已批准）、declined（已拒绝）] 的枚举；Chargebacks 表：退单包含有关放置在事务表中的某些事务的传入退单的基本信息，trans_id 是 transactions 表的

题71：根据下表编写 SQL 语句以查找每个帖子的评论数。其中：上表没有主键, 所以可能会出现重复的行；每行可以是一个帖子或对该帖子的评论；如果是帖子的话，parent_id 就是 null；对于评论来说，parent_id 就是表中对应帖子的 sub_id。解题思路：（1）首先我们需要找到所有帖子的 id，通过题目我们知道所有帖子的 parent_id 为 null，因此我们可以使用 WHERE 查询找到所有的帖子；（2）然后根据找到的帖子的 post_id 找到对应的 sub_id，采用左连

题70：写一条 SQL 查询语句查找最后一个能进入电梯且不超过重量限制的 person_name 。题目确保队列中第一位的人可以进入电梯 。其中：person_id 是这个表的主键；该表展示了所有等待电梯的人的信息。表中 person_id 和 turn 列将包含从 1 到 n 的所有数字，其中 n 是表中的行数。解题思路：利用自连接，根据 turn 排序， 并累加 weight，找到最后一个使得总和小于等于 1000 的 person_name。select b.person_namefrom

题69：根据下表写一条 SQL 查询语句获取即时订单在所有用户的首次订单中的比例，结果保留两位小数。其中：delivery_id 是表的主键，该表保存着顾客的食物配送信息，顾客在某个日期下了订单，并指定了一个期望的配送日期（和下单日期相同或者在那之后）。解题思路：（1）按照用户分组，筛选出所有首次订单 ；（2）针对首次订单遍历记录并标记。select round ( sum(order_date = customer_pref_delivery_date) * 100 / co

题68：根据下面两个表请写出一条SQL语句以查询每个用户的注册日期和在 2019 年作为买家的订单总数。其中：Users表：主键是 user_id，表中描述了购物网站的用户信息，用户可以在此网站上进行商品买卖；Orders表：主键是 order_id，外键是 item_id 和（buyer_id，seller_id）。解题思路：select user_id buyer_id,join_date,count(buyer_id ) orders_in_2019 --统计2019年购买单数