• 力扣SQL题记录

    1193. 每月交易 I

    题目 

    SQL Schema：

    1. Create table If Not Exists Transactions (id int, country varchar(4), state enum('approved', 'declined'), amount int, trans_date date)
    2. Truncate table Transactions
    3. insert into Transactions (id, country, state, amount, trans_date) values ('121', 'US', 'approved', '1000', '2018-12-18')
    4. insert into Transactions (id, country, state, amount, trans_date) values ('122', 'US', 'declined', '2000', '2018-12-19')
    5. insert into Transactions (id, country, state, amount, trans_date) values ('123', 'US', 'approved', '2000', '2019-01-01')
    6. insert into Transactions (id, country, state, amount, trans_date) values ('124', 'DE', 'approved', '2000', '2019-01-07')

    Table: Transactions

    +---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| country       | varchar |
| state         | enum    |
| amount        | int     |
| trans_date    | date    |
+---------------+---------+
id 是这个表的主键。
该表包含有关传入事务的信息。
state 列类型为 “[”批准“，”拒绝“] 之一。

    编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。

    以 任意顺序 返回结果表。

    查询结果格式如下所示。

    示例 1:

    1. 输入：
    2. Transactions
     table:
+------+---------+----------+--------+------------+
| id   | country | state    | amount | trans_date |
+------+---------+----------+--------+------------+
| 121  | US      | approved | 1000   | 2018-12-18 |
| 122  | US      | declined | 2000   | 2018-12-19 |
| 123  | US      | approved | 2000   | 2019-01-01 |
| 124  | DE      | approved | 2000   | 2019-01-07 |
+------+---------+----------+--------+------------+
输出：
+----------+---------+-------------+----------------+--------------------+-----------------------+
| month    | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
+----------+---------+-------------+----------------+--------------------+-----------------------+
| 2018-12  | US      | 2           | 1              | 3000               | 1000                  |
| 2019-01  | US      | 1           | 1              | 2000               | 2000                  |
| 2019-01  | DE      | 1           | 1              | 2000               | 2000                  |
+----------+---------+-------------+----------------+--------------------+-----------------------+

     代码

    1. select DATE_FORMAT(trans_date, '%Y-%m') AS `month`, 
    2.         country, 
    3.         count(id) as trans_count , 
    4.         count(if(state = 'approved', 1, null)) as approved_count,
    5.         sum(amount) as trans_total_amount,
    6.         sum(if(state = 'approved', amount, 0)) as approved_total_amount 
    7. from Transactions
    8. group by `month`, country
    9. ;

    考点

    1、使用 DATE_FORMAT 按格式提取日期。这里不能直接用MOUTH()，因为只能提取出月份，而题目要求带年份，如2018-1

    2、使用if 来限定条件

    1211. 查询结果的质量和占比

    题目 

    SQL Schema

    1. Create table If Not Exists Queries (query_name varchar(30), result varchar(50), position int, rating int)
    2. Truncate table Queries
    3. insert into Queries (query_name, result, position, rating) values ('Dog', 'Golden Retriever', '1', '5')
    4. insert into Queries (query_name, result, position, rating) values ('Dog', 'German Shepherd', '2', '5')
    5. insert into Queries (query_name, result, position, rating) values ('Dog', 'Mule', '200', '1')
    6. insert into Queries (query_name, result, position, rating) values ('Cat', 'Shirazi', '5', '2')
    7. insert into Queries (query_name, result, position, rating) values ('Cat', 'Siamese', '3', '3')
    8. insert into Queries (query_name, result, position, rating) values ('Cat', 'Sphynx', '7', '4')

    查询表 Queries： 

    +-------------+---------+
