字符串专题

字符串的处理要求多样，这里做积累。

1. 首字母大写：

力扣1667：

select user_id,concat(upper(left(name,1)),
lower(substring(name,2))) as name
from Users
order by user_id

--- sql 没有像 python 一样，将首字母大写的函数 title()；

substring(name,2)：左起第二个开始取子集。

2. 聚合合并：

力扣1484：

select sell_date,count(distinct product) as num_sold,
group_concat(distinct product order by product) as products
from Activities
group by sell_date

---group by 后被单值覆盖；

group_concat(... [order by ...]) 聚合后通过拼接保留多值。

3. 写入记录：

力扣1795：

(select product_id,'store1' as store,store1 as price
from Products
where store1 is not null)
union
(select product_id,'store2' as store,store2 as price
from Products
where store2 is not null)
union
(select product_id,'store3' as store,store3 as price
from Products
where store3 is not null)

--- 'store1' as store 可将字符串 store1 直接写入记录。

 4. 日期处理：

力扣1193：

select date_format(trans_date,'%Y-%m') as month,
country,
count(trans_date) as trans_count,
sum(if(state='approved',1,0)) as approved_count,
sum(amount) as trans_total_amount,
sum(if(state='approved',amount,0)) as approved_total_amount
from Transactions
group by month,country

--- date_format (..., '%Y-%m') 将 “年-月-日” 转为 “年-月” 。