select
	si.singer_id,
si.singer_name,
al.album_id,
al.album_name,
count(li.id) as play_count
from singer_info si
left join album_info al using(singer_id)
left join song_info so using(album_id)
left join listen_rcd li using(song_id)
group by si.singer_id, si.singer_name,al.album_id,al.album_name
having play_count=0;

select 
	gd.gd_id,
gd.gd_nm,
count(*) "fav_count"
from gd_inf gd
join xhs_fav_rcd fav on gd.gd_id=fav.mch_id
group by gd.gd_id
order by fav_count desc
limit 1;

select length(singer_name) "1",count(1) from singer_info group by 1;

select 
	singer_name,
char_length(singer_name) "len"
from singer_info;

select upper(singer_name) "uppered_name" from singer_info;

select dt,tmp_h,tmp_l,con 
from weather_rcd_china 
where city="beijing" and con like "%雪%";

select 
	 left(name,1) "surname",
 count(1) "cnt"
from students
group by surname
order by cnt desc
limit 5;

select 
	 left(name,1) "surname",
 count(1) "cnt"
from students
group by surname
order by cnt desc;

select 
	city,
sum(case when con like "%多云%" then 1 else 0 end) as cloudy_days,
concat(cast((sum(case when con like "%多云%" then 1 else 0 end)/count(1))*100 as decimal(10,2)),"%") "p"
from weather_rcd_china
where year(dt)=2021
group by city
order by 3 desc;

select 
	* 
from hand_permutations
where left(card1,1)=left(card2,1);

select * 
from hand_permutations
where 
	(card1 like "A%" or card2 like "A%") 
	and 
right(card1,1)=right(card2,1);

select * 
from hand_permutations
where (card1 like "A" or card2 like "A") and left(card1,2)=left(card2,2);

select * 
from hand_permutations
where left(card1,1)="A" and right(card2,2)!="A";

select *
from hand_permutations
where 
	concat(card1,card2) like "%A%A%" or
concat(card1,card2) like "%A%K%" or
concat(card1,card2) like "%K%K%" or
concat(card1,card2) like "%K%A%" 
;

select
	(case 
	when mch_nm like "%按摩保健休闲%" then "按摩保健休闲" 
		when mch_nm rlike ".*(按摩|保健|休闲|养生|SPA|会所).*" then "按摩、保健、休闲、养生、SPA、会所" end) as "reg_rules",
 count(distinct mch_nm) mch_cnt
from cmb_usr_trx_rcd
where mch_nm like "%按摩保健休闲%" or mch_nm rlike ".*(按摩|保健|休闲|养生|SPA|会所).*"
group by reg_rules
order by mch_cnt desc;

select count(distinct mch_nm) as mch_cnt
from cmb_usr_trx_rcd
where mch_nm like "%按摩保健休闲%";

select 
	user_id,
dayname(start_time) "day_of_week",
count(1) "listens_per_day"
from listen_rcd
group by user_id,day_of_week
order by user_id,day_of_week;

select * 
from cmb_usr_trx_rcd
where 
	usr_id="5201314520" and 
(date(trx_time) between "2024-06-08" and "2024-06-10" or
date(trx_time) between "2024-09-15" and "2024-09-17");

select * 
from cmb_usr_trx_rcd
where 
	usr_id="5201314520" and 
date(trx_time) between "2024-06-08" and "2024-06-10" or
date(trx_time) between "2024-09-15" and "2024-09-17";

select 
	count(distinct case 
when time(login_time) between "07:30:00" and "09:30:00" or time(login_time) between "18:30:00" and "20:30:00" then usr_id end) as "通勤",
count(distinct case 
when time(login_time) between "11:30:00" and "14:00:00" then usr_id end) as "午休",
count(distinct case 
when time(login_time) between "22:30:00" and "23:59:59" then usr_id
when time(login_time) between "00:00:00" and "01:00:00" then usr_id end) as "临睡"
from user_login_log
where 
	login_time>=date_format(date_sub(curdate(),interval 1 month),"%y-%m-01 00:00:00") and
login_time<date_format(curdate(),"%y-%m-01 00:00:00");