| Column Name | Type    |
+-------------+---------+
| query_name  | varchar |
| result      | varchar |
| position    | int     |
| rating      | int     |
+-------------+---------+
此表没有主键，并可能有重复的行。
此表包含了一些从数据库中收集的查询信息。
“位置”（position）列的值为 1 到 500 。
“评分”（rating）列的值为 1 到 5 。评分小于 3 的查询被定义为质量很差的查询。

    将查询结果的质量 quality 定义为：

    各查询结果的评分与其位置之间比率的平均值。

    将劣质查询百分比 poor_query_percentage 为：

    评分小于 3 的查询结果占全部查询结果的百分比。

    编写一组 SQL 来查找每次查询的名称(query_name)、质量(quality) 和 劣质查询百分比(poor_query_percentage)。

    质量(quality) 和劣质查询百分比(poor_query_percentage) 都应四舍五入到小数点后两位。

    查询结果格式如下所示：

    Queries table:
+------------+-------------------+----------+--------+
| query_name | result            | position | rating |
+------------+-------------------+----------+--------+
| Dog        | Golden Retriever  | 1        | 5      |
| Dog        | German Shepherd   | 2        | 5      |
| Dog        | Mule              | 200      | 1      |
| Cat        | Shirazi           | 5        | 2      |
| Cat        | Siamese           | 3        | 3      |
| Cat        | Sphynx            | 7        | 4      |
+------------+-------------------+----------+--------+

Result table:
+------------+---------+-----------------------+
| query_name | quality | poor_query_percentage |
+------------+---------+-----------------------+
| Dog        | 2.50    | 33.33                 |
| Cat        | 0.66    | 33.33                 |
+------------+---------+-----------------------+

Dog 查询结果的质量为 ((5 / 1) + (5 / 2) + (1 / 200)) / 3 = 2.50
Dog 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33

Cat 查询结果的质量为 ((2 / 5) + (3 / 3) + (4 / 7)) / 3 = 0.66
Cat 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33

     代码

    1. select query_name, 
    2.         round(avg(rating/position), 2) as quality,
    3.         round(sum(if(rating < 3, 1, 0))* 100 / count(query_name), 2) as poor_query_percentage 
    4. from Queries
    5. group by query_name
    6. ;

     考点

    1、round()函数保留小数

    1251. 平均售价 

    题目 

    SQL Schema

    1. Create table If Not Exists Prices (product_id int, start_date date, end_date date, price int)
    2. Create table If Not Exists UnitsSold (product_id int, purchase_date date, units int)
    3. Truncate table Prices
    4. insert into Prices (product_id, start_date, end_date, price) values ('1', '2019-02-17', '2019-02-28', '5')
    5. insert into Prices (product_id, start_date, end_date, price) values ('1', '2019-03-01', '2019-03-22', '20')
    6. insert into Prices (product_id, start_date, end_date, price) values ('2', '2019-02-01', '2019-02-20', '15')
    7. insert into Prices (product_id, start_date, end_date, price) values ('2', '2019-02-21', '2019-03-31', '30')
    8. Truncate table UnitsSold
    9. insert into UnitsSold (product_id, purchase_date, units) values ('1', '2019-02-25', '100')
    10. insert into UnitsSold (product_id, purchase_date, units) values ('1', '2019-03-01', '15')
    11. insert into UnitsSold (product_id, purchase_date, units) values ('2', '2019-02-10', '200')
    12. insert into UnitsSold (product_id, purchase_date, units) values ('2', '2019-03-22', '30')

    Table: Prices

    +---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| start_date    | date    |
| end_date      | date    |
| price         | int     |
+---------------+---------+
(product_id，start_date，end_date) 是 Prices 表的主键。
Prices 表的每一行表示的是某个产品在一段时期内的价格。
每个产品的对应时间段是不会重叠的，这也意味着同一个产品的价格时段不会出现交叉。

    Table: UnitsSold

    +---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| purchase_date | date    |
| units         | int     |
+---------------+---------+
UnitsSold 表没有主键，它可能包含重复项。
UnitsSold 表的每一行表示的是每种产品的出售日期，单位和产品 id。

    编写SQL查询以查找每种产品的平均售价。
    average_price 应该四舍五入到小数点后两位。
    查询结果格式如下例所示：

    Prices table:
