select
	aa.singer_id
,aa.singer_name
,bb.album_id
,bb.album_name
, 0 as play_count
from singer_info aa
left join album_info bb on aa.singer_id = bb.singer_id
where aa.singer_name not in(
 select
	distinct
 	d.singer_name
 from listen_rcd a
 left join song_info b on a.song_id = b.song_id
 left join album_info c on b.album_id = c.album_id
 left join singer_info d on c.singer_id = d.singer_id
)

select
	aa.singer_name
,bb.album_name
from singer_info aa
left join album_info bb on aa.singer_id = bb.singer_id
where aa.singer_name not in(
 select
	distinct
 	d.singer_name
 from listen_rcd a
 left join song_info b on a.song_id = b.song_id
 left join album_info c on b.album_id = c.album_id
 left join singer_info d on c.singer_id = d.singer_id
)

select
	user_id
,DATE_FORMAT(a.start_time,'%W') sing
,count(song_id)
from listen_rcd a
group by 
 	a.user_id
,sing
order by user_id,sing

select
	f
,count(distinct s.student_id)
from
	(
select
	a.student_id
,case when b.score >= 110 then '[110,120]'
	when b.score >= 90 then '[90,110)'
when b.score >= 60 then '[60,90)'
when b.score < 60 then '[0,60)' end f
from students a
left join scores b on a.student_id = b.student_id
where b.subject = '数学' and b.exam_date = '2024-06-30'
) s
group by s.f

select
	f
,count(distinct s.student_id)
from
	(
select
	a.student_id
,case when b.score >= 110 then '[110,120]'
	when b.score >= 90 then '[90,110)'
when b.score >= 60 then '[60,90]'
when b.score < 60 then '[0,60)' end f
from students a
left join scores b on a.student_id = b.student_id
where b.subject = '数学' and b.exam_date = '2024-06-30'
) s
group by s.f

select
	f
,count(distinct s.student_id)
from
	(
select
	a.student_id
,case when b.score >= 110 then '[110,120]'
	when b.score >= 90 then '[90,110)'
when b.score >= 60 then '[60,90]'
when b.score < 60 then '[0,60)' end f
from students a
left join scores b on a.student_id = b.student_id
where b.subject = '数学'
) s
group by s.f

select
	a.prd_id
,b.prd_nm
,count(a.if_snd) num
from tb_pg_act_rcd a
left join tb_prd_map b on a.prd_id = b.prd_id
group by 1,2
order by num desc
limit 1

select
	a.prd_id
,b.prd_nm
,count(a.if_snd) num
from tb_pg_act_rcd a
left join tb_prd_map b on a.prd_id = b.prd_id
group by 1,2
order by num asc

select
 a.cust_uid
 ,a.start_loc
 ,a.end_loc
 ,a.start_tm
 ,a.car_cls
fromdidi_sht_rcd a
inner join loc_nm_ctg b	on	a.end_loc = b.loc_nm and b.loc_ctg = '餐饮'
order by start_tm asc

select
 *
fromdidi_sht_rcd a
inner join loc_nm_ctg b	on	a.end_loc = b.loc_nm and b.loc_ctg = '餐饮'
order by start_tm asc

select
	a.live_id
,b.live_nm 
,count(distinct a.usr_id) num
from ks_live_t1 a
left join ks_live_t2 b on a.live_id = b.live_id
where enter_time >= '2021-09-12 23:00:00' and enter_time < '2021-09-13 00:00:00'
group by a.live_id,b.live_nm
order by num desc
limit 5

select
	a.live_id
,b.live_nm 
,count(distinct a.usr_id) num
from ks_live_t1 a
left join ks_live_t2 b on a.live_id = b.live_id
where enter_time > '2021-09-12 23:00:00' and enter_time < '2021-09-13 00:00:00'
group by a.live_id,b.live_nm
order by num desc
limit 5

select
	a.live_id
,b.live_nm 
,count(a.usr_id) num
from ks_live_t1 a
left join ks_live_t2 b on a.live_id = b.live_id
where enter_time > '2021-09-12 23:00:00' and enter_time < '2021-09-13 00:00:00'
group by a.live_id,b.live_nm
order by num desc
limit 5

select
	a.live_id
,b.live_nm 
,count(distinct a.usr_id) num
from ks_live_t1 a
left join ks_live_t2 b on a.live_id = b.live_id
where enter_time > '2021-09-12 23:00:00' and enter_time < '2021-09-13 00:00:00'
group by a.live_id,b.live_nm
order by num
limit 5

select 
    * 
from 
    scores 
where 
    (
      (subject = '历史' and score >= 90)
    or (subject = '地理' and score >= 90)
    or (subject = '政治' and score >= 90)
       )
    and exam_date='2024-06-30'
order by score desc ,student_id,subject

select
	*
from scores
where exam_date = '2024-06-30' and subject = '英语' and (score between 100 and 110)
order by score desc

select 
*
from students
where class_code in('C219','C220','C221') AND gender = 'f'
order by student_id

select
 	*
 from subject_score
 where chinese>100 or math >100 or english >100
 order by chinese asc

select
 	student_id
 from subject_score
 where chinese>100 or math >100 or english >100
 order by chinese asc

select
	exam_date
,max(case when subject = '语文' then score else null end )
,max(case when subject = '数学' then score else null end )
,max(case when subject = '英语' then score else null end )
from scores
where student_id = '460093' and	 subject in('语文','数学','英语')
group by exam_date