Doris/Mysql 常用的用户名加密、排序方式、日期

-- 一个中文长度为3字节
select CONCAT(SUBSTRING('李晨', 1, 1), REPEAT('*', LENGTH('李晨')/3 - 1)) AS 用户名;
select CONCAT(SUBSTRING('李晨', 1, 1), REPEAT('*', 2)) AS 用户名;

select CONCAT(REPEAT('*', LENGTH(initiator_name)/3 - 1),SUBSTRING(initiator_name,-2))
from ods.ods_research_db_project;

-- 6,9,18,1,1
select LENGTH('李晨'),
LENGTH('李收到'),
LENGTH('李收到啦附件'),
LENGTH('A'),
LENGTH('9');

-- 中文首字母拼音排序，NULL会排在第一
select provmanager_accode,
provmanager_name,
count(project_doctor_num) project_doctor_total_num
from ads.ads_dow_register_doctor_research_project_info
group by provmanager_accode, provmanager_name
order by convert(provmanager_name using gbk) asc;
 

-- 中文首字母拼音排序，NULL排在最后

order by 医院名称 is null,convert(医院名称 using gbk);

-- 对时间、数字排序，NULL排在最后还可以：

1、order by checkpass_time is null,checkpass_time ASC
2、ORDER BY ISNULL(Sort_No),Sort_No

3、SELECT Sort_No FROM test ORDER BY -Sort_No DESC
4、SELECT nian,price from Tbl ORDER BY price!=0 desc,price asc
5、SELECT nian,price,1/price as od from Tbl ORDER BY od desc

-- 昨天date 年月日 一定要加 interval n day
select date_sub(curdate(),interval 1 day);

-- 指定排序规则
select cc.org_code,
cc.owner_code,
case cc.type
when 10 then '科普'
when 11 then '文章'
when 20 then '视频'
when 40 then '科普/病例科普'
end 物料类型,
count(distinct cc.code)
from ods.ods_content_data_db_cons_content cc
where cc.type in (10,11,20,40)
group by cc.org_code,cc.owner_code,cc.type
order by field(物料类型,'病例','视频','科普','文章');