+------------+------------+------------+--------+
| product_id | start_date | end_date   | price  |
+------------+------------+------------+--------+
| 1          | 2019-02-17 | 2019-02-28 | 5      |
| 1          | 2019-03-01 | 2019-03-22 | 20     |
| 2          | 2019-02-01 | 2019-02-20 | 15     |
| 2          | 2019-02-21 | 2019-03-31 | 30     |
+------------+------------+------------+--------+
 
UnitsSold table:
+------------+---------------+-------+
| product_id | purchase_date | units |
+------------+---------------+-------+
| 1          | 2019-02-25    | 100   |
| 1          | 2019-03-01    | 15    |
| 2          | 2019-02-10    | 200   |
| 2          | 2019-03-22    | 30    |
+------------+---------------+-------+

Result table:
+------------+---------------+
| product_id | average_price |
+------------+---------------+
| 1          | 6.96          |
| 2          | 16.96         |
+------------+---------------+
平均售价 = 产品总价 / 销售的产品数量。
产品 1 的平均售价 = ((100 * 5)+(15 * 20) )/ 115 = 6.96
产品 2 的平均售价 = ((200 * 15)+(30 * 30) )/ 230 = 16.96

    代码 

    1. select 
    2.     p.product_id, 
    3.     round(sum(p.price * u.units) / sum(u.units), 2) as average_price 
    4. from Prices as p, UnitsSold as u
    5. where p.product_id = u.product_id 
    6.         and u.purchase_date >= p.start_date 
    7.         and u.purchase_date <= p.end_date
    8. group by p.product_id
    9. ;
    1. SELECT
    2.     product_id,
    3.     Round(SUM(sales) / SUM(units), 2) AS average_price
    4. FROM (
    5.     SELECT
    6.         Prices.product_id AS product_id,
    7.         Prices.price * UnitsSold.units AS sales,
    8.         UnitsSold.units AS units
    9.     FROM Prices 
    10.     JOIN UnitsSold ON Prices.product_id = UnitsSold.product_id
    11.     WHERE UnitsSold.purchase_date BETWEEN Prices.start_date AND Prices.end_date
    12. ) T
    13. GROUP BY product_id
    14. ;

    考点

    1、联表查询

    SQL75 检索产品名称和描述（一）

    描述

    Products表

    prod_nameprod_desc
    a0011usb
    a0019iphone13
    b0019gucci t-shirts
    c0019gucci toy
    d0019lego toy

    【问题】编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中包含 toy 一词的产品名称

    【示例结果】返回产品名称和产品描述

    prod_nameprod_desc
    c0019gucci toy
    d0019lego toy

    示例1

    输入：

    DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_desc` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('a0011','usb'),
('a0019','iphone13'),
('b0019','gucci t-shirts'),
('c0019','gucci toy'),
('d0019','lego toy');

    复制输出：

    c0019|gucci toy
d0019|lego toy

    代码 

    1. select 
    2.     prod_name,
    3.     prod_desc
    4. from 
    5.     Products
    6. where 
    7.     prod_desc like '%toy%'
    8. ;

     考点

     1、like模糊匹配

    SQL76 检索产品名称和描述（二）

    描述

    Products表

    prod_nameprod_desc
    a0011usb
    a0019iphone13
    b0019gucci t-shirts
    c0019gucci toy
    d0019lego toy

    【问题】编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中未出现 toy 一词的产品，最后按”产品名称“对结果进行排序。

    【示例结果】返回产品名称和产品描述

    prod_nameprod_desc
    a0011usb
    a0019iphone13
    b0019gucci t-shirts

    示例1

    输入：

    DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_desc` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('a0011','usb'),
('a0019','iphone13'),
('b0019','gucci t-shirts'),
('c0019','gucci toy'),
('d0019','lego toy');

    复制输出：

    a0011|usb
