1667.修复表中的名字
表: Users
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| user_id | int |
| name | varchar |
+----------------+---------+
user_id 是该表的主键。该表包含用户的 ID 和名字。名字仅由小写和大写字符组成。
编写一个 SQL 查询来修复名字,使得只有第一个字符是大写的,其余都是小写的。返回按 user_id 排序的结果表。
答案解析
查询表,然后查出的name 要首字母大写其他字母小写,再排序
concat(str1,str2, str3…) 返回str1 、 str2、str3、…的拼接
concat_ws(separator ,str1,str2,…) 返回str1、str2、…的拼接,中间添加分隔符separator
lower(str) 返回str的小写, upper(str) 返回str的大写
left(str, n) 返回str前n个字符,如果str长度小于n就返回str, 如果n为负数就返回空字符串,right(str, n) ,同理
substr(str,m,[n]) ,n是长度可省略,m是起点, 截取str从第m开始,长度为n的子串
知道了上面这些知识就可以写出SQL语句了:
SELECT user_id, concat(upper(left(name, 1)), lower(substr(name, 2))) name
FROM users
ORDER BY user_id
1484.按日期分组销售产品
表 Activities:
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| sell_date | date |
| product | varchar |
+-------------+---------+
此表没有主键,它可能包含重复项。此表的每一行都包含产品名称和在市场上销售的日期。
写个查询,来查找每个日期、销售的不同产品的数量及其名称。
每个日期的销售产品名称应按词典序排列。返回按 sell_date 排序的结果表。
输入:
Activities 表:
+------------+-------------+
| sell_date | product |
+------------+-------------+
| 2020-05-30 | Headphone |
| 2020-06-01 | Pencil |
| 2020-06-02 | Mask |
| 2020-05-30 | Basketball |
| 2020-06-01 | Bible |
| 2020-06-02 | Mask |
| 2020-05-30 | T-Shirt |
+------------+-------------+
输出:
+------------+----------+------------------------------+
| sell_date | num_sold | products |
+------------+----------+------------------------------+
| 2020-05-30 | 3 | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2 | Bible,Pencil |
| 2020-06-02 | 1 | Mask |
+------------+----------+------------------------------+
答案解析
group_concat用法
distinct 要连接的字段 order by 要排序的字段 separator 分隔符
group_concat(distinct product order by product separator ',')
count(distinct product) 获取分组不同的product
按销售日期分组,按日期排序。把产品名按分组连接,按字典序排序
SELECT sell_date, count(distinct product) num_sold, group_concat(distinct product order by product separator ',') products
FROM activities
GROUP BY sell_date
ORDER BY sell_date
1527.患某种疾病的患者
患者信息表: Patients
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| patient_id | int |
| patient_name | varchar |
| conditions | varchar |
+--------------+---------+
patient_id (患者 ID)是该表的主键。
'conditions' (疾病)包含 0 个或以上的疾病代码,以空格分隔。这个表包含医院中患者的信息。
写一条 SQL 语句,查询患有 I 类糖尿病的患者 ID (patient_id)、患者姓名(patient_name)以及其患有的所有疾病代码(conditions)。
I 类糖尿病的代码总是包含前缀 DIAB1 。
按 任意顺序 返回结果表。
输入:
Patients表:
+------------+--------------+--------------+
| patient_id | patient_name | conditions |
+------------+--------------+--------------+
| 1 | Daniel | YFEV COUGH |
| 2 | Alice | |
| 3 | Bob | DIAB100 MYOP |
| 4 | George | ACNE DIAB100 |
| 5 | Alain | DIAB201 |
+------------+--------------+--------------+
输出:
+------------+--------------+--------------+
| patient_id | patient_name | conditions |
+------------+--------------+--------------+
| 3 | Bob | DIAB100 MYOP |
| 4 | George | ACNE DIAB100 |
+------------+--------------+--------------+
解释:Bob 和 George 都患有代码以 DIAB1 开头的疾病。
答案解析
直接查找conditions 中包含DIAB1开头的病的, DIAB1要么在开头, 要么在后面
在开头是 DIAB1% 在后面是 %空格DIAB1%,(防止有DDIAB1这种病)
SELECT *
FROM Patients
WHERE conditions like 'DIAB1%' OR conditions like '% DIAB1%'
总结
字符串操作相关用法
concat(str1, str2, str3,...) 返回这几个字符串的拼接
concat_ws(分隔符, str1, str2, .....) 返回这几个字符串中间以分隔符隔开的拼接
lower(str) 返回str的小写,
upper(str) 返回str的大写
left(str, n) 返回str前n个字符,如果str长度小于n就返回str, 如果n为负数就返回空字符串,
right(str, n) ,同理
substr(str,m,[n]) ,n是长度可省略,m是起点, 截取str从第m开始,长度为n的子串
group by 相关
group_concat用法
distinct 要连接的字段 order by 要排序的字段 separator 分隔符
group_concat(distinct product order by product separator ',')
统计group by 被分到一组的数据,某字段出现不同种类
count(distinct 字段)