Day03-LeetCode-SQL启航~

1667. Fix Names in a Table

+----------------+---------+| Column Name    | Type    |+----------------+---------+| user_id        | int     || name           | varchar |+----------------+---------+user_id is the primary key for this table.This table contains the ID and the name of the user. The name consists of only lowercase and uppercase characters.Write an SQL query to fix the names so that only the first character is uppercase and the rest are lowercase.Return the result table ordered by user_id.The query result format is in the following example.Example 1:Input: Users table:+---------+-------+| user_id | name  |+---------+-------+| 1       | aLice || 2       | bOB   |+---------+-------+Output: +---------+-------+| user_id | name  |+---------+-------+| 1       | Alice || 2       | Bob   |+---------+-------+

这一题需要把name栏位的名字改成字首大写，其余小写的格式，
当下我想用title这类python在用的函式，但发现SQL没有这个，
于是到w3s查询UPPER、LOWER转大写跟转小写，还学到了SUBSTR、CONCAT的用法，
SUBSTR是提取字串的函式，CONCAT是连结字串的函式，
我直接注解在下面方便记录。

SELECT user_id,    CONCAT(UPPER(LEFT(name,1)),  #CONCAT连结((字),(字))，UPPER(LEFT(name,1)栏位name左边第1个字大写    LOWER(SUBSTR(name,2))) name  #LOWER(SUBSTR(name,2))，提取name中，第2个字以后的字串并转小写。FROM UsersORDER BY user_id; #按user_id做排序

1484. Group Sold Products By The Date

+-------------+---------+| Column Name | Type    |+-------------+---------+| sell_date   | date    || product     | varchar |+-------------+---------+There is no primary key for this table, it may contain duplicates.Each row of this table contains the product name and the date it was sold in a market. Write an SQL query to find for each date the number of different products sold and their names.The sold products names for each date should be sorted lexicographically.Return the result table ordered by sell_date.The query result format is in the following example.Example 1:Input: Activities table:+------------+------------+| 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    |+------------+------------+Output: +------------+----------+------------------------------+| sell_date  | num_sold | products                     |+------------+----------+------------------------------+| 2020-05-30 | 3        | Basketball,Headphone,T-shirt || 2020-06-01 | 2        | Bible,Pencil                 || 2020-06-02 | 1        | Mask                         |+------------+----------+------------------------------+Explanation: For 2020-05-30, Sold items were (Headphone, Basketball, T-shirt), we sort them lexicographically and separate them by a comma.For 2020-06-01, Sold items were (Pencil, Bible), we sort them lexicographically and separate them by a comma.For 2020-06-02, the Sold item is (Mask), we just return it.

这一题要将sell_date 中重複日期的商品加在一起，并把商品名摆在products栏位中，
比如2020-05-30有3笔，num_sold中加总有3，products中填上那3个品项Basketball,Headphone,T-shirt
，知道要用COUNT去做加总，用DISTINCT提取重複值，但先后顺序不会写，GOOGLE后在将注解写上记录。
** GROUP_CONCAT能合併多笔

SELECT  sell_date , COUNT(DISTINCT product) AS num_sold,                     #加总(DISTINCT product)是因为每个商品都不一样才能这么做GROUP_CONCAT(DISTINCT product ORDER BY product) AS products                     #GROUP_CONCAT是多笔连结函式，其中发现ORDER BY product这句不写也能过FROM ActivitiesGROUP BY sell_date # GROUP BY用来分类群组，不打会6笔都加在一起ORDER BY sell_date;

1527. Patients With a Condition

+--------------+---------+| Column Name  | Type    |+--------------+---------+| patient_id   | int     || patient_name | varchar || conditions   | varchar |+--------------+---------+patient_id is the primary key for this table.'conditions' contains 0 or more code separated by spaces. This table contains information of the patients in the hospital. Write an SQL query to report the patient_id, patient_name and conditions of the patients who have Type I Diabetes. Type I Diabetes always starts with DIAB1 prefix.Return the result table in any order.The query result format is in the following example.Example 1:Input: Patients table:+------------+--------------+--------------+| patient_id | patient_name | conditions   |+------------+--------------+--------------+| 1          | Daniel       | YFEV COUGH   || 2          | Alice        |              || 3          | Bob          | DIAB100 MYOP || 4          | George       | ACNE DIAB100 || 5          | Alain        | DIAB201      |+------------+--------------+--------------+Output: +------------+--------------+--------------+| patient_id | patient_name | conditions   |+------------+--------------+--------------+| 3          | Bob          | DIAB100 MYOP || 4          | George       | ACNE DIAB100 | +------------+--------------+--------------+Explanation: Bob and George both have a condition that starts with DIAB1.

这一题是找出字串中有"DIAB1"字样的资料并列出，
这题找字串马上就用LIKE"%"写出来，但忽略了小细节卡关，直接写在注解中。

#SELECT * FROM Patients #WHERE conditions LIKE "%DIAB1%";<<他不会将D前面没有字的值列入select *from Patientswhere conditions like '% DIAB1%' or       conditions like 'DIAB1%' #要补上这句才能找到开头DIAB1的资料