a0019|iphone13
b0019|gucci t-shirts

    代码 

    1. select 
    2.     prod_name,
    3.     prod_desc
    4. from 
    5.     Products
    6. where
    7.     prod_desc not like '%toy%'
    8. order by 
    9.     prod_name
    10. ;
    1. # 2.instr(filed,str)函数：返回str子字符串在filed字符串的第一次出现位置
    2. select prod_name,prod_desc from Products
    3. where instr(prod_desc,"toy") = 0
    4. order by prod_name asc
    1. # 3.locate函数：Locate(str,sub) > 0，表示sub字符串包含str字符串；Locate(str,sub) = 0，表示sub字符串不包含str字符串。
    2. select prod_name,prod_desc from Products
    3. where locate("toy",prod_desc) = 0
    4. order by prod_name asc
    1. #4.正则匹配前加not
    2. select 
    3.     prod_name,
    4.     prod_desc
    5. from Products
    6. where prod_desc not REGEXP 'toy' 
    7. order by prod_name asc

     考点：

    1、not like

    2、字符串

    3、正则表达式

    SQL81 顾客登录名 

    描述

    我们的商店已经上线了，正在创建顾客账户。所有用户都需要登录名，默认登录名是其名称和所在城市的组合。

    给出 Customers表 如下：

    cust_idcust_namecust_contactcust_city
    a1Andy LiAndy LiOak Park
    a2Ben LiuBen LiuOak Park
    a3Tony DaiTony DaiOak Park
    a4Tom ChenTom ChenOak Park
    a5An LiAn LiOak Park
    a6Lee ChenLee ChenOak Park
    a7Hex LiuHex LiuOak Park

    【问题】编写 SQL 语句，返回顾客 ID（cust_id）、顾客名称（cust_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。提示：需要使用函数、拼接和别名。

    【示例结果】

    返回顾客id cust_id，顾客名称cust_name，顾客登录名 user_login

    cust_idcust_nameuser_login
    a1Andy LiANOAK
    a2Ben LiuBEOAK
    a3Tony DaiTOOAK
    a4Tom ChenTOOAK
    a5An LiANOAK
    a6Lee ChenLEOAK
    a7Hex LiuHEOAK

    【示例解析】

    例如，登录名是 ANOAK（Andy Li，居住在 Oak Park）

    示例1

    输入：

    DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
	cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
	cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名',
	cust_contact VARCHAR(255) NOT NULL COMMENT '客户联系人',
	cust_city VARCHAR(255) NOT NULL COMMENT '客户城市'
);
INSERT `Customers` VALUES ('a1','Andy Li','Andy Li','Oak Park'),('a2','Ben Liu','Ben Liu','Oak Park'),('a3','Tony Dai','Tony Dai','Oak Park'),('a4','Tom Chen','Tom Chen','Oak Park'),('a5','An Li','An Li','Oak Park'),('a6','Lee Chen','Lee Chen','Oak Park'),('a7','Hex Liu','Hex Liu','Oak Park');

    复制输出：

    cust_id|cust_name|user_login
a1|Andy Li|ANOAK
a2|Ben Liu|BEOAK
a3|Tony Dai|TOOAK
a4|Tom Chen|TOOAK
a5|An Li|ANOAK
a6|Lee Chen|LEOAK
a7|Hex Liu|HEOAK

    代码 

    1. select
    2.     cust_id,
    3.     cust_name,
    4.     upper(concat(substring(cust_contact, 1, 2), substring(cust_city, 1, 3))) as user_login
    5. from
    6.     Customers
    7. ;

    考点

    1、截取字符串substring(字符串， 起始位置， 截取长度)

    2、拼接字符串concat(字符串1， 字符串2， ……)

    3、大写字符串upper(字符串)；lower小写

    SQL85 确定 Products 表中价格不超过 10 美元的最贵产品的价格

    描述

    Products 表

    prod_price
    9.49
    600
    1000

    【问题】编写 SQL 语句，确定 Products 表中价格不超过 10 美元的最贵产品的价格（prod_price）。将计算所得的字段命名为 max_price。

    【示例结果】返回max_price

    max_price
    9.49

    【示例解析】

    返回十元以下最高价格max_price。

    示例1

    输入：

    DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_price` DOUBLE NOT NULL COMMENT '产品价格'
);
INSERT INTO `Products` VALUES (9.49),
(600),
(1000);

    复制输出：

    max_price
9.490

    代码

    1. select 
    2.     max(prod_price) as max_price
    3. from
    4.     Products
    5. where 
    6.     prod_price <= 10
    7. ;
    1. select 
    2.     prod_price as max_price
    3. from
    4.     Products
    5. where 
    6.     prod_price <= 10
    7. order by
    8.     prod_price desc
    9. limit 0, 1
    10. ;

    考点

    1、limit

    2、max 

     

    SQL97 返回顾客名称和相关订单号以及每个订单的总价

    描述

    Customers 表有字段，顾客名称：cust_name、顾客id：cust_id

    cust_idcust_name
    cust10andy
    cust1ben
    cust2tony
    cust22tom
    cust221an
    cust2217hex

    Orders订单信息表，含有字段，订单号：order_num、顾客id：cust_id

    order_numcust_id
    a1cust10
    a2cust1
    a3cust2
    a4cust22
    a5cust221
    a7cust2217

    OrderItems表有字段，商品订单号：order_num、商品数量：quantity、商品价格：item_price

    order_numquantityitem_price
    a1100010
    a220010
    a31015
    a42550
    a51525
    a777

    【问题】

    除了返回顾客名称和订单号，返回 Customers 表中的顾客名称（cust_name）和Orders 表中的相关订单号（order_num），添加第三列 OrderTotal，其中包含每个订单的总价，并按顾客名称再按订单号对结果进行升序排序。

    【示例结果】返回顾客名称 cust_name、订单号order_num、订单总额OrderTotal

    cust_nameorder_numOrderTotal
    ana5375
    andya110000
    bena22000
    hexa749
    toma41250
    tonya3150


    【示例解析】

    例如顾客名称cust_name为an的顾客的订单a5的订单总额为quantity*item_price = 15 * 25 = 375，最后以cust_name和order_num来进行升序排序。

    示例1

    输入：

    DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);
INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
	cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
	cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
);
INSERT `Customers` VALUES ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex');

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  quantity INT(16) NOT NULL COMMENT '商品数量',
  item_price INT(16) NOT NULL COMMENT '商品价格'
);
INSERT `OrderItems` VALUES ('a1',1000,10),('a2',200,10),('a3',10,15),('a4',25,50),('a5',15,25),('a7',7,7);

    复制输出：

    an|a5|375
andy|a1|10000
ben|a2|2000
hex|a7|49
tom|a4|1250
tony|a3|150

    代码

    1. select
    2.     c.cust_name,
    3.     o.order_num,
    4.     sum(oi.item_price * oi.quantity) as OrderTotal
    5. from
    6.     Orders as o
    7.     inner join Customers as c on c.cust_id = o.cust_id
    8.     inner join OrderItems as oi on o.order_num = oi.order_num
    9. group by
    10.     c.cust_name,
    11.     oi.order_num
    12. order by
    13.     c.cust_name,
    14.     o.order_num
    15. ;

    考点

    1、内连接多个表

    2、需要按照客户编号分组。原因：每个顾客对应着一份订单。我们按照订单分组求总价，自然而然也要按照顾客分组，求出每个订单对应的顾客。

  • 相关阅读:
    依图语音API的C#封装以及调用进行语音转写的处理
    19.数据结构和算法的交叉口，下一章进入算法介绍
    系统(层次)聚类
    L2 数据仓库和Hive环境配置
    srcnn fsrcnn espcn rdn超分网络的结构
    微信小程序template界面模板导入
    【c++】std::function
    论文学习笔记（二）：面对多步攻击的网络安全态势评估
    基于SWAT-MODFLOW地表水与地下水耦合
    python基于协同过滤推荐算法的电影观后感推荐管理系统的设计
  • 原文地址：https://blog.csdn.net/m0_49499183/article/details/